SQL Server Audit Specification – Filter DML by All But One User

auditsql serversql-server-2012

I'm setting up a database audit. I'd like to capture any DML change that is issued by database user other than the service account related to the application.

In short, this is to prove to our external auditors that no users are tampering with data outside of the application.

This following script sets up a DML audit that is global to all principles.

CREATE DATABASE AUDIT SPECIFICATION [OurDatabaseAuditSpec]
FOR SERVER AUDIT [OurAudit]
ADD (DELETE ON OBJECT::[dbo].[SensitiveData] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[SensitiveData] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[SensitiveData] BY [public])
WITH (STATE = ON)
GO

How do I go about modifying this audit specification to capture everything EXCEPT what is being done by the application service account?

Am I limited to specifying each principle individually or can I specify an exception? Reading through BOL it seems I can only list principles to audit – not principles to ignore.

Best Answer

You can't do this at the audit specification level, but you can do it at the server audit level. So, if this audit does other things too, and you don't want to filter out those events by login, you will need to create a separate audit. Assuming you want to filter out all activity of any kind by this service account, you can do this:

ALTER SERVER AUDIT OurAudit WITH (STATE = OFF);
GO
ALTER SERVER AUDIT OurAudit WHERE server_principal_name <> N'service account name';
GO
ALTER SERVER AUDIT OurAudit WITH (STATE = ON);
GO

Depending on how they connect, authenticate, perhaps impersonate, and how the login is mapped to the database user, you may need to experiment with this and filter against database_principal_name or session_server_principal_name instead of server_principal_name. I just tested this on SQL Server 2012 and it seems to work as you would expect. I don't believe it will work on 2008 or 2008 R2 (not relevant for you, but for future readers).

I still think that filtering a large portion of your activity seems suspect and not really in the spirit of auditing. You must have pretty laissez-faire auditors if they just trust that you protect your passwords for service accounts. Someone must know them, right? An auditor's job is to not inherently trust anyone.

Related Question