Can't connect to local database file .mdf using SQL Server Authentication?

Question

Using  - Visual Studio 2005 Professional, SQL Server 2005 Express, WinForms App

Problem - I am unable to connect to SQL Server local Database file (.mdf) through  SQL Server Authentication.

Description -

I am addding a SQL Database file (local) in the c# winapp solution by right-click -> add new item -> SQL database file.

It adds the file, starts a wizard to create datasets and tableAdapters. Dataset files (.xsd) are created in solution. In the Data Connection pane along ToolBox we can see Database1.mdf.

When i right click on databse1.mdf -> Modify Connection.

It shows a form with Test Connection option. The connection gets tested for Windows Auth.But for SQL Server Auth it shows following error message -

When User Instance = True

Error 1 "Failed to generate a user instance of SQL Server. Only an integrated sonnection can generate a user instance. The connection will be closed"

When i set User Instance = False it shows this message -

Error 2 "An Attemp to attach an auto-named database for file C:\Doc&Sett\Admin\MyDocs\VS2005\Projects\SamApp\SamApp\Database1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share".

Everything is done through Visual Studio and no code is involved at this stage.

These are the Connection strings autogenerated for each scenario - 

---------Successful-----------Windows Auth---------------------------------------------------------

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\vijay\My Documents\Visual Studio 2005\Projects\SamApp\SamApp\Database1.mdf";Integrated Security=True;User Instance=True

------------Failure----------SQL Server Auth------------Error 1------------------- 

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\vijay\My Documents\Visual Studio 2005\Projects\SamApp\SamApp\Database1.mdf";User ID=Vibhor;User Instance=True

 

--------------Failure-----------SQL Server Auth---------Error 2---------------------

 

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\vijay\My Documents\Visual Studio 2005\Projects\SamApp\SamApp\Database1.mdf";User ID=Vibhor;User Instance=False

Please Help

How can i connect to a local mdf file in the solution using SQL Server Authentication?

The app needs to be portable & self-contained(all app data together) with database file included so that it can be just zipped and ported to another system by copying. The new target system will have  SQL Server installed. The user on other system can run it and connect to the same local mdf file by providing a valid login(username/password).

Thus it can be run on any system with SQl server and a proper login.

thats why i want SQl Server Authentication

And my prob is similar to

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/990e9b1e-7a64-4105-aefa-650c9ba488e0

but no help from this post

 

---

Vibhor Agarwal

Answer 1

My exact problem is this

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/6764e959-4a32-49e3-ae84-61471d6a105a

---

Vibhor Agarwal

Answer 2

Vibhor,

Unfortunately this is a by design limitation.  You have to use Windows authentication for user instance. As you have found in the last thread you post here, Mike already pointed the limitation. Also this document clearly mentioned it, http://msdn.microsoft.com/en-us/library/ms254504.aspx.

"Integrated Security is set to true. To connect to a user instance, Windows Authentication is required; SQL Server logins are not supported.".

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 3

dont care if its user instance or not i just want ot connec to local mdf file

what about Error 2 when i set User Instance  = false

 

---

Vibhor Agarwal

Answer 4

How can i keep local database(.mdf file) so that i can distribute the \Bin\Debug or Bin\Release folder as my application to others and can be easily run on other system.

I wan doing this when i was using Access File. It vcan be kept with the exe and still be connected to.

The new system on to whih the app is distributed, must have SQL Server or SQl Server Express installed, and the app should be able to connect to the .mdf file using any login available on its SQL Server installation.

Any way whether it uses User Instance or not i just need two things

 - local database .mdf  file

 - authentication with a username/ password

---

Vibhor Agarwal

Answer 5

Vibhor,

Your second error is caused by the fact that there is already a database with the same name existing in your SQL Server instance. To have it work, you can first detach the database from SQL Server Express instance.

However if you run it second time, you may still encounter this issue. You need to make sure everytime you can connect to the database, you need to detach it before you close a connection.

You cannot separately distribute a .mdf file without first installing SQL Server Express. For your requirement, I think that Access database is proper for you. If you use SQL Server Express and want a username/password, I recommend that you just use server/client mode with the connection string like "server=xxx;database=xxx;uid=xxx;pwd=xxx;". For user instance, it is actually deprecated in the future SQL Server releases, so I do not recommend that you use it from now on.

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 6

@charles Wang

if i keep just keep it like u said

"server=xxx;database=xxx;uid=xxx;pwd=xxx;" what about the table i m connecting to.

The user who will run the on his system will not have the tables which i am using so i guesse i have two options

 ---> i have to distribute the database in the form of .mdf file to him so that he can connect to the dadtabase with the tables that i have provided

--->o the application has to create tables/views/etc by executing statments

I guess the first option is better i,e to distribute alredy created database and that will be in mdf

Finally is there any way i can distribute database with the app and the burden (technical)on user is minimum except that he shud have SQL Server or Express installed on his system and has to provide a username/password.

---

Vibhor Agarwal

Answer 7

dont worry if any feature is deprecated or not

my app is not going to be upgraded to new tech, same .net 2.0 and SQL serer 2005

---

Vibhor Agarwal

Answer 8

I guess the first option is better i,e to distribute alredy created database and that will be in mdf

Finally is there any way i can distribute database with the app and the burden (technical)on user is minimum except that he shud have SQL Server or Express installed on his system and has to provide a username/password.

As I have mentioned, unfortunately you cannot do this without installing a SQL Server instance. To reduce his efforts, you may consider letting your application help him install the SQL Server Express if he does not install it.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 9

As I have mentioned, unfortunately you cannot do this without installing a SQL Server instance. To reduce his efforts, you may consider letting your application help him install the SQL Server Express if he does not install it.

@Charles Wang

Will having the the SQL Server or Express installed beforehand on user's system work in this case. i.e...

SQL Server Express will be there on user's system but alredy installed as a requirement.

The application to be distributed will be supplied afterwards. POSSIBLE?

---

Vibhor Agarwal

Answer 10

My aim ---->

During Development

1. in a new database create some tables and views required by the app

2. Configure the database through wizard in VS 2005 to autogenerate DataSets and TableAdpater so that i can add them to my forms as controls through ToolBox.

3 Have a username passowrd to connect to the database.

During Distribution

1. the user has already .net framework & SQL Server installed bcoz these are the app requiremnts

2. Distribute the bin\Debug or bin\Release folder to a user

3. Distribute the database as mdf

3. user runs the app and is able to connect to database through the app

---

Vibhor Agarwal

Answer 11

Vibhor,

Your requirements can be implmented with the logic as follows:

1. Give your login a db_creator role in your SQL Server Express instance so that it has the permission to create a database.

2. During your application start time, first connect to the master database in your SQL Server Express instance with your login, check if the database is already existed, if not, attach your datatabase to the SQL Server instance (see the sample E in this article http://msdn.microsoft.com/en-us/library/ms176061.aspx), otherwise continue using it or detach it based on your requirements.

3. During your application close time, detach the database if it is required (http://msdn.microsoft.com/en-us/library/ms190794.aspx).

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 12

@Charles Wang

your last reply seems perfect for me thanx

but i have a few queries

Give your login a db_creator role in your SQL Server Express instance so that it has the permission to create a database.

that will be done but that role has to be given to a login using win-auth or sql server-auth?

During your application start time, first connect to the master database in your SQL Server Express instance with your login, check if the database is already existed, if not, attach your datatabase to the SQL Server instance (see the sample E in this article

Do i need to check if the database Archive is attached or not before detaching, In case it is not attached, will detaching throw an exception/error?

USE master;
GO
sp_detach_db Archive;
GO
CREATE DATABASE Archive
      ON (FILENAME = 'D:\SalesData\archdat1.mdf')
      FOR ATTACH ;
GO

Can i execute these statments from a c# program using Conn.ExecuteNonQuery();

---

Vibhor Agarwal

Answer 13

>> that will be done but that role has to be given to a login using win-auth or sql server-auth?

Either is OK, but from your description, it seems that you prefer SQL Authentication.

 

>>Do i need to check if the database Archive is attached or not before detaching, In case it is not attached, will detaching throw an exception/error?

Since the login is a dbcreator so it is the database owner of the database by default and surely it can detach the database. The T-SQL script is like this:
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE database_id=DB_ID('Archive'))
 exec sp_detach_db 'Archive'
ELSE
BEGIN
CREATE DATABASE Archive ON
(FILENAME='D:\SalesData\archdat1.mdf')
FOR ATTACH
END

>> Can i execute these statments from a c# program using Conn.ExecuteNonQuery();

Absolutely :)

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 14

@charles wang

thanx a lot sir. especially for that T-SQL script

 

---

Vibhor Agarwal

Answer 15

will try this shortly as my laptop bceomes available to me nad report back in case i face any issue

thanx

---

Vibhor Agarwal