question

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 asked DSPatrick action

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

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?

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what is this Powershell script doing / trying to do?

What do you want it to do?

0 Votes 0 ·

There is no Powershell script running or scheduled to run. If I disabled a SQL Server job agent then this login failed log goes away.

0 Votes 0 ·
BillStewart avatar image
1 Vote"
BillStewart answered

The error message is telling you exactly what's wrong.

You are trying to connect to a SQL Server instance, but the account you are trying to use is not defined as a login for that instance.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered

The SQL Server agent "Log on as" is setup to use "NT Service\SQLAgent$sqlServerInstance". It's running fine under this account though. However, I kept getting this error in the default SQL Server instance.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
1 Vote"
JeffreyWilliams-3310 answered

Is this by chance related to the syspolicy agent jobs? If so - it is a known issue: https://feedback.azure.com/forums/908035-sql-server/suggestions/32897173-syspolicy-purge-history-agent-job-tries-to-access

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

If I disabled a SQL Server job agent then this login failed log goes away.

If you disabled SQL Server Agent, that's a good lead. Now you need to look through the jobs that you have. Not the least the ones that fails...

Jeffery is on to something. Do you get these login failures at two o'clock in the morning? In that case it is quite surely the syspolicy job.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well, you could find the job on the offending instance and disable the job. Since it is not logging on to the right server, it's not of much use anyway. (And if you don't use Policy-Based Management, you never have any use of it.)

1 Vote 1 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog commented

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


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And you modified this on the named instance(s), and you get the error on the default instance?

0 Votes 0 ·

Yes, I put it in step 2 of the instance that has this error.

0 Votes 0 ·

But that's not the instance the error is coming from. It's another instance that is confused and knocks on the wrong door. Replace the offending step on all instances on the machine.

I normally don't have Agent running on my instances at home, but I started Agent on the DIXNEUF (where it is step 3, SQL 2019). And indeed, I found a logon error at two o'clock on the instance named CATORCE! (Please don't ask me how they do it.)

0 Votes 0 ·
CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog commented

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.

81740-image.png



image.png (34.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Again - to repeat myself - this is a known issue. There are 2 ways to remove the error:

1) disable the agent job on all instances
2) Add the service account for every instance on all instances - with appropriate permissions

The problem is that the script identifies all instances on that system and attempts to connect to every instance. Since each instance is running under its own service account - it doesn't have permissions on the other instances and generates a login failure.

Or - you can just ignore the error since it isn't causing any issues.

0 Votes 0 ·

For what it's worth, I took the code above and put it in step 3 of the job (it may be step 2 on earlier versions, but on SQL 2019, it is step 3), and then started that step manually. I then checked the errorlog for all my instances, and none had a login error for the time I ran the job.

But as Jeffery notes, you can ignore the error - there is no real harm.

Yes, I also noticed that the was listed as succeeded in the job history.

0 Votes 0 ·