How to Enable Files & Folders Level Auditing in SQL Server? – Interview Question of the Week #176

Question: How to Enable Files & Folders Level Auditing in SQL Server?

Answer: Recently I was working with a client on a SQL startup issue. After spending a lot of time, we were able to find that many files were missing in Binn folder. Now, they were interested to know who is doing it as it happened second time on this server. In this blog, we would learn the way to identify who deleted the files and folders related. Apparently, the SQL Server Binn had a lot of files missing so we would follow the steps needed to implement this.

As far as I know, there is no solution in-built within SQL Server. we need to have auditing enabled and it must be enabled at the windows level. Here are the steps to achieve this.

Step 1: Enable Audit Policy:

1. Go to Administrative Tools->Local Security Policy->Local Policies->Audit Policy
2. Double click “Audit Object Access” in the right pane and switch-on “Success” & “Failure”

Note: make sure this policy is not overridden by your domain policy. Else the changes will revert to old values.

Step 2: Enable auditing on a Folder required:

We need to enable and add user/security group for auditing on the folder which needs to be captured for file deletion.

1. Right-click on the target folder (in our case C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn), select Properties and go to Security Tab.
   
2. Click on Advanced, go to Auditing Tab. Click on Add.
   
3. Select a Principal and add EVERYONE
4. For Type, Option Select ALL
5. Click on Show Advanced Permissions
   
6. Select Delete and delete subfolders and files. Uncheck others and click on OK.
   
7. Click OK and again Click on OK

How to check if the file is deleted?

Based on above setting, the entries would be written to Security Event Log. We need to open “Event Viewer” and choose Security. The right-hand side will display all the security audit events. We can search for Event ID 4663. You can also filter by the event ID 4663.

Log Name: Security
Source: Microsoft-Windows-Security-Auditing
Event ID: 4663
Task Category: File System
Level: Information
Keywords: Audit Success
User: N/A
Computer: SERVER
Description:
An attempt was made to access an object.
Subject:
Security ID: domain\user
Account Name: user
Account Domain: domain
Logon ID: 0x156B141
Object:
Object Server: Security
Object Type: File
Object Name: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\BackupToUrl.exe
Resource Attributes: S:AI
Process Information:
Process ID: 0x2240
Process Name: C:\Windows\explorer.exe
Access Request Information:
Accesses: DELETE
Access Mask: 0x10000

Please make sure you are increasing the event log size as this will be very noisy and events can get easily get rolled over. To do that:

• Go to properties to “Security” Event Log.
• Change the maximum log size to a bigger size.

Hope this would help someone in finding the “bad” process/person. Do you know some other way to do auditing?

Reference: Pinal Dave (https://blog.sqlauthority.com)