syspolicy_purge_history generates unauthorised connections to other instances on a machine.

Question

Post reply

syspolicy_purge_history generates unauthorised connections to other instances on a machine.

gabriel.defigueiredo

Old Hand

Points: 369
More actions
September 12, 2010 at 9:06 pm

#227648

My scenario is this:
I have 3 SQL Server instances, say A, B C on a virtual server. syspolicy_purge_history has been installed on all threee instances by default. Each instance has its own dedicated service account (sqlsvc-A, sqlsvc-B and sqlsvc-C respectively).
step 3 in syspolicy_purge_history in each of these instances reads:
(Get-Item SQLSERVER:\SQLPolicy\Machinename\A).EraseSystemHealthPhantomRecords()
(Get-Item SQLSERVER:\SQLPolicy\Machinename\B).EraseSystemHealthPhantomRecords()
(Get-Item SQLSERVER:\SQLPolicy\Machinename\C).EraseSystemHealthPhantomRecords()
so they appear to have been set correctly.
syspolicy_purge_history in instance A works OK.
syspolicy_purge_history in in instance B seems to want to first connect to instance A, resulting in:
Error: 18456, Severity: 14, State: 11. Login failed for user 'Machinename\sqlsvc-A'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>] .
No errors are reported by Powershell.
syspolicy_purge_history in in instance C seems to want to first connect to instance A resulting in
Error: 18456, Severity: 14, State: 11. Login failed for user 'Machinename\sqlsvc-A'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>] .
Then it tries to connect to instance B, resulting in
Error: 18456, Severity: 14, State: 11. Login failed for user 'Machinename\sqlsvc-B'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>] .
No errors are reported by Powershell.
My knowledge of Powershell is minimal at the moment. What is the solution to stop these unauthorised connections?

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

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

Maxim Picard SSCrazy Points: 2052 More actions · Answer 1

This should be in SQL Server 2008 forum.

This was resolved on the MSDN forum:

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/5968e421-c387-4e91-921c-7e9b1f3d1c90

http://support.microsoft.com/kb/955726

mdavide SSC Veteran Points: 254 More actions · Answer 2

This issue is still open. I have tried both fixes mentioned in the link and neither corrected my issue.

gabriel.defigueiredo Old Hand Points: 369 More actions · Answer 3

to recap:

"The problem has gone away after recreating the jobs as indicated in Method 2 of http://support.microsoft.com/kb/955726

"

I haven't had any issues since then, 'til date.

Try deleteing and recreating the jobs, as I did.

Cheers,

G

SQL51F1 SSCommitted Points: 1959 More actions · Answer 4

OK, so it is about 4 years after this post but I have the same issue and none of the solutions worked - after numerous attempts.

I was down to pulling things apart in PS to see what it was doing.

In the end, the only thing that worked was putting the service account for the agent into the other instance. I don't mean their qualified account - I mean 'NT SERVICE\SQLAgent${instance name}'

A lack of planning on your part does not constitute an emergency on mine.

bo.lausten Valued Member Points: 67 More actions · Answer 5

Try this solution, worked perfectly for me: http://serverfault.com/questions/211014/syspolicy-purge-history-generates-failed-logins