1

I have dozens of SQL Servers, on many different versions. Many of them have an error in the SQL Server error log EVERY DAY at just about 2 am.

Login failed for user 'TheDomain\SQLServerAgentServiceAccount'

Some show this reason:

Reason: Token-based server access validation failed with an infrastructure error.  
Check for previous errors. [CLIENT: <local machine>]

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

The rest show this:

Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 5.

Our network team doesn't see anything suspicious in this time frame. The second reason makes sense and is true - there ISN'T a matching login for the service account. I don't know why there would be an explicit one created for a service account.

What could the servers be trying to do at the same time every day? There are no Agent jobs, backups, maintenance plans, or any other known services that would do anything.

  • Do you have the state number for these errors? – Laughing Vergil Dec 29 '16 at 17:21
  • I don't know for sure, but it "feels" like the security authorizations are failing at this time - an expiration of, or an explicit removal of, permissions for the service account, requiring it to explicitly log in again. Broadly speaking, this error happens when UAC prevents the system from sending all of the needed security groups, or when the security token is invalid. I'm betting on the latter being the issue. – Laughing Vergil Dec 29 '16 at 17:25
  • Do you see any error message in your sql server error log? if so, please post your error number. – jyao Dec 29 '16 at 18:17
  • I just added details. – SomeGuy Dec 29 '16 at 18:18
  • 1
    Have you thought of setting a profiler trace to run for 5-10 minutes around 2 AM (Audit Failed Logins)? – SQL_Underworld Dec 29 '16 at 21:44
4

Using an Extended Events session I was able to track down the issue and see that it came from the syspolicy_purge_history job that SQL Server creates by default to clean up Policy Management records. Step 3 is "Erase Phantom System Health Records". This contains a PowerShell script that tries to connect to OTHER instances on the machine, causing the login failures. This is a known bug and only affects machines with more than 1 SQL Server instance. https://connect.microsoft.com/SQLServer/feedback/details/606801/syspolicy-purge-history-job. So when you see login failures on an instance, they are not that instance trying to connect to itself, but another instance on the same machine.

The link here explains further and the code below will update the job step to connect to only the instance the job is running on.

https://www.codykonior.com/2015/05/31/login-errors-with-syspolicy-purge-history/

Exec msdb.dbo.sp_update_jobstep @job_name = 'syspolicy_purge_history', @step_name = 'Erase Phantom System Health Records.', @step_id = 3, @command=N'$applicationName = "SQLPS ($env:USERNAME@$env:COMPUTERNAME)" $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnection.ConnectionString = "Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=master;Integrated Security=SSPI;Application Name=$applicationName" $PolicyStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SQLServerConnection) $PolicyStore = New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($PolicyStoreConnection) $PolicyStore.EraseSystemHealthPhantomRecords() '

OR, copy and paste the below into the job step:

$applicationName = "SQLPS ($env:USERNAME@$env:COMPUTERNAME)"
$SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLServerConnection.ConnectionString = "Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=master;Integrated Security=SSPI;Application Name=$applicationName"
$PolicyStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SQLServerConnection)
$PolicyStore = New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($PolicyStoreConnection)
$PolicyStore.EraseSystemHealthPhantomRecords()
0

You did not mention which version of SQL Server you are using. Just in case it matters. However, Aaron Bertrand has a list which you should keep a link to:

The login does not exist in SQL Server, but the login attempt came from the local machine. ... The takeaway here: always specify the database name explicitly in the options tab of the connection dialog; do not use the browse feature.

That is just a snippet from the longer discussion on State 5. So read through the details.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.