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

Solution

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! :)

Comments

[David]

@AmiramKorach: I've tried Windows auth and SQL auth in both, same results.

[Amiram Korach]

Maybe your connection string is wrong. Please add it.

[sgmoore]

Can you check the Sql Server's log? I would not have expected a 'login failed' message unless you are actually talking to the sql database.

[sgmoore]

> 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? Easy way to disprove this is downloading and trying LinqPad (from linqpad.net)

[user46193]

have you got it working?

[David]

Lots of good advice :) TCP/IP and Named Pipes were indeed disabled, but enabling them doesn't seem to change the result. The Data Sources test worked, but the application still doesn't. (Makes me wonder if it's something about the .NET Framework's trust level or something like that.) It's definitely using the connection string from the config file, because when I change it the resulting error changes (username, etc.). The user has permissions to the DB, and can interact with it via SSMS without problems.

[David]

Linq to Sql. The app is about as simple as it gets, it just creates a data context and queries the first row from the only table in the database. I'm logged in as Administrator, so it should be running as such...

[David]

Do they need to be manually opened on the local host? I'm running the app on the same box that the database lives on (via RDP).

[David]

Still no luck. It's definitely connecting to the SQL server because it's getting back a SQL error message.

[Dave]

Are you running this in debug in VS or from an exe?

[Dave]

And do me a favor; Just create a new password for this, and update both the connection string and the SQL server's. Also, ensure you're connecting to the correct instance (Again, I'm not being rude, I know this as I've done it :) )

[David]

So I'm not entirely sure how, but it's working now. Maybe I previously had a typo in the connection string and I corrected it without even noticing it while testing different strings? Anything's possible. At this point I'll try to reproduce the error just to narrow down what was wrong so I can have a more complete question/answer here. But at the moment it's just working for reasons unknown (or, rather, not failing for reasons unknown). Either way, thanks!