login failure error for syspolicy_purge_history job on multiple instances server

Question

Post reply

login failure error for syspolicy_purge_history job on multiple instances server

sqlfriend

SSC Guru

Points: 52769
More actions
December 1, 2018 at 12:04 pm

#376575

I have built two instances on SQL 2017. One is default instance, The other is a named instance for example myinstance1. And we also have different service account for the two instances.
There is a system job syspolicy_purge_history scheduled on both instances by default.
The job generate a login failing error:
Login failed for user'mydomain\myinstance1sqlservice'. Reason: Could not find a login matching the name provided.
The system job has the 3 rd step with a powershell code: they are identical on both instances.
if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()
It seems the job on second instance try to reach the computer name instance (default) , but the account is not on the default instance.
It should just run on its own instance, why it try to reach to the default instance?
How to fix this?
Thanks!
December 4, 2018 at 1:05 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 1

Here is a couple links that helped me fix mine.

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

https://serverfault.com/questions/211014/syspolicy-purge-history-generates-failed-logins

I had to correct the formatting in the script in the first example to this below. It worked on all of mine except one server where I am getting a powershell script error related to the policy store. I set it back to the original command.

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Thanks, does this code mean: not to use the named instance, but just use the computer name which is the default instance.
Since it only server name in it like this: a Source=$(ESCAPE_NONE(SRVR));

I replaced the code with this on my second instance which is a named instance, and no the login failure any more.
But just want to understand what instance the new code refers to.

Thanks

Harold Buckner SSCarpal Tunnel Points: 4173 More actions · Answer 3

SVR is a token that suppose to provide the Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name.

Some info: https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-2017