Sql-server – How todentify Who or What is Restarting SQL services via Service Control

sql serverwindows

I investigated an instance that experienced an unexpected restart and came across the usual service control event but no user login associated with it. Is it still possible that an authorized user initiated the restart or is this due to Windows issuing the command? If an authorized user, outside of the SQL error logs and event logs, where could I go about attempting to identify the user?


Log Name: System
Source: Service Control Manager
Date: 12/24/2014 9:02:24 AM
Event ID: 7036
Task Category: None
Level: Information
Keywords: Classic
User: N/A
Computer: XXX
Description:
The SQL Server Agent (MSSQLSERVER) service entered the stopped state.

Best Answer

If you have the default trace running, and you should have it running, it may be able to help you see what happened. The default trace is lightweight and among other things does track Server Stop and Server Start. See Feodor Georgiev's article:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

The trace logs recycle fairly quickly on a busy server, so you would need to check as soon as possible after a Server Stop and Server Start. Toward the bottom of the heading Security Audit Events there is code to ‘Audit Server Starts and Stops’. (Tweaked only slightly to include the default count of trace logs.) 

SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), 4) T  -- 4 is the default number of trace log files
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Server Starts and Stops' )
Related Question