SQL Login fails for app but not for SSMS

Question

I wrote a simple test application in C# (4.5) which does nothing more than connect to a database and display a message. (Basically just to test the deployment and DB connection.) On my local workstation everything works fine. I installed SQL Express 2012, connect as the current user (integrated security), no problems.

However, when deployed to Windows Server 2008, the application can't connect to the database. It keeps getting a login failed error.

On the server I followed the same installation for SQL Express 2012. I'm logged in as Administrator when I run the console application. I even tried turning on mixed mode authentication, creating a user, and connecting as that user. I can connect in SQL Management Studio, but the application always gets a login failed error.

Is there some hidden security setting in Windows Server 2008 which I need to modify to allow a .NET application to connect to a SQL server? What might cause this behavior where I can login to the database via SSMS but not via an application?

Error:

System.Data.SqlClient.SqlException (0x80131904): Cannot open database "TestDatabase" requested by the login. The login failed. Login failed for user 'sa'.

Naturally, this is the version of the error when I tried connecting as sa as sort of a last ditch effort for testing. The same error happens for TestUser (a SQL user I created for this) as well as PERSONALSERVER\Administrator (the current logged in user running the application).

The connection strings I've tried are:

• Data Source=localhost\sqlexpress2012;Initial Catalog=TestDatabase;Integrated Security=True
• Data Source=localhost\sqlexpress2012;Initial Catalog=TestDatabase;User Id=TestUser;Password=testpassword

Answer 1

First, I've never seen an SQL express instance use the year. Typically it is always localhost\SqlExpress

In configuration manager, ensure TCP/IP and Named Pipes are enabled.

In control panel, try and create a SQL connection with the SQL Server Driver (under Administrative tools) -> Data Sources as this will just test a local connection with very few variables (eg, your code (which I'm sure is written perfectly :) ))

Also, do you have 2 places you are referencing your connection string (I've done this); it was saved in my app.config file and as a string in my code (or even a resource string).

Connect via IP address instead of computer name (this means updating your connection string too - details on how at bottom of this post).

Lastly, update your connection string to

Data Source=.\sqlexpress2012; Initial Catalog=TestDatabase; Integrated Security=SSPI; User ID=myDomain\myUsername; Password=myPassword;

or try it with:

Server=.\sqlexpress2012; Database=TestDatabase; Trusted_Connection=True;

I will assume the user has efficient rights to the database in question. :)

Ensure the SQL ports, typically 1433 and 1434 are open.

Log on to SSMS with the username/password to ensure they do have permissions.

www.ConnectionStrings.com - Great resource for connection strings! :)