Login failed for user 'serverName\sqlServerInstance$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Question

I found that I kept getting this error from the SQLPS (SQLAgent$instanceName@serverName) application name. How do I modify this SQLPS application so that I no longer receive this login failed error?

Answer 1

Hi

>Yes, it failed at 2AM. Since it seems to be a syspolicy job, do I just ignore this error? I'm running SQL Server 2019

The syspolicy_purge_history is a built-in SQL Server Agent Job which is configured to run once a day at 2 AM by default. You can disable it without using Policy-Based Management.

Or check the solution in the following post.(Edit the step 3 in syspolicy_purge_history job)
https://dba.stackexchange.com/questions/159482/sql-server-login-failure-every-day-at-same-time-with-the-service-account
http://dbatasks.blogspot.com/2018/08/login-failed.html

Answer 2

Okay, I copy the code below from https://dba.stackexchange.com/questions/159482/sql-server-login-failure-every-day-at-same-time-with-the-service-account but it's not working. I still receive that same error.

$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()

Answer 3

I put that code in both instances and I still receive the same error on the default instance.

This code show me the Login failed error.

EXEC sp_readerrorlog 0, 1, 'Login failed';   

But again, I look a the history, the job has no failure.

Answer 4

If it is at 02:00 solution here:
https://www.codykonior.com/2015/05/31/login-errors-with-syspolicy-purge-history
Still not solved in SQL 2022 where I found this also