Auditing Critical Windows Files and Folders for SQL Server

By: K. Brian Kelley   |   Updated: 2014-09-08   |   Comments (2)   |   Related: > 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:

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:

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:

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:

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:

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:

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.

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.

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

• Learn how to audit the local administrators on your server.
• Put in place auditing for your sysadmin members within SQL Server.
• Control access to SSIS packages within SQL Server using roles.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips