June 28, 2021 at 1:38 pm
Installed two named instances of sql 2019. In one of the instances, I am seeing login failed for user 'mydomain\sqlagentserviceaccount' reason: could not find a login matching the name provided.
The time the message gets lodged is the time when syspolicy_purge_history runs. I ran that job manually earlier and that time it didn't seem to generate failed login. But every night there's one. Since this is a new install I wanted to make sure it is error free before moving it to production.
Any insight will be appreciated.
June 29, 2021 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 29, 2021 at 8:50 pm
This might help. The step might be running under the owner, not the Agent account
Double check the ownership of the job. If you can post more info, that's helpful.
June 29, 2021 at 9:20 pm
Review this issue: https://feedback.azure.com/forums/908035-sql-server/suggestions/32897173-syspolicy-purge-history-agent-job-tries-to-access
It is a known issue and not much you can do about it, unless you want to grant permissions to each service account on each instance on that server (which I would not recommend). Or - you could try modifying the agent job on every instance using the code outlined here: https://docs.microsoft.com/en-us/answers/questions/301096/login-failed-for-user-39servernamesqlserverinstanc.html
Or - just recognize that it is a known issue and ignore the error.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2021 at 1:16 pm
The owner is sa for the sys policy purge job. The two instances in SQL server uses the same domain account for sql agent. The instance that was create first shows this error. No error in the one that was created after. This is coming from sys policy purge at 2 am.
June 30, 2021 at 1:25 pm
@Jeffery
I did come across those articles. However, I have other environments with multi instance where it is the same set up (sql agent uses a domain account and is same for both instance...no powershell modification on those sys policy purge jobs) and don't see this issue. Seeing this on a fresh install of sql 2019 with CU 11. Curious as to why not many people reporting it. The first list that you posted was reported in 2017 with only 6 upvotes and it was set to 'in review' in 2018. If the number of cases are low then should there not be something that can be done about it in that why are some getting it vs not others?
June 30, 2021 at 1:42 pm
Probably because most people don't run multi-instance servers. Other than that - I have no idea why some would have an issue and others would not. All I can do is point out the issue and the possible solutions, if you don't want to implement those because you believe there is 'another' solution available - that is up to you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 9, 2021 at 1:25 pm
Just wanted to update.
Scenario was: SQL server agent service account was domain based and same for both instances. The SQL service account was domain based and same for both instances. SQL server agent account different from SQL service account. The instance that was installed first was Instance1 and the latter was instance2. And, every 2 am when syspolicy_purge_history ran it would throw a login failure for sql server agent account on instance1.
What got rid of the error was to add the domain based sql server agent account to the Logins of the Instance1 (not required to add to both the instances but on the one where error was being generated). No admin rights whatsoever. Just a regular login account with the default public role. Did not see the login error after that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply