none
How to Deploy .mdf Files RRS feed

  • Question

  • Hi Guys,

    I have a problam. I wanna deploy my application that it has data.mdf file.

    When I install and test it I get this message:

    ---------------------------
    Microsoft SQL Server Login
    ---------------------------
    Connection failed:
    SQLState: '42000'
    SQL Server Error: 5133
    [Microsoft][SQL Native Client][SQL Server]Directory lookup for the file "C:\Program Files\MyDic\Database\Data.mdf" failed with the operating system error 5(Access is denied.).
    Connection failed:
    SQLState: '42000'
    SQL Server Error: 1832
    [Microsoft][SQL Native Client][SQL Server]Could not attach file 'C:\Program Files\MyDic\Database\Data.mdf' as database 'MyDic'.

    ---------------------------
    OK  
    ---------------------------

    I have to mention that:

    SQL Server: SQL Server 2005 Express

    Operating System: Windows XP SP2

    and I'm Local Admin.

    Some parts of my code are:

    lstrcpy(szDataDir,

    TEXT("DRIVER={SQL Native Client};SERVER=.\\sqlexpress;Trusted_Connection=Yes;DATABASE=MyDic;AttachDBFileName="));

    GetCurrentDirectory(sizeof(szCurrentDir), szCurrentDir);

    lstrcat(szCurrentDir, TEXT("\\Database\\Data.mdf;"));

    lstrcat(szDataDir, szCurrentDir);

    SQLDriverConnect(hdbc, hWnd, szDataDir, SQL_NTS, szConnStrOut, sizeof(szConnStrOut), &cbConnStrOut, SQL_DRIVER_COMPLETE);

    How can I fix it?

    Regards

    Tuesday, June 13, 2006 4:25 AM

Answers

  • The SQL Express service account is Network Service, which does not have permissions to attach database from the user profile directory where your database is located. (i.e. My Documents) You have a couple options:

    1. Use a User Instance by adding User Instance=True to your connection string. This is a special type of instance tha SQL Express supports that runs a separate SQL process in your user context and has permissions to your profile directories. (Check out the User Instance white paper for more info)
    2. Move the database to a different directory where Network Service can see it, for example the Data directory for the server.
    3. Give Network Service permissions to your profile directories. This probably isn't the best idea, since any service that runs as Network Service will now have permissions to your folders.

    It really depends on how you're using this database. If this is a single user application and you are deploying the mdf file with the application, then you should probably be using User Instances, otherwise, I'd opt with #2 and install the database to a common directory.

    Regards,

    Mike Wachal
    SQL Express team

    Mark the best posts as Answers!

    Tuesday, June 13, 2006 7:23 AM
    Moderator

All replies

  • The SQL Express service account is Network Service, which does not have permissions to attach database from the user profile directory where your database is located. (i.e. My Documents) You have a couple options:

    1. Use a User Instance by adding User Instance=True to your connection string. This is a special type of instance tha SQL Express supports that runs a separate SQL process in your user context and has permissions to your profile directories. (Check out the User Instance white paper for more info)
    2. Move the database to a different directory where Network Service can see it, for example the Data directory for the server.
    3. Give Network Service permissions to your profile directories. This probably isn't the best idea, since any service that runs as Network Service will now have permissions to your folders.

    It really depends on how you're using this database. If this is a single user application and you are deploying the mdf file with the application, then you should probably be using User Instances, otherwise, I'd opt with #2 and install the database to a common directory.

    Regards,

    Mike Wachal
    SQL Express team

    Mark the best posts as Answers!

    Tuesday, June 13, 2006 7:23 AM
    Moderator
  • Dear Mike,

    Thank you very much for your bright comments.

    But I have another question. Can I use 'User Instance = True' in ODBC connection string for SQL Native Client driver?

    Sincerely.

    Wednesday, June 14, 2006 2:21 AM
  • User Instances are only supported in Shared Memory, local access and single user. All of this is part of the SQL Native Client.

    Regards,

    Mike

    Wednesday, June 14, 2006 6:02 AM
    Moderator
  • Thank you again.

    Best

    Thursday, June 15, 2006 1:46 AM
  • Hello Mike,

    you wrote:

    > for example the Data directory for the server.

    What is the Data directory for the server? Is this where SQL Server is installed?
    C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    Where should Databases be stored in a least prvileged environment.
    So that a standard user can work with them?
    So that users from other computers in a small XP Network (without a domain controller)
    can access them?

    Regards
    Markus

     

     

    Saturday, November 4, 2006 9:20 AM
  • Other questions that come up:

    Is it recommended to store Application Databases in this Directory?
    There In a sub directory CompanyName/AppName?
    If in a sub directory, which access rights should this directory get and how is this done from
    a installation programm?

    How do i get to know the Data Directory?

    Sorry is there a detailed article how to deploy databases exactly on msdn?


    Saturday, November 4, 2006 9:51 AM
  • Hi Markus,

    The Data directory is at C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Data and this is a common location to store database files for SQL Server.

    If you want to create subdirectories, I don't see any problem with that. Typically, when you create sub-directories, they have the same permissions as the partent directory, as long as you have not changed this behavior on your computer. Remember, the user installing your application will have to be an administrator user on the computer in order to create folders under Program Files and to copy files there. You can find detailed information about securing the data and log files here.

    Normally the database file is manually installed and attached to the SQL Server by a DBA, and not by an installation program. Unless you're are talking about having every copy of the application with it's own database and having only single user access. In that case, you should consider just putting the database directly into your application project and using a User Instance to access the database. If you're using VS 2005 it will automatically set this all up for you when you create a new database in your project, you can then deploy everything using ClickOnce deployment. More information about ClickOnce deployment is available in the VS documentation.

    Mike

    Monday, November 6, 2006 1:06 AM
    Moderator
  • Hello Mike,

    i write a visual basic 2005 program with Sql Server express for home users and small networks. The installation of the program should be per machine and not per user of the computer. The installation program should install SQL Server and a named instance of SQL Server Express with the program name, if not already on the computer.

    Every user of the program should be able to store his objects in the program default database. And he should be able to create own databases and store his objects there. (See my question Copy a database with copy the .mdf file and attaching it with a new name?)

    The users of the program should be able to decide with databases are shared with other users (on the same computer or on other computers.)  This is be done by the program which reads that information in a  database table.

    I did not care about exact installation yet, because I want first get the program written. Now I read something about Vista and get a bit confused. What does this mean for Visual Studio applications which come with .mdf files and need a named instance? Is Click Once the right thing for my goals?

    > The Data directory is at C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Data and this
    > is a common location to store database files for SQL Server.

    I read in vista stuff that programs should not write in the \Programs Folder. Is a .mdf file user data which should be in a user profile? If the Data directory is the right place, please how do I get the foldename??

    If I want to copy a (own, not the SQL Server) model (.mdf) database file for a new user database, then this can be done only if the database files reside in the user profile, or not?

    > Normally the database file is manually installed and attached to the SQL Server by a DBA,

    I think this was in the past. Now Sql Server should replace Access mdbs and not every home user is a DBA. They should only click install and nothing else.

    Regards, Markus

     

     

     

    Monday, November 6, 2006 6:13 AM
  • A database should be like a file for the program user. He should be able to store information in it and send it to others per Mail for example who have the same program and can access this information then. Attaching and detaching should be handled from the program, there must be other names for this for the user.

    But can a program act like this in the data directory?

    If i store the .mdfs in the all users profile, is the a attach and detach possible with the normal rights of the sql service account? How can i get this goals vista compatible?

    Markus

     

    Monday, November 6, 2006 6:31 AM
  • What is the right vista way for a programm to share program documents with other users on the same computer? So that other users can read, change or even delete this documents with the program? Tthis documents be .mdf, .doc or other files.

    Markus

    Sorry for all these questions... i hope i'm not the only one who have them.

     

    Monday, November 6, 2006 7:31 AM
  • If all user files are in the profiles: What is when Windows does not start or the partition is damaged and need to be installed again? Are then all user docs away? Therefore i have my own data in a own partition, not in the system partition and i'm free to install windows when i want.
    Monday, November 6, 2006 8:54 AM
  • Hi Markus,

    My first response is that you should be looking at SQL Server Compact Edition (formerly SQL Server Everywhere Edition) for storing data local to the user and SQL Server for storing central data. Rather than trying to write to both, you should set up synchronization between the local SQLce database and the remote SQLServer database. You can find more information about SQLce at the following places:

    This is a little different programming model that you might be thinking right now, but it seems suited to what you're trying to do.

     Markus Sch. wrote:
    i write a visual basic 2005 program with Sql Server express for home users and small networks. The installation of the program should be per machine and not per user of the computer. The installation program should install SQL Server and a named instance of SQL Server Express with the program name, if not already on the computer.

    VS ClickOnce deployment only deploys per user so ClickOnce may not work for you. You may need to use a standard MSI deployment project to allow you to install per machine. You might also be able to re-think you're design to allow for per user deployments. You should follow this up in the VS forums where you'll find more information about deployment semantics for VS applications.

    SQL Express is always deployed per machine, but User Instances are per user.

     Markus Sch. wrote:
    Every user of the program should be able to store his objects in the program default database. And he should be able to create own databases and store his objects there. (See my question Copy a database with copy the .mdf file and attaching it with a new name?)

    The users of the program should be able to decide with databases are shared with other users (on the same computer or on other computers.)  This is be done by the program which reads that information in a  database table.

    You storage semantics are possible, but your notion of the user "deciding" which databases are shared doesn't really mesh with how SQL Server works. User access is controled by use of Logins and Database Users that are defined in SQL Server. Assigning these permissions is not usually something controled by every end-user. Any data you want to share should be located on a central server that all users have access to.

     Markus Sch. wrote:
    I did not care about exact installation yet, because I want first get the program written. Now I read something about Vista and get a bit confused. What does this mean for Visual Studio applications which come with .mdf files and need a named instance? Is Click Once the right thing for my goals?

    ClickOnce deployed .mdf file will still work on Vista because they deploy to a per user location under the user profile directories. Standard ClickOnce applciations with embedded .mdf files use a User Instance, which also works on Vista. User Instances only allow single user, local access to the data, you will not be able to share the data in the database deployed via ClickOnce with others because of this. See my comments above about using a central server for shared data.

     Markus Sch. wrote:
    I read in vista stuff that programs should not write in the \Programs Folder. Is a .mdf file user data which should be in a user profile? If the Data directory is the right place, please how do I get the foldename??

    User specific information should never be installed in Program Files, this is a best practice for any application, not just one's running on Vista. If you're .mdf if meant to contain data for only the current user, then that file should be installed to the profile directories. This is exactly what ClickOnce depoyment and User Instances is designed to accomodate. With ClickOnce and User Instance, you do not need to get the folder name, you use the |DataDirectory| moniker in your connection string; VS and SQL Server understand how to find the file and attach it based on that.

     Markus Sch. wrote:
    If I want to copy a (own, not the SQL Server) model (.mdf) database file for a new user database, then this can be done only if the database files reside in the user profile, or not?

    You can create a user database any number of ways. You can copy the file from some location, you can deploy the .mdf with your project or you can run a script that will create the database once you've connected to the SQL Express instance. If you copy the file, you will need to put the original in a location that all users have access to and copy the file to a location that the user has permission for writing files. The user profile is a good location because you know the users have permission for this location. You might also be able to store it in a different location, but you'll need to ensure that users have permissions to access and write to that folder. You may need to do create those permissions as part of your application installation.

     Markus Sch. wrote:
    > Normally the database file is manually installed and attached to the SQL Server by a DBA,

    I think this was in the past. Now Sql Server should replace Access mdbs and not every home user is a DBA. They should only click install and nothing else.

    This is an over simplification. SQL Express is still SQL Server, it's a Windows Service, it runs like a server and it normally expects that it's files aren't going to be moving around. Jet (Access) is a file based database, it doesn't run all the time and it has double-click semantics that allow for the database to be launched at runtime when it is needed. We added User Instances to SQL Express to make certain things important to VS integration more possible. We can deploy the .mdf file through ClickOnce and allow non-Administrative users to attach those files at runtime because of this change, but that is a long way away from arbitrarily copying files around and expecting them to work. Even if your database is local and for a single user, you need to pay attention to the general principles of Client/Server architecture when designing your application.

     Markus Sch. wrote:
    A database should be like a file for the program user. He should be able to store information in it and send it to others per Mail for example who have the same program and can access this information then. Attaching and detaching should be handled from the program, there must be other names for this for the user.

    But can a program act like this in the data directory?

    If i store the .mdfs in the all users profile, is the a attach and detach possible with the normal rights of the sql service account? How can i get this goals vista compatible?

    As I point out above, SQL Server databases do not behave like this. User Instances gives you some of these behaviors, but there are some controls you need to have over file deployment to get this to work correctly. Read the white paper on User Instances for more information. If you store the .mdf in the user profile, a User Instance can attach to the file but the parent instance of SQL Express can not. By default, SQL Express uses the Network Service account as it's Service Account, and this account does not have permissions to the user profile folders.

     Markus Sch. wrote:
    What is the right vista way for a programm to share program documents with other users on the same computer? So that other users can read, change or even delete this documents with the program? Tthis documents be .mdf, .doc or other files.

    There is a difference between sharing an .mdf file and any other file. Normally, you don't share the .mdf file as much as the data in that folder. If you want to share data with anyone on a computer, you need to ensure that everyone on the computer has permissions to connect to the SQL Server and has permissiosn to access the database. Those users don't necesarily require permission to the actual .mdf file itself since access to the data is controled through SQL Server. You need to ensure that hte SQL Server service account has permission to read and write to the .mdf file so that the file can be attached and used.

    By this definition, I would still say that the Data folder is an appropriate way to store the .mdf file. Non-admin users would not be able to put the data file into the folder, but you would give them permissions to access the data via Logins and User on SQL Server.

     Markus Sch. wrote:
    If all user files are in the profiles: What is when Windows does not start or the partition is damaged and need to be installed again? Are then all user docs away? Therefore i have my own data in a own partition, not in the system partition and i'm free to install windows when i want.

    I'm not sure I understand this. If the partition or disk where you have your .mdf file is damaged, then your file is probably damaged. You should have a system for backing up your data to guard against disk corruption. Whether your data will be safe in it's own partition depends on how Windows sees that partition and what type of Windows Install you are performing. Specific question about how user files are treated during Windows Install should be directed to the Windows team.

    Regards,

    Mike

    Tuesday, November 7, 2006 12:38 AM
    Moderator
  • Hello Mike,

    thank you very much for your answer. But i don't know how exactly i should do it. I hope i can explain it in the following lines.


    > SQL Express is always deployed per machine, but User Instances are per user.

    Then I need standard MSI deployment.

    The program should be deployed per machine. The installation should install SQL Server Express with a named instance (program name) which is visible for all users. The installation should attach a main database (ProgramName_Main_DB.mdf) which is shared.

    All local users of the program should be able to create new databases (copy the ProgramName_Model_DB.mdf to a file with another name which then is attached under this name). In the users view he is then the owner of this database and decides if this database is private data for him or not.


    > You storage semantics are possible, but your notion of the user "deciding" which
    > databases are shared doesn't really mesh with how SQL Server works.

    From the user's point of view, as the owner of the database, it is a check box "Private Database". From the programs internal point of view it is the SQL Server view.

    From the users point of view each user created database contain own related data (like a folder for example), is private or not on the computer and in the network. Can be copied and send per mail to others who use the same program. These others can then work with the copy of the database. But they can not import the objects of it in their own databases.


    > This is an over simplification. SQL Express is still SQL Server, it's a Windows
    > Service, it runs like a server and it normally expects that it's files aren't going to be
    > moving around.

    For me it means less work to see a database of the users point of view (as explained above) as a SQL Server Express Database (File). Because i have not to write import and export code. Simply copy and attach/detach files.


    > If you store the .mdf in the user profile, a User Instance can attach to the file but the
    > parent instance of SQL Express can not. By default, SQL Express uses the
    > Network Service account as it's Service Account, and this account does not have
    > permissions to the user profile folders.

    Programs Data Directory:

    The ProgramName_Main_DB.mdf may reside in the SQL Express Data Folder.
    (Again the question: How do I get the folder name? It is not a user Instance.)

    The ProgramName_Model_DB.mdf may reside in a subfolder of All Users.WINXPRO because it is never attached.
    It is only copied by local users to new Files named ProgramName_UserName_DBName_DB.mdf.

    But where do I store the ProgramName_UserName_DBName_DB.mdfs? If I store it local in the users profile it can never be attached. The user can't copy it to the SQL Express Data Folder.

    What is to do?
    - Create a own Folder C:\ProgrammNameData with the needed permissions?
    - Let the SQL Express use another (which?) account?
    - ?


    I wrote:

    >> If all user files are in the profiles: What is when Windows does not start or the
    >> partition is damaged and need to be installed again?
    >> Are then all user docs away? Therefore i have my own data in a own partition, not >> in the system partition and i'm free to install windows when i want.

    You:
    > I'm not sure I understand this. If the partition or disk where you have your .mdf file is
    > damaged, then your file is probably damaged.
    > You should have a system for backing up your data to guard against disk
    > corruption

    I mean i have my data in another partition outside windows and on another disk. I have no own data in the profiles. I had installed windows serveral times in the past few years. These installations did not affect my data in any way. The only work i had to do was to install windows and programs.

    Regards, Markus

     

    Tuesday, November 7, 2006 11:28 AM
  • > From the users point of view each user created database contain own related data (like a folder for example),
    > is private or not on the computer and in the network

    This can be misunderstood. Better:
    From the users point of view each user created database contain related data (like a folder for example), 
    is private to a user ore shared with others n the computer and in the network.

     

    Tuesday, November 7, 2006 11:45 AM
  • I was wondering if there was a way to replicate an mdf file through an sql script that I could execute in a query analyzer or similar tool.

    I need to deploy an mdf file to my shared hosting environment, but I only have access to the remote database through a query analyzer type of tool where I can run an sql script.

    Is there a way I could generate an sql script from the mdf file locally on my machine? The mdf file contains views and stored procedures.

    I have SQL Server 2005 Express with Management Studio. The target database server is SQL Server 2000.

    Would appreciate your advice.

    Friday, February 9, 2007 5:51 AM
  • The SQL team recently released a tool to do exactly what you need. Grab the 1.0 Release of the Database Publishing Wizard or you can get the current release of the community verion from CodePlex. There is documentation on the site.

     

    Mike

    Saturday, February 10, 2007 5:09 AM
    Moderator
  • Thanks Mike! That's a useful tool.
    Sunday, February 11, 2007 3:21 AM