Can you help me decipher this SQL Server issue and correct it? Check out this tip to learn more.

Solution

Error number 18456 indicates a login failure. To troubleshoot the SQL Server login failure we need to determine the state of the error message. In this tip we are going to troubleshoot error messages with an error state of 10.

Error State 10 indicates that password checking could not be performed and the login failed. Let's review the list of steps below to try to fix the issue.

Troubleshooting SQL Server Login Failures - Error State 10

Step 1: First let's check whether the SQL Server login is locked, expired or requires a password change. In order to accomplish that, we will use the Login Property function which will give us information about the login policy setting. Login Property function returns 1 or 0 as output.

If the account is locked, you can use the below script to unlock the account.

ALTER LOGIN [sqldbpool] WITH PASSWORD = 'password' UNLOCK

If the account is expired, use the below script to set it to an active state.

ALTER LOGIN [sqldbpool] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

If the login must change password on next login is true, you have to reset the password.

Step 2: If there are no issues with the SQL Server login then the next step is to check the SQL Server Domain Service account status. Let's check these properties of the Service Account:

Service Account is locked

Service Account is disabled in domain

Account is expired

Need to change password

To check the above properties of domain account, follow the below steps:

Go to Start | Run and then type "dsa.msc". That command will open the Active Directory Users and Computers console.

Next right click on the active directory group in the console and click on "Find". Type the service account name in the find dialog box and click on the "Find Now" button.

Right click on the service account name from the search result --> Select Properties --> Go to Account tab. Here you have to check whether account is locked out or expired or disabled in domain or needs the password changed.

If you find any of the above issue, you have to fix it accordingly.

Step 3: If everything in step 2 is error free, you have to check the ring buffer for any security errors. Execute the below query to check the security error log.

From the output we can see the two hexadecimal error codes 0x8C5 and 0x139F. You have to convert these error code into decimal value, which will be 0x8C5 = 2245 and 0x139F = 5023.

Check the above decimal error codes using the NET HELPMSG command.

Net HelpMsg command output indicates that, Enforce Password Policy is in place, but the SQL Server Login password does not meet the password policy requirements. Changing the password as per password policy requirement will fix the issue.

Next Steps

Add the Security Ring Buffer Check in to you daily DBA Checklist and troubleshoot the error if you face it.

Remove SQL Server Service account from the account expire and account lock out policy.

If possible use the different service accounts for different environments.

Last Update: 2012-04-27

About the author

Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

when i Run dsa.msc command , it shows an Error saying that Windows cannot find 'dsa,msc' . Make sure you typed the name correctly and then try again. I am using SQL Server Management Studio(SSMS) 2012 version . Please help me

Thanks for this article which was really helpful to solve this error. I also shared other way to eliminate such login error read this which helps you more:- http://www.sqlrecoverysoftware.net/blog/sql-login-error-18546.html

Great article, probably one of the best on the web discussing this topic.I noticied that you addressed the first error code from the ring_buffers outbut but you did not mention any possible cause for the second one (0x139F = 5023). We are seeing a lot of records with this error code and I was wondering what your views on that code were?

created a linked server between two sql servers with the option be made using the logins current security context,

User is using service account to access this linked server, when he was remotly connecting to the 1st sql server to access linked server, it was getting success,

but we can't provide remote access to the service account permenantly it is limited,

So, he wass using service account from his laptop to access the linked server, getting below error, Service account has access on his laptop and it has db owner access both SQL Servers.

I didn't get any solution for this issue, Could you please help me on this tio understand how to resolve the issue.

The test connection to the linked server failed.

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd) at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection() at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()

This article is straightforward and provides great examples of troubleshooting a specific problem. Good job!

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.