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 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.
Biography
Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
