Login failed for SQL Server user

Question

I know this question has been asked before on here, but none of the solutions seem to have helped. I am migrating a database from one machine hosting SQL Server 2008 Express to another. Basically the exact same configuration. I backed up my database from Old Server to New Server. When I try to run my application, I'm told "Login failed for user 'sqluser'." I've compared the settings from Old Server and New Server and they are identical as far as I can tell. If it helps, here is my connection string:

<add name="RetailCrimeConnectionString" connectionString="Data Source=NewServer\SQLEXPRESS;Initial Catalog=RetailCrime;Persist Security Info=True;User ID=sqluser;Password=AwesomePassword"
        providerName="System.Data.SqlClient" />

If I switch the connection string back to OldServer, my application is happy again.

The user sqluser is datareader and datawriter on RetailCrime -- just like he is on OldServer. The application is clearly connecting because the error changes to a connection error if I change the data source to something purposely incorrect. I've gone through the SQL instance settings and I think everything that needs to be enabled is enabled. I've even added a firewall exception (a step I didn't need to take on OldServer). And I have re-created the database and created a test one on NewServer with the same results.

Is there anything outside the normal fare of Google results that I may have missed? Let me know if there is any other info you need. I wasn't sure what other details to include.

Thanks!

Answer 1

Try deleting "NewServer", and add "." instead. If it's not on the same machine, put IP address of NewServer in place of the ".", like "Data Source=10.10.46.15\SQLExpress". Like below:

<add name="RetailCrimeConnectionString" 
    connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=RetailCrime;Persist Security Info=True;
User ID=sqluser;Password=AwesomePassword" providerName="System.Data.SqlClient" />

You also need to make sure your TCP/IP communication is enabled. This is for 2012 but step should be similar, See steps:

1. On the Start menu, click All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager.
2. Click SQL Server 2012 Services.
3. Expand the SQL Server 2012 Network Configuration node, and then select Protocols for MSSQLServer (SQL Instance Name).
4. Right-click TCP/IP, and then click Enable.
5. Select SQL Server 2012 Services in the tree.
6. Right-click SQL Server (SQL Instance Name), and then click Restart.

Update:

Last thing to try, maybe:

Go to Start > Microsoft SQL Sever 2012 folder > Configuration Tools > Open SQL Server Configuration Manager

Make sure everything on the list in SQL Server Services is not Stopped. And make sure the start Mode is set to Automatic, not manual.

Answer 2

I figured it out. It turns out that I set the server to only accept Windows authentication and not SQL Server accounts when I set it up. Once I set that in SMSS, the web application started accepting SQL login with no trouble. I came to this idea when I realized that I could connect by impersonating Windows accounts. Thanks for the ideas! Merry Christmas!

Answer 3

Have you created the SQL Login for sqluser and mapped it to the database user?

You only mention the database user in your question.