Unable to Connect to Localhost SQL Server Express Instance

Problem

Connecting programmatically or by using osql failed with the following, generic error:

error: 40 - Could not open a connection to SQL Server

My programmatic attempt looked like this:

string cs = "Data Source=localhost;" +
            "Trusted_Connection=true;" +
            "Initial Catalog=dbname;";
SqlConnection connection = new SqlConnection(cs); 
connection.Open();

Other variants I tried for Data Source were (local), 127.0.0.1, and the fully-qualified name of my local server.

My osql attempt looked like this:

c:\>osql -E -S localhost

Solution

The solution, at least for the basic connection problem, turned out to be a missing instance name. The instance name can be found by looking in Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager, under SQL Server 2005 Services. In my case, the instance is named “SQLEXPRESS“, so I tacked this onto my connection strings for a (more) successful login.

Programmatic:

string cs = "Data Source=localhost\\sqlexpress;" +
            "Trusted_Connection=true;" +
            "Initial Catalog=dbname;";
SqlConnection connection = new SqlConnection(cs);
connection.Open();

osql:

c:\>osql -E -S localhost\sqlexpress
1> 

Authorization

Actually, the above code didn’t quite work. I still get an authorization error:

Cannot open database "dbname" requested by the login. The login failed.
Login failed for user 'DOMAIN\username'.

This was actually because I hadn’t yet created the database, nor a user! I followed this handy guide in order to create a database, a user, and grant the user access to the new database.

In the end, my connection code looks like this:

string cs = "Data Source=localhost\\sqlexpress;" +
            "Trusted_Connection=true" +
            "Initial Catalog=dbname;" +
            "user id=username;password=userpass;";
SqlConnection connection = new SqlConnection(cs);
connection.Open();