Auditing Critical Windows Files and Folders for SQL Server

By:   |   Updated: 2014-09-08   |   Comments (2)   |   Related: More > Security

Problem

In our extract, transform, and load (ETL) processing, we have some sensitive files and folders. Files containing financial information, as well as configuration files and the like, are stored on disk. We need to know if someone touches them. Is there a way to do this?

Solution

Yes, there is. File auditing has been built into the Windows operating system since the beginning. Let's step through how to set it up.

Setting Up Auditing on the Folder

The first thing you'll need to do is navigate to the folder and choose Properties in Windows File Explorer. You'll want to click on the Security tab and then click on the Advanced button:

You'll want to click on the Security tab and then click on the Advanced button

This brings up a new dialog window. Click on the Auditing tab and, if necessary, click on the button with the shield and Continue, which tells you that the OS needs to access this with your escalated privileges:

Click on the Auditing tab and, if necessary, click on the button with the shield and Continue

You'll get a dialog box which allows you to add, remove, and modify who is being audited, and for what actions. Click on the Add button. You'll then get the standard dialog window which allows you to choose who you're going to add. In my case, I chose Authenticated Users because I want to audit everyone. I didn't include screenshots of those two dialog windows because they're standard. However, once you choose who you want to audit, you'll be presented with a new dialog window that may be new to you:

You'll get a dialog box which allows you to add, remove, and modify who is being audited, and for what actions.

In this case I've checked every option, both success and failure, except for someone using Full Control or someone merely traversing (clicking through) the folder. Any reads, writes, deletes, or creates will be audited. Actually, it's more correct to say every attempt to do these things. That's why the Failure boxes are also checked. If you only care about when someone actually succeeds, you don't have to audit for the Failures.

Keep in mind that auditing at this level will generate a lot of events, as we'll see shortly. Therefore, if you can eliminate things to audit for, such as the ones corresponding to the attributes, you should.

Now that you've got the auditing set up at the file/folder level, you might think you're all done. However, you're not. There's one other place that has to be checked, otherwise no auditing will be done.

Verifying Settings in the Local Security Policy

Typically these settings are configured by a group policy object (GPO) pushed down by your domain administrators. If this is the case and the settings aren't correct for what you need, you'll have to speak to whoever is in charge of creating and modifying the GPO that applies. If it's not being set by a GPO, you can edit the settings in your Local Security Policy. In either case, you can see what the settings are by bringing up your Local Security Policy.

To access your Local Security Policy, choose it under Administrative Tools, whether you get to this through your Start Menu or the Control Panel. You'll want to expand the Local Policies folder and click on the Audit Policy folder. The policy you're interested in is the one titled Audit object access in the right hand pane:

Verifying Settings in the Local Security Policy

If you're only interested in auditing Success events, you don't have to have Failure checked. However, the typical setting for this policy is for both to be checked. The reason for this setting is to have both options checked is this policy determines *if* auditing takes place. If the policy isn't set to audit, it doesn't matter what you may set up on a file or folder. No auditing will take place. This policy is the pre-requisite to auditing file and folder access.

Viewing Access

Any audit events will be put into the Security event log for the operating system:

Any audit events will be put into the Security event log for the operating system

There's a lot of events, so you're going to want to filter the log:

There's a lot of events, so you're going to want to filter the log

Choose to only look at event ID 4663. Any file/folder object access will generate this event. This will give us more than enough information:

Any file/folder object access will generate this event.

Click OK and see the number of events. For instance, simply going to the folder, creating a text file, and renaming it generates 84 events.

Simply going to the folder, creating a text file, and renaming it generates 84 events.

You'll want to look at the Details tab because you'll want to see the ObjectName and the AccessMask. You're looking for the matching object name corresponding to the files and folders you care about.

You'll want to look at the Details tab because you'll want to see the ObjectName and the AccessMask

The Access Mask is a little more difficult. The best resource I've found is put out by Wireshark. Keep in mind that when looking at the Details tab, leading zeroes will not be shown. So for this example, 0x10000 is listed as 0x00010000 in the Wireshark SMB (Server Message Block) reference. This corresponds to me deleting the Test.txt file.

But What about All the Events?

There's going to be a lot of events. You'll probably want to look to build a script to pull events periodically if your organization doesn't already have a product that collects and culls event logs. This sort of auditing is heavy. That's why it isn't turned on by default.

Next Steps


Last Updated: 2014-09-08


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, September 15, 2014 - 1:12:07 PM - Renato Back To Top

Very nice article. Thanks for sharing.


Monday, September 08, 2014 - 7:39:27 AM - Kevin Buchan Back To Top

Is there any perfmance hit on the server when you enable auditing?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools