Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
    
Do you use windows authentication in both SSMS and your app? –  Amiram Korach Nov 12 '12 at 14:31
    
@AmiramKorach: I've tried Windows auth and SQL auth in both, same results. –  David Nov 12 '12 at 14:33
    
Maybe your connection string is wrong. Please add it. –  Amiram Korach Nov 12 '12 at 14:35
1  
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 Nov 12 '12 at 15:10
1  
> 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) –  sgmoore Nov 12 '12 at 15:15
show 1 more comment

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
I named the instance sqlexpress2012 during the installation. –  David Nov 12 '12 at 14:50
    
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 Nov 12 '12 at 15:05
    
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 Nov 12 '12 at 15:07
    
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 Nov 12 '12 at 15:09
    
Still no luck. It's definitely connecting to the SQL server because it's getting back a SQL error message. –  David Nov 12 '12 at 15:13
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.