This topic came up on a forum post recently, and after responding a couple of times to the thread I had to jump into one of my VMs to figure out what exactly the problem was.
If you use Server Audits in SQL Server, one of the features that you might be interested in is the ability to have the audit data written into the Windows Security Event Log, especially if your company already has data collection software in place to harvest the log records for auditing purposes. However, if you try and actually implement a Server Audit in SQL Server 2008 or SQL Server 2008 R2 that writes to the Security Event Log, unless the service is running under the Local System account, the audit will fail. To demonstrate this, we’ll create a server audit with a server level audit specification to track object accesses in the server and then try to start the audit. The SQL Server services in my test VM are running under a local low privilege user named SQLServiceAcct, and all of the steps described in the Books Online topic Write SQL Server Audit Events to the Security Log have been followed to enable the instance for writing to the Windows Security Log.
CREATE SERVER AUDIT [Audit_ToSecurityLog]
TO SECURITY_LOG
WITH(QUEUE_DELAY = 0, ON_FAILURE = CONTINUE)
GOCREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec_ObjectAccess]
FOR SERVER AUDIT [Audit_ToSecurityLog]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GOALTER SERVER AUDIT [Audit_ToSecurityLog]
WITH (STATE = ON);
Once we attempt to enable the server audit the following error will be logged into the SQL Server ERRORLOG file:
2012-06-22 20:48:09.92 spid53 Error: 33204, Severity: 17, State: 1.
2012-06-22 20:48:09.92 spid53 SQL Server Audit could not write to the security log.
Now, since I knew for a fact that I had followed all of the steps in the Books Online, I decided to fire up Process Monitor and setup a filter on the Process Name so that if it wasn’t sqlservr.exe the events were excluded from the display.
Then I disabled the server audit and enabled it again so that I could see what events occurred in Process Monitor leading to the failure writing to the Security Event Log.
Here we can see that the problem is that SQL Server needs to Read/Write access to the HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security registry key but does not have permission. So to fix this I opened up the registry editor to this key and then added the SQL Server service account for my VM to the key with Full Control as show below.
Once this was completed, I went back into SQL Server Management Studio and disabled and re-enabled the audit again. This time no error was logged to the ERRORLOG file, so I went back to Process Monitor to see what changed. This time we can see that SQL Server actually added a key under the HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security key named MSSQLSERVER$AUDIT, which makes it possible for the instance to write into the Security Event Log.
If we refresh the key in the registry editor we can see the new key and the values set underneath it.
I tested this in SQL Server 2012 RTM and the behavior is exactly the same. Once the MSSQLSERVER$AUDIT key has been created, you can remove the permissions from the HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security key and the auditing will continue to work (I had to test it out of curiosity).
Testing the audit after fixing the registry permissions yields the expected events in the Security Log:
Log Name: Security
Source: MSSQLSERVER$AUDIT
Date: 6/22/2012 8:43:47 PM
Event ID: 33205
Task Category: (3)
Level: Information
Keywords: Classic,Audit Success
User: SQL2K8R2-IE2\SQLServiceAcct
Computer: SQL2K8R2-IE2
Description:
Audit event: event_time:2012-06-23 00:43:47.4280312
sequence_number:1
action_id:SL
succeeded:true
permission_bitmask:1
is_column_permission:true
session_id:52
server_principal_id:261
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:898102240
class_type:U
session_server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_sid:010500000000000515000000f84a18afb97103f8a5b4f260e8030000
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:SQL2K8R2-IE2
database_name:AdventureWorks2008R2
schema_name:Sales
object_name:SalesOrderDetail
statement:SELECT * FROM Sales.SalesOrderDetail
additional_information:
.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQLSERVER$AUDIT" />
<EventID Qualifiers="16384">33205</EventID>
<Level>0</Level>
<Task>3</Task>
<Keywords>0x80a0000000000000</Keywords>
<TimeCreated SystemTime="2012-06-23T00:43:47.428031200Z" />
<EventRecordID>47907</EventRecordID>
<Channel>Security</Channel>
<Computer>SQL2K8R2-IE2</Computer>
<Security UserID="S-1-5-21-2937604856-4160975289-1626518693-1001" />
</System>
<EventData>
<Data>event_time:2012-06-23 00:43:47.4280312
sequence_number:1
action_id:SL
succeeded:true
permission_bitmask:1
is_column_permission:true
session_id:52
server_principal_id:261
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:898102240
class_type:U
session_server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_name:SQL2K8R2-IE2\Jonathan Kehayias
server_principal_sid:010500000000000515000000f84a18afb97103f8a5b4f260e8030000
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:SQL2K8R2-IE2
database_name:AdventureWorks2008R2
schema_name:Sales
object_name:SalesOrderDetail
statement:SELECT * FROM Sales.SalesOrderDetail
additional_information:
</Data>
</EventData>
</Event>
