SQL audit (Server audit specification > SERVER_STATE_CHANGE_GROUP) log sa user instead DOMAIN\USERNAME

Question

Hi all,

I am testing audit for SQL Server currently running on a IAAS VM (Win server 2022, SQL Server 2022 CU14). I have created an audit which log file in a file. I have created a Server audit specification which include the action SERVER_STATE_CHANGE_GROUP. Audit setup complete successfully, The idea is to catch user that start and stop SQL Server service.

On the VM is installed a Domain Controller. I used some Active Directory users to play with SQL service.

Checking audit log, I can see starstarted and shutdown events but insted to find AD user (Domain\username) that trigger the instance to stop/start I find "sa" user.
For other actions I am able to see AD user who run the actions. See attached screenshot for details.

Do you have any idea?

Answer 1

Starting SQL Server is a Windows event, so it is quite clear that SQL Server cannot know who performed that action.

Stopping SQL Server is also normally a Windows event, although there is the SHUTDOWN command.

So if you want to know who started and stopped SQL Server, you will need to audit this in Windows, presumably by trawling the event log.

Answer 2

Hi，Alessandro Colombi

You can view the SQL Server service startup details through the Windows Event Log by filtering for events with ID 7036 to find the corresponding instance name you can write a script in PowerShell to automatically filter the events.With Get-WinEvent.

Additionally, you can use PowerShell to periodically monitor the instance's running status and output the service status to a file.

Best Regards,

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.