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 a developer and a database expert. I have been working with several of the Google APIs, since 2012. I helping others in the On-Line community to develop with the Google APIs, by creating my own blog www.daimto.com. This and my presence on a number of On-Line developer forums lead me to be noticed by the Google Analytics API development team. I was nominated for and recently became one of the first Google Developer Experts for Google Analytics.

Leave a comment

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