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”

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

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.
    How to Enable Files & Folders Level Auditing in SQL Server? - Interview Question of the Week #176 audi-02
  2. Click on Advanced, go to Auditing Tab. Click on Add.
    How to Enable Files & Folders Level Auditing in SQL Server? - Interview Question of the Week #176 audi-03
  3. Select a Principal and add EVERYONE
  4. For Type, Option Select ALL
  5. Click on Show Advanced Permissions
    How to Enable Files & Folders Level Auditing in SQL Server? - Interview Question of the Week #176 audi-04
  6. Select Delete and delete subfolders and files. Uncheck others and click on OK.
    How to Enable Files & Folders Level Auditing in SQL Server? - Interview Question of the Week #176 audi-05
  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)

, , , , , ,
Previous Post
How to Write Errors in Error Log? – Interview Question of the Week #175
Next Post
How to Find Cardinality or Uniqueness for any Column? – Interview Question of the Week #177

Related Posts

2 Comments. Leave new

  • Julian Valoo
    May 31, 2018 10:35 am

    HI Pinal

    Very good solution as always

    In my opinion , I think preventing this issue from happening is a better option. I normally restrict access to the SQL server folder to only the SQL Server service accounts , in this way there should be no delete taking place. Unless there is a SQL server job that’s deleting the files accidently or some malicious activity is happening, or the process/person that’s deleting the files is using NT Authority. This is also an external audit requirement for us to prevent access to the SQL server folder or drive. This is something that’s audited on a yearly basis by an external audit firm.

    Reply
  • Darren Onekhamphoui
    June 2, 2018 12:26 am

    Nice!

    Reply

Leave a Reply

Menu