So i had installed SQL Server 2012 on my machine and i had used it from Visual Studio all worked fine. Till i decided to give access of my database to one of my team mate. When he tried to login he got the following error.
I was confused , had the SQL login created and user with appropriate rights created on the database. But a quick look into server logs provided more details. The severity and state is not provided in the error message for security purposes. Just in case some one is trying to hack into your SQL Server.
As you can see from SQL Server Logs the severity is 14 and state is 58. Now the fix i am going to describe will work only for state 58. For other states you can refer to the following link.
The states have some form of description in codes. Although not elaborate you can make sense of the codes. Like in my case the state was 58 which WinAuthOnlyErr. This made it clear to me that when i had installed the SQL Server i did not set the authentication to mixed mode instead i kept it as windows only. Changing it back to windows was not that difficult at all.
All you need to do is follow few simple steps.
On the SQL Server do a right click and go to properties. This will take you to the following page
On the Server Properties go to security settings and change the Server authentication to SQL Server and Windows authentication mode.
After you have done this change restart the SQL Server Service. “ CAUTION: DO NOT Do this on live server without scheduling down time”
Also, the following page tells you how to trouble shoot the problem. Check the bottom of the page for detailed description of the states as well.