c# 2008 SQL Server Express Connection String

Kiril picture Kiril · Jun 15, 2009 · Viewed 69k times · Source

I have a 2008 SQL Server Express installed on one of my machines and I'm attempting to establish a remote connection... when I use the MS SQL Server Management Studio I can log into the database without any problems at all (with the same credentials), but when I try to create a connection string in my C# application I get an exception:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

Here is what my connection string looks like (the private information is changed):

"Data Source="MACHINENAME\\SQLEXPRESS";User ID="Admin";Password="the_password";Initial Catalog="MyDatabase";Integrated Security=True;Connect Timeout=120");

As I said, I can login using the Management Studio with the same settings: same user id, password and data source name, but it fails when I attempt to open a connection with the above connection string.

Note:

  1. I have enabled the remote connectivity on the server, disabled the firewall, enabled TCP/IP connection to the server, turned on the SQL Browser.

  2. The connection string works fine when I'm on the same machine.

  3. I looked up the Integrated Security option and I set it to false just to make sure that it's not attempting to use the Windows Login, but it still fails.

  4. The database is setup to allow both windows login and database login.

  5. I changing the Integrated Security option to SSPI, True, and finally False, all 3 gave me the same error as above.

Can anybody tell me if I'm doing something wrong?

UPDATE, here is my exact code (this time only the password is removed, and I've added a picture of management studio running on the same machine):

string _connectionString =
            //string.Format("Server=%s;User ID=%s;Password=%s;Database=%s;Connect Timeout=120", // Same problem
            //string.Format("Server=%s;User ID=%s;Password=%s;Database=%s;Integrated Security=False;Connect Timeout=120", // Same problem
            string.Format("Data Source=%s;User ID=%s;Password=%s;Initial Catalog=%s;Integrated Security=False;Connect Timeout=120", // Same problem
            "GANTCHEVI\\SQLEXPRESS",
            "FinchAdmin",
            "the_password",
            "Finch");

Connected Via Management Studio: See Picture http://s113.photobucket.com/albums/n202/ozpowermo/?action=view&current=ManagementStudio.jpg

http://s113.photobucket.com/albums/n202/ozpowermo/?action=view&current=ManagementStudio.jpg

I FIGURED IT OUT:

When using the "Data Source=" label one should use the "User Id", if you use User ID it doesn't seem like it works!

string _connectionString = "Data Source=GANTCHEVI\\SQLEXPRESS;Initial Catalog=Finch;Integrated Security=False;User Id=FinchAdmin;Password=the_password;Connect Timeout=0";"

Answer

eu-ge-ne picture eu-ge-ne · Jun 15, 2009

Remove Integrated Security=True from your connection string and (optional) add Persist Security Info=True;

From MSDN:

Integrated Security - When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.