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
How to Deploy .mdf Files
Ravi Rao
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
ja123ee
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:
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!
Dave Holmes
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.
tawammar
Thank you again.
Best
Mikunos
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
jwolfskin
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
Larry Menard
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
Alan Hoiland
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
joeller
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.
Rebekkah
> 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.
jgorlick
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
Jonot2
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
luke_5290
js06
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