SQL Express connection string hell ASP.Net

Question

SQL Express 2005 is running locally. I have a project written by another person running on the same machine. All I want to do is connect to it, can't be that hard right?

This is the one I use in my old classic ASP code to hit another database running on the same instance:

Provider=SQLOLEDB;Data Source=MYLAPTOP\MSSMLBIZ;Persist Security Info=True;User ID=TestUser;Password=letmein;Initial Catalog=TestDB

But trying a version of that makes the .net code throw a wobbler as he's written it using SQLServer drivers so its not liking the Provider stuff.

Here is the orginal connection string from his code:

Server=(local);Initial Catalog=TheDatabase;User Id=TheUser;Password=ThePassword;

I've been to http://www.connectionstrings.com/sql-server-2005 and tried several of the options from there, these all get "SQL Server does not exist or access denied" (what a lovely mixed error message that is!):

Data Source=localhost;Integrated Security=True;Initial Catalog=TheDatabase
Data Source=localhost\SQLEXPRESS;Integrated Security=True;Initial Catalog=TheDatabase
Data Source=MyLaptop\SQLEXPRESS;Integrated Security=True;Initial Catalog=TheDatabase
Server=MyLaptop\SQLEXPRESS;Initial Catalog=TheDatabase;User Id=TheUser;Password=ThePassword;

I've created logins for MyLaptop/IUSR_MyLaptop, MyLaptop/ASPNET, MyLaptop/IWAM_MyLaptop in SQL Express and given them all read/write permissions to my DB and set their default DB to be TheDatabase.

What the heck am I doing wrong and how can I debug the problem some more?

UPDATE: Special Thanks to Chris for all his pointers, got there in the end, if you are having similar problem please read all the comments there are lots of links and tips on how to track them down.

May be a daft question but what is the actual instance name of the sql express server you're trying to connect to? Is it localhost\sqlexpress or something else? Cut the app out of the equation and just try to connect to sql via sqlcmd by doing 'sqlcmd -S servername\instancename -E' in a command prompt and see if it connects. — Chris W
The instance is actually called MSSMLBIZ but left it as SQLExpress for clarity. Tried teh above and got this: SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired. Checked in Server Config and all it switched on? — Pete Duncanson
Going to try a reinstall to try to reset everything and start again... — Pete Duncanson
Reinstall was a pig! Thanks to this article for the help incase anyone else gets the "The SQL Server failed to start" error on Win XP SP2: support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B920114 — Pete Duncanson

Chris W Chris W · Accepted Answer · 2009-07-23T12:13:01

Can you advise exactly what is in the config?

Are you using the block - in which case a valid connection string would be:

<add name="connection" providerName="System.Data.SqlClient" connectionString="Data Source=localhost\MSSMLBIZ;Initial Catalog=TheDatabase;Integrated Security=True" />

or

<add name="connection" providerName="System.Data.SqlClient" connectionString="Data Source=localhost\MSSMLBIZ;Initial Catalog=TheDatabase;Integrated Security=False;User Id=TheUser;Password=ThePassword;Application Name=AppName;" />

Or are you getting the connection string from app settings - in which case I guess your provider is set in code inside the app itself?

SQL Express connection string hell ASP.Net

4 Answers