Error number 18456 sql server

Input : select * from sys.sysmessages where error = 18456

Output: Login failed for user ‘%.*ls’.%.*ls%.*ls

This is one of the infamous error message (and number) that most of the DBAs and developers have come across while working on SQL server. This message simply denotes that the client call was able to reach the SQL server and then an ACCESS was denied to the particular login for a reason. To figure out the exact reason, this error number 18456 with its STATE number is logged into the SQL server error log file, if SQL server was allowed or configured to capture the failed logins.

Configuring SQL server for capturing login failures:

By default, SQL server is configured to capture only failed logins but it can be changed to any of the options as mentioned in this books online link http://technet.microsoft.com/en-us/library/ms188470.aspx

Below figure shows these options to have login failed messages written into error log

Accessing Error log:

Now that, we know SQL server logs all login failed messages into its error log and windows event viewer but how do I access them?

Windows event viewer:

GO to start –> Run –> Eventvwr –> open up the application logs, and now we could see the login failed error message with computer name, instance name, date and time and finally the reason for the login failed

SQL Error log:

SQL server error log can be viewed from multiple places

• If we have gained access or can gain access to SQL server with different logon credentials then always use sp_readerrorlog, xp_readerrorlog or use the GUI option of opening up Management node –> SQL server Logs –> View –> SQL server log. Same sample shown above looks like

• In a case where we cannot gain access to SQL server, then we may use the actual error log path and open the txt file physically from the file system. Use SQL server configuration manager to find the error log path and from there you could open the file. Get the value next to –e parameter and that gives the actual error log file location

Typically the error log files are available in install directory for SQL server.

SQL server 2005:

C:MSSQLMSSQL.1MSSQLLOGErrorlog

SQL server 2008:

C:MSSQLMSSQL10.instanceIDMSSQLLogErrorlog

InstanceID – MSSQLSERVER for default instance and for named instance it’s the name of the instance

STATES of 18456

State 1:

This is a very generic error message that is sent to the client tools to deliberately hide the nature of the login failure issue. However, the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition with its state number.

State 2 and 5:

This state occurs when a SQL server login logs in with the name that doesn’t exist in sql server. This error mostly comes in when users specify wrong user name or misspell the login name. I am logging in to my instance with a login name called DOESNTEXIST that really doesn’t exist and let’s see what the error state in error log is.

Error: 18456, Severity: 14, State: 5.

Login failed for user ‘DOESNT EXIST’. Reason: Could not find a login matching the name provided. [CLIENT: ]

State 6:

This state occurs when a user tries to login with a WINDOWS account but specifying that as a sql server account. I have a windows account test (domaintest) but I am specifying it as a sql account and trying to login into SQL server, let’s see what state the error log has

Error: 18456, Severity: 14, State: 6.

Login failed for user ‘domaintest’. Reason: Attppting to use an NT account name with SQL Server Authentication. [CLIENT: ]

State 7:

This state occurs when a wrong password is specified for a login which is disabled too. In this case, my SQL server user ‘Leks’ is disabled and I’m mentioning a wrong password for the connection

Error: 18456, Severity: 14, State: 7.

Login failed for user ‘Leks’. Reason: An error occurred while evaluating the password. [CLIENT: ]

For the accounts (logins) that are disabled and if you specify the correct password, the error log is logged with 18470

Error: 18470, Severity: 14, State: 1.

Login failed for user ‘Leks’. Reason: The account is disabled. [CLIENT:]

State 8:

This state occurs when password is not correct in the connection string for any SQL server authenticated logins. I’m logging into SQL server using ‘sa’ account with wrong password

Error: 18456, Severity: 14, State: 8.

Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT:]

State 9:

This state means that the password was rejected by the password policy check as an invalid one. The policy API has rejected the password with error NERR_BadPassword. See more info on this error and visit http://msdn.microsoft.com/library/default.asp?url=/library/enus/netmgmt/netmgmt/net_validate_output_arg.asp

State 10:

This is one of the rare state and is very well documented here – http://support.microsoft.com/kb/925744

State 11 & 12:

This state means the login was valid but server access failed. One such example is when a windows login in trying to access sql server that wasn’t explicitly added to sql server (at least starting from 2008). To overcome this error, you can add that domainwindows account to sql logins explicitly.

Other reasons for this to happen are when a login is denied access (revoking connect permissions to SQL) to SQL server and UAC issues.SQL server product team covered this state extensively here –http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

Windows account which do not have access

Error: 18456, Severity: 14, State: 11.

Login failed for user ‘domainuser’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]

SQL account that was denied access

Error: 18456, Severity: 14, State: 12.

Login failed for user ‘Leks’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]

State 13:

This state occurs when any login tries to access to sql server with services paused on it. But for this reason, there will also be other error number 17142 logged along with 18456.

Error: 17142, Severity: 14, State: 0.

SQL Server service has been paused. No new connections will be allowed. To resume the service, use SQL Computer Manager or the Services application in Control Panel.

Error: 18456, Severity: 14, State: 13.

Login failed for user ”. Reason: SQL Server service is paused. No new connections can be accepted at this time. [CLIENT:]

State 16:

This state occurs for logins that do not have access to the target database or the database doesn’t exist anymore with the same name or offline. We always specify the database in the connection string as initial catalog or using -d parameter.

This can be fixed by granting access to the database and sometimes orphan users existing in the database. Orphan users can be fixed by sp_change_users_login

This state occurs in SQL server 2005 and same is changed to 40 in SQL server 2008 and above

Error: 18456, Severity: 14, State: 16.

Login failed for user ‘Leks’. [CLIENT: <local machine>]

State 18:

This state occurs when a sql login is added with USER MUST CHANGE THEIR PASSORD ON FIRST LOGON or a login that needs to have its password changed as per the domain or windows password policy ; this state gets in to error log when the new password box prompts up.

This error is logged with state 18488

Error: 18488, Severity: 14, State: 1.

Login failed for user ‘Tester’. Reason: The password of the account must be changed. [CLIENT: ]

State 23:

This state can happen due to couple reasons; first being simultaneous action of shutting down SQL SERVER and any incoming logins to sql server takes place. The other one is documented here as an issue http://support.microsoft.com/kb/937745

State 38:

This is similar to state 16 but this was introduced from SQL server 2008 and above.

Database doesn’t exist or login doesn’t have access to the database.

Error: 18456, Severity: 14, State: 38.

Login failed for user ‘Leks’. Reason: Failed to open the explicitly specified database. [CLIENT: ]

State 40:

This state occurs when the login’s default database doesn’t exist in SQL server or offline or the login doesn’t have access to the default database. This state is always logged alongside with error 4064

Error: 18456, Severity: 14, State: 40.

Login failed for user ‘Leks’. Reason: Failed to open the database specified in the login properties. [CLIENT: ]

State 58:

This state occurs when a SQL server login is used for accessing SQL server when SQL server is installed with windows authentication mode.

You may have to use only windows login or change the authentication to mixed authentication for this to work – http://msdn.microsoft.com/en-us/library/ms188670.aspx. The authentication mode change always requires a SQL restart to come into effect.

Error: 18456, Severity: 14, State: 58.

Login failed for user ‘sa’. Reason: An attppt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

I have tried and covered most of the states that I know of, and if you find states that I haven’t discussed happy to hear and add to this list. Apart from the error 18456, there are other login failure errors that you might have to keep an eye on

Happy “login failure “troubleshooting

Read time 6 minutes

“We have been using SQL Server to manage our databases for a long time and update it with the latest version as soon it gets updates. But one of our team members started getting the error that login failed for the user. We always use SQL Server authentication and provide valid credentials. But the user is not able to connect with the server.”

The above issue can happen to any SQL Server user and make it impossible to access your server and the saved databases. Generally, the user name is accepted but the password is considered false. And the error message displayed does not define the reason. However, administrators can check the error details in the event log to get a clear picture of the problem.

More About Server Error Code 18456

Microsoft SQL Server error code 18456 denotes a failed login attempt which may be due to an incorrect password entry. Generally, it happens even if the username is accepted. And the error message displayed does not define the reason. However, administrators can check the error details in the event log to get a clear picture of the problem.

This error may be interpreted as a mechanism to prevent unauthorized persons from accessing the server by guessing the credentials. But genuine users may find it hard to overcome the problem.

Probable causes and of SQL server login error code 18456

• The username may be misspelled
• The user account may not exist in the SQL Server.
• The authentication mode would be different. Perhaps you are using SQ Server Authentication but Windows Authentication set for the user.
• The username and password may have changed and the user should contact the administrator for the new credentials.
• If the error is not showing any description but shows that the administrator has not authorized you to get the details of the error. Contact the administrator and get help with the issue.
• The password of your SQL account is incorrect and the administrator may assign you the new credentials.

If the above solutions do not work for you, then you should run some basic troubleshooting methods.

How to protect your password during SQL Server authentication?

Unlike Windows Authentication, the SQL Server authentication method does not use user account credentials but creates its credentials in the SQL Server. The user must provide the username and password each time they try to access the SQL Server Management Studio using SQL Server authentication. There are some tips related to managing passwords for it.

• It would help if you changed the password routinely to protect it from unauthorized access. SQL Server has a policy where “the user must change password at next login” each time they open the application.
• A password expiration policy sets the number of days for the password’s validity. After that, you must change it.
• The DBA can choose the Windows password policies that put the parameters for password length and inclusion of complexity level.

Resolution to SQL error code 18456

When an SQL Server is started for the first time, there is a possibility that ‘Windows authentication’ is enabled under the security option. In such a situation, the server will not recognize the user and the user will get the failed login 18456 error. If you want to login through ‘SQL Server Authentication’, then enable it by following the steps:

1. Go to the Properties of the selected server (from the right-click menu).
   
2. Now go to the Security page and under Server authentication choose the option SQL Server and Window Authentication mode.
   
3. Now expand Security > Logins and right-click the server name, and select Properties.
4. In the Login properties dialogue box, provide the password details (under SQL Authentication).
5. Now go to the Status tab, and choose Enabled under Login.
6. Click OK and restart the SQL server.

But if you want to use the ‘Windows authentication’ option, then it will be better if an administrator provides admin rights to the user, so that he must be able to log in without any hassle.

1. At first, login to SQL Server as an administrator, and go to the server where the particular user account is residing. Expand the Logins, right-click on the desired account and click New Login.
   
2. In the Login – New window, click Search.
   
3. Now type the username of the account which you have selected in the Logins folder and then click Check Names. Click OK.
   
4. Now back in the Login – New window, go to the Server Roles tab and check the sysadmin option.
   
5. Now go to the Securables tab and verify the role addition.

All the above-mentioned methods are used to overcome login problems. However, they cannot help when the SQL database is corrupt or MDF files are inaccessible. To recover SQL data and to repair the MDF files in such cases, you need an efficient MDF recovery tool like Kernel for SQL server recovery. It scans, analyzes, and repair MDF or NDF file and brings back all the precious data within no time.

Conclusion

Microsoft SQL Server error code 18456 for failed login attempt by a user is annoying to authenticated users and therefore its probable reasons are discussed along with two login enabling methods via Windows Authentication or SQL Server Authentication are explained to users to overcome the situations. For users encountering other SQL Server errors due to corrupted database files, a popular and reliable SQL Database Recovery tool is suggested as well.