As you know there are two ways to connect to sql server.
- Windows Authentication
- SQL Server Authentication
Windows Authentication uses the logon on your pc to access the database server. While SQL Server Authentication required that you enter a login and a password.
The picture above shows what happened when I tried to login to SQL Server with a new user I created. Error 18456. This user has all the permissions it needs so why can’t I login? Well this message is intentionally very vague, we don’t want to give people to much information that they could use against us. As the DBA for the server you need to check your logs. Here is what i found.
Well at least i have something i can Google now. State 58 is what is important here. Basically this means that the Server is not set up to accept “SQL Server Authentication” its set to “Windows Authentication” only.
In management studio right click the Server and go to properties. Select “Security” on the right. Change the radio button to “SQL Server and Windows Authentication Mode”. Click ok. You will need to restart SQL server for the changes to take effect.
Now you should be able to Login using either “Windows Authentication” or “SQL Server Authentication”