Every once in awhile I need to reinstall SQL Server. Every time after I reinstall SQL Server and try to run my project executable, I would run into a connection error which says I cannot login with my specific user name.
First – Ensure your Login user is Added
The first thing I would do is to ensure my restored database has the right user. In fact, it has to be a user from the SQL Server security group, and not the one from the Database security group.
In the following example, the database apts_dev4.0 has a user name call “MentorStreetsDBUser”. Under the DICKYS2\SQLEXPRESS security, it also has “MentorStreetsDBUser”. However, these two users are not the same at all. The one in the database is created by my previous SQLServer. This logon will not work for the newly installed SQLServer.
For this part, I simply remove the MentorStreetsDBUser from the database, and add a new user by specifying MentorStreetsDBUser from the SQL Server. This way I should able to logon using this account name. Make sure you give this user the proper security rights.
But Wait… I still get a Login Fail message with this user!
Second – Ensure SQL Server Authentication Mode is On
This is the part where I usually forgot to do.
Right click on the SQL Server select Property. Click the Security section. You will see the following:
In the Server authentication, select both “SQL Server and Windows Authentication mode”
By default, SQL Server only allows Windows Authentication login, and not the SQL Server login. After restart SQLServer, everything will work again.
Conclusion
First, I would hope that SQL Server Management would at least so a warning letting me know that the imported database user would not able to log on.
Second, I think that MS SQL Server should default the Server authentication to both the “SQL Server and Windows Authentication Mode”.
In addition they should provide better error message. I personally don’t see a big security issue if you at least tell the user that "SQL Server Mode Login is not supported”.