Sql Server Authentication – Error 18456


Please Share

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.

LoginFail

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.

logonFailedAdmin

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.

sqlserverproperties

Now you should be able to Login using either “Windows Authentication”  or  “SQL Server Authentication”

 

Please Share


Linda Lawton

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as an application developer and a database expert. I have also been working with Google APIs since 2012 and I have been contributing to the Google .Net client library since 2013. In 2013 I became a a Google Developer Experts for Google Analytics.

Leave a comment

Your email address will not be published. Required fields are marked *