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?
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
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! :)
User contributions licensed under CC BY-SA 3.0