Everytime I run the Maintenance Plan in SSMS, it keeps failing. I Goggled the errors in the log:
2018-02-14 09:34:45 - !  SQLServer Error: 17892, Logon failed for login '(login name removed)' due to trigger execution. [SQLSTATE 42000]
2018-02-14 09:34:45 - !  Logon to server '(local)' failed (ConnUpdateJobActivity_NextScheduledRunDate)
2018-02-14 09:36:20 - !  SQLServer Error: 17892, Logon failed for login '(login name removed)' due to trigger execution. [SQLSTATE 42000] and I found others who had the same issue.
"Description: Failed to acquire connection "Local server connection".
Connection may not be configured correctly or you may not have the right
permissions on this connection. End Error Error: 2018-02-14 15:15:02.53
They said to check the Manage Connections and makes sure the server name is
listed with Local server connection. Then they recommended deleting the
maintenance plan and create a new one. I did that and still get the same
problem. The server is using Windows Authentication so I'm not sure where the
single point of failure is here. The job execute as NT Service\SQLSERVERAGENT
so it should have ran successfully.
But again, if I run the syspolicy_purge_history job after creating a plan for
it (same Local Server Connection, same server name), it runs successfully. It
so strange. If it's a permissions thing on the server name, the purge history
job should fail as well? Any thoughts on how to fix this?
It looks like you have a login trigger defined that is rolling back the connection attempt for the SQL Server agent job.
If you look at the syspolicy_purge_history job - that job should be owned by the 'sa' account. The agent jobs for the maintenance plans are created using the owner of the maintenance plan - which would be the account used to create the plans. Most likely - that means the owner of the maintenance plans and agent jobs is your account.
To insure the agent jobs and maintenance plans run under the system account - you need to modify the owner. The agent jobs can be updated in SQL Server Agent but the maintenance plans need to be updated manually in the table msdb.dbo.sysssispackages - changing the ownersid to 0x01 (sa).
The maintenance plan itself doesn't need to be modified - but if you don't make that change then if the agent job is ever deleted and recreated it would be recreated with the owner of the plan and you would have to remember to change the job.
Actually, I noticed on other servers that the Maintenance Plan ownersid is 0x01 followed by a long string of numbers and letters, the syspolicy_purge_history job is set to the 'sa' account, and the Maintenance Plan runs fine without errors. So I don't think changing the ownersid for the Maintenance Plan to 0x01 will work, and may cause other issues?
Changing the owner of the maintenance plan won't cause any issues. The reason you are seeing different owner SID's on different systems is because the individual that created the plan is set as the owner.
The key is to insure that the SQL Server agent jobs are owned by sa (or a sysadmin account). If you modify the maintenance plan owner prior to creating the schedule for the subplan - then the agent job will be created with that owner. If you create the schedule for the subplan prior to changing the owner - then the owner of the agent job will be the owner of the maintenance plan.
The problem you are running into is the fact that the agent job (not the maintenance plan) is running in the context of a login that is blocked. That login is blocked in a login trigger that has been defined on that system - and when that login attempts to access SQL Server to execute - the login trigger rolls back the login attempt and your agent job fails.