Audit SQL Server stop, start, restart
This article outlines an approach for determining the domain identity of a user who has initiated a stop, start, or restart request on SQL Server services. Although SQL Server contains server and database auditing functionality as part of the product, this cannot be used to determine the identity of a user changing the service state of a SQL Server instance since that operation is occurring at the system rather than database level.
I recently worked with a customer to help resolve an issue with Team Foundation Server where collection databases were not coming online following a service restart. While working on this issue, the following question was posed: "how can we identity the user(s) responsible for stopping, starting, and restarting SQL Server services?" .
Preliminary investigation into using SQL Server audit functionality yielded no solution. Checking with Microsoft database specialists, they confirmed that the closest auditing SQL Server could provide for service restarts would tie that operation to the privileged 'sa' identity. Checking with one of Microsoft's Premier Field Engineers specializing in Security, Liju Varghese provided a quick overview on how to implement service level auditing.
The following sections provide details on the settings required at the operating system level to audit service management operations, in this case for the SQL Server service. This is done using group policy objects.
Prerequisites
Ensure the following prerequisites are met prior to proceeding:
- The Group Policy Editor must be available on the SQL Server machine. If you don’t already have it, you can install using Add Roles & Features wizard:
- Ensure you have sufficient permissions to perform the actions outlined in this document. This may include administrative rights on the local server hosting the SQL Server instance, and possibly domain administrative rights if you are configuring Group Policy Objects at the domain level.
The next section, Configuration Details, provides detailed steps on configuring your environment to track SQL Server service starts, stops, and restarts. If you are more interested in seeing the results of this configuration first, you can skip to the following section titled Testing the Configuration to see how service starts, stops, and restarts are reported.
Configuration Details
The first step in the process is to enable auditing SACL on the SQL Server service. This must be done on the server that is hosting the SQL Server service. To begin these steps, click on the Windows Start button, then search for and start the Group Policy Management application:
Navigate to the “Group Policy Objects” folder in your domain, right-click on that folder, and select the “New” menu item to create a new group policy object:
Provide a meaningful name for your new GPO and click “OK”:
Right-click on the domain, or suitable OU container to restrict the policy application to only your SQL Server machines, and select the “Link an Existing GPO…” menu option:
Select the newly created “SQL Server Policy” group policy object and click “OK” to link this object:
Confirm that the new “SQL Server Policy” object shows as a linked object to your domain or OU container:
Right-click on the newly created GPO folder and select the “Edit…” menu item:
The “Group Policy Management Editor” will appear next. Expand and locate the SQL Server service name along the following path: Computer Configuration → Policies → Windows Settings → Security Settings:
Right-click on your SQL Server service and select the “Properties” menu item, then complete the following dialog window to define a policy for this service:
Next, click the “Edit Security…” button to display the “Security for SQL Server” dialog window. From here, click on the “Advanced” button to display the “Advanced Security Settings” window. From this window, select the “Auditing” tab and then click the “Add” button to create a new audit setting:
In the next window, add “Authenticated Users” as a principal, set the type to “Success”, and check the following permissions: “Start, stop and pause”, “Write”, “Delete”:
In the previous window, you should now see the Success Audit entry like the following:
Enable “Object Access” auditing policies using the Group Policy Management Editor. Navigate to these settings along the following path: Computer Configuration → Policies → Windows Settings → Security Settings → Advanced Audit Policy Configuration → Audit Policies → Object Access, as shown in the following image:
Once the steps outlined above have been completed, you are ready to test the configuration by stopping, starting, or restarting the SQL Server instance.
Testing the Configuration
Once the GPO configuration has been implemented, you can check the results of stopping, starting, or restarting the SQL Server instance using the following steps.
First, stop, start, or restart the SQL Server instance. There are multiple ways to do this, including: SQL Server Management Studio, the Services (services.msc) application, or from the command line (sc.exe). No matter which method is used, the identity of the user invoking the start, stop, restart operation will be available.
Next, start the Event Viewer application, and navigate to: Event Viewer (Local) → Windows Logs → Security and filter on Event ID 4656. You will see a list like the following:
Select each Event ID 4656 and look in the General tab for “Accesses” text labeled either “Stop the service” or “Start the service” for the MSSQLSERVER service, like the following:
The domain and username are displayed in the “Subject” area as shown in the image above.
These events will appear for any direct stop, start, restart operation on the SQL Server. There is one other scenario we need to consider, where a SQL Server instance is restarted due to a complete server restart operation. In that scenario, the events described above will not be present.
To determine the user identity initiating a server restart, you can look in the Event Viewer (Local) → Windows Logs → Security section of the Event Viewer and filter by Event ID 1074. This provides information on the user initiating the server restart as shown in the following image:
Conclusion
Hopefully, this information will prove useful in your development and/or infrastructure management activities.
Until next time...