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

By: Jugal Shah   |   Read Comments (11)   |   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.

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.

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

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 Step

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

About the author

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

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

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 *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code