CODEWITHSUNDEEP
sql-servervisual-studio-2010sql-server-2008-r2ssmsconnection-string
StatsViaCsh
StatsViaCsh

Reputation: 2640

SQL Server Express troubles connecting via C# and SSMS

I'm having some problems consistently connecting to my database via a connection string from Visual Studio 2010, and in SSMS. The connection string worked reliably for weeks... when I worked with the database in SSMS, the connection string stopped working. "Login failed" error. In my efforts to apply the solutions given to other people with extensive searching, I was able to connect, once, by detaching the database in SSMS. I restarted SSMS and the database appeared in object explorer, but the full path was given as the name. Now I can't work with the database, as you can see below in object explorer:

enter image description here

I'm not sure if it needs to be attached, as I've tried and that generates an error as well. So I'd really appreciate a little guidance on using an SQL Server 2008 R2 Express database from .NET, as well as SSMS. I'm the only user, it's a local instance, and simplicity and reliability would trump any custom security settings. If it's of interest, here's the connection string I've been using:

connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Market.mdf"";Integrated Security=True;Connect Timeout=600;User Instance=False";

There is an abundance of forum posts/ solutions as well as articles on this exact issue, but I haven't been able to get anything I've found thus far to work. Punting... can anyone get me on track?

Upvotes: 1

Views: 857

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

Detach the database (right-click, detach).

Open a New Query Window.

Attach the database.

CREATE DATABASE Market ON 
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Market.mdf')
FOR ATTACH;

Now you can connect to the database by name. Your connection string should be:

connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=Market;Integrated Security=True;";

You should rarely, if ever, want to use the AttachDbFilename and User Instance features on purpose. These are the cause for all of the Google hits you came up with.

Upvotes: 2

Related Questions