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.
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”.
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.