solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Server Login Failure Error 18456, Severity 14, State 10

MSSQLTips author Jugal Shah By:   |   Read Comments (12)   |   Related Tips: More > Security
Problem

Recently I experienced a situation where a SQL Server login failed with this error message:
Error: 18456, Severity: 14, State: 10.
Login failed for user 'SQL_Login'. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX].

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.

Review the SQL Server Error Logs for Login Failures

Execute the query below to determine the different login failures.  For more information about the xp_readerrorlog extended stored procedure, review Reading the SQL Server log files using T-SQL.

EXEC xp_readerrorlog 0,1,"18456",Null

In our example, the login failure error message is show below:

Error: 18456, Severity: 14, State: 10.
Login failed for user 'SQL_Login'. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX].

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.

  • 1 indicates that login is in the specified state (True).
  • 0 indicates that login is not in the specified state (False)
select loginproperty('SQLDBPool','isexpired')
select loginproperty('SQLDBPool','IsLocked')
select loginproperty('SQLDBPool','IsMustChange')

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.
Launch 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.

Active Directory Console to check if the login is locked out, password is expired, etc.

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.

Check the Ring Buffer in SQL Server 2008

SELECT CONVERT (varchar(30), GETDATE(), 121) as Run_Time,
dateadd (ms, (ST.[RecordTime] - sys.ms_ticks), GETDATE()) as [Notification_Time],
ST.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC

Check the Ring Buffer in SQL Server 2005

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - ST.[RecordTime]), GETDATE()) AS NotificationTime,
ST.* , sys.ms_ticks AS [CurrentTime]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC


SQL Server Ring Buffer Output

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.

Command Prompt Net HelpMsg

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: 4/27/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Friday, April 27, 2012 - 1:25:47 PM - Bill Read The Tip

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


Friday, April 27, 2012 - 4:53:36 PM - Jugal Read The Tip

Thanks Bill


Friday, May 11, 2012 - 11:58:18 AM - ron Read The Tip

"select login property" does not work for me but "select loginproperty" does.


Friday, May 11, 2012 - 1:28:24 PM - Greg Robiodux Read The Tip

Ron - thanks for pointing that out.   The tip has been updated.


Friday, May 11, 2012 - 1:55:20 PM - Jugal Read The Tip

thanks Ron


Saturday, May 12, 2012 - 4:20:42 PM - Bijoy Kaiprath Read The Tip

No flaws. Perfect troubleshooting. Thank you for the wonderful article Jugal


Tuesday, August 28, 2012 - 10:19:52 AM - Gloria Read The Tip

This is one of the best help tips written.  Thank you Jugal.


Tuesday, August 28, 2012 - 1:28:36 PM - Jugal Read The Tip

Thanks Gloria


Saturday, November 24, 2012 - 6:02:38 PM - Rishikesh Tiwari Read The Tip

Really its to good to know about this.Thanks Jugal.


Tuesday, January 08, 2013 - 11:55:48 AM - Ravi Teja G Read The Tip

Nice article Jugal


Tuesday, March 26, 2013 - 12:56:29 AM - Sravani Read The Tip
Hi,
 
Facing issue with the linked server, Please go through below,
 
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()
===================================
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient Data Provider)
------------------------------
Server Name: BDONT3ADSQL1\BDONT3ADSQL11
Error Number: 18456
Severity: 14
State: 1
Line Number: 1

Thursday, September 05, 2013 - 2:09:51 PM - Mike Connolly Read The Tip

Hello Jugal,

 

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?

Thanks

Mike



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.