Write SQL Server Audit events to the Security log

 

Write SQL Server Audit events to the Security log

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.

Local Policiesà User Rights Assignmentà Generate security auditsà Add User or Group

Local Policiesà Manage Auditing and security logs à Generate security auditsà Add User or Group

gpupdate /force from cmd

– Close the Security Policy tool.
– Restart SQL Server to enable this setting.

 

 Provide full permission for the SQL Server service account to the registry hive. 

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security

 

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.
– Rename it Audttesting (can choose any 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”.

 

Can be set the ServerAuditSpecification by below query also 

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]

FOR SERVER AUDIT [Audttesting]

ADD (BACKUP_RESTORE_GROUP),

ADD (FAILED_LOGIN_GROUP),

ADD (SUCCESSFUL_LOGIN_GROUP),

ADD (LOGOUT_GROUP)

WITH (STATE = ON)

GO

 

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.