There are a few options you have if you want to enable audit logging on Microsoft SQL Server. You can write the logs to a .sqlaudit file, but you cannot open these in a simple text editor like Notepad. You would have to use the SQL Log Viewer. In my case, I needed a way to forward the logs to a third party, and their software didn’t have a way to read the .sqlaudit logs either. The workaround I found was being able to have Microsoft SQL Server write the audit logs to the Windows Security Log. This was an ideal setup for me because I just wanted to be able to track login attempts at the database level. To get it setup, there are a few steps you need to do.
To configure the audit object access setting in Windows using auditpol
– Open a command prompt with administrative permissions.
– On the Start menu, point to All Programs, point to Accessories, right-click Command Prompt, and then click Run as administrator.
– If the User Account Control dialog box opens, click Continue.
– Execute the following statement to enable auditing from SQL Server.
auditpol /set /subcategory:"application generated" /success:enable /failure:enable
– Close the command prompt window.
To grant the generate security audits permission to an account using secpol
You must set the permissions to the service account that is running the SQL Server so that it can “generate security audits”.
– For any Windows operating system, on the Start menu, click Run.
– Type secpol.msc and then click OK. If the User Access Control dialog box appears, click Continue.
– In the Local Security Policy tool, expand Security Settings, expand Local Policies, and then click User Rights Assignment.
– In the results pane, double-click Generate security audits.
– On the Local Security Setting tab, click Add User or Group.
– In the Select Users, Computers, or Groups dialog box, either type the name of the user account, such as domain1user1 and then click OK, or click Advanced and search for the account.
– Click OK.
– Close the Security Policy tool.
– Restart SQL Server to enable this setting.
On SQL Server – Create The Audit Object
– The first step is to create a new audit object. To create a new audit object using SSMS, go to the SQL Server instance you want to audit, open up “Security,” and you will see the “Audits” folder.
– Right-click on the “Audits” folder and select “New Audit,” and the “Create Audit” dialog box appears.
– The first thing you need to do is to decide if you want to use the name that is automatically generated for you as the audit object name, or to assign your own name. Since numbers don’t mean much to me, I assigned it my own name.
– Next, you have to provide a “Queue Delay” number. This refers to the amount of time after an audit event has occurred before it is forced to be processed and written to the log. The default value is 1000 milliseconds, or 1 second. While I am going to accept the default for this demo, you might want to consider increasing this value if you have a very busy server.
– Next, beside “Audit,” in the dialog box, there is a drop-down box with “File” selected by default. This is where you’ll want to select Security Log (or Application Log depending on which one you want to use).
– Click OK.
– Right-click on the audit object you created and select “Enable Audit”.
On SQL Server – Create The Server Audit Specification
Now that we have created the audit, we need to create the matching audit specification. If we wanted to do an instance-wide audit, we would create a server audit specification.
– Under Security, right-click on the Server Audit Specifications and select “New Server Audit Specification”.
– Give the specification a name, and select the Audit object you want it to be tied to.
– Then select specific Audit Action Types you want SQL Server to log.
– Click OK.
– Right-click on the server audit specification and select “Enable Server Audit Specification”.
Conclusion
After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.
