Sys logins mysteriously dropped
-
We did some maintenance work the other night (enabled live vMotion and storage updates). There were no SQL changes. We have 5 production SQL instances, 3 of which are SQL 2016 and virtual, and 2 of which are SQL 2008 physical. The 3 virtual (2016) needed to be restarted because of the cluster they are on. After the restart, the log files on those 3 instances started filling up with this error (every 5 seconds)-
The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'
We discovered that 2 sys logins were gone on all 5 SQL instances, ##MS_PolicyEventProcessingLogin## and ##MS_PolicyTsqlExecutionLogin##, but the errors only occurred on the 3 virtual 2016 instances that were restarted. We recreated the logins as disabled, and the errors stopped. We don’t know definitively when or why the logins were dropped, but we do have clones of 3 of the servers from a week prior and the sys logins did exist when those clones were created.
Any thoughts on how did the logins get dropped?
- steal - Thursday, May 3, 2018 10:53 AM
You could query the default trace files if they are available back to when the logins disappeared. The default trace does capture the event Audit Addlogin Event which fires when a login is created or dropped. So you could look for that event and filter on the TargetLoginName which would be the login deleted/added for that event. So something along the lines of:
DECLARE @path nvarchar(500);SELECT @path =
REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])),500)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;SELECT
te.name as EventClassName,
tr.*
FROM fn_trace_gettable(@path, default) tr
inner join sys.trace_events te
ON tr.eventclass = te.trace_event_id
WHERE te.name = 'Audit Addlogin Event'
AND TargetLoginName = 'LoginName'Sue
Edit:Typo
-
We have an Audit in place that captures Login created or Login Changed.
I have seen on patch nights, those same logins get dropped and recreated as part of the patches;
i would not expect that to happen, but it does.
i can track down a specific example like below easily,but what was specifically patched(OS vs SQL?) i am not sure yet.I would mention the patches are typically for windows, all our SLq servers have been up to date for a long time.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - Lowell - Thursday, May 3, 2018 3:20 PMWe have an Audit in place that captures Login created or Login Changed.
I have seen on patch nights, those same logins get dropped and recreated as part of the patches;
i would not expect that to happen, but it does.
i can track down a specific example like below easily,but what was specifically patched(OS vs SQL?) i am not sure yet.Mine do from SQL Server patches but they are always recreated right after. The posters logins were not recreated though.
Sue
-
Apologies that I am slow in replying, thanks to those who replied. I did query the default trace files, unfortunately they did not go back far enough to capture the dropped logins. This did prove that the logins were not dropped as part of the maintenance done that night though.
This week we had to restart one of the SQL instances that was not affected upon my original post. It immediately started throwing the same error when it came back to life. This proves that it's unrelated to the SQL version or hosting infrastructure differences (storage, physical vs virtual).
My theory is that an update was applied to all 5 instances at some point (that did not require a reboot) but which did drop the two system logins. For whatever reason, the errors do not manifest until reboot. I have asked our IT team to let me know what updates/patches were applied in the weeks prior, but I suspect this may just remain a mystery (as it has in all other posts I have reviewed, going back quite a few years). If I learn more, I will certainly share.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply