6

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.

5

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.

  • Just because it's called "audit" doesn't really mean it is for auditors, for example when decommissioning an old application we might audit the table to make sure no one is using it (we know the app is using it, so that login can be filtered out) but we need to figure out if anyone else started relying on this table over the years and let them know its going to be decommissioned just as an example. – gareth Jun 26 '18 at 9:23
  • @gareth In general, sure, but RThomas explicitly said, "...this is to prove to our external auditors..." so in this case it really does mean it is for auditors. – Aaron Bertrand Jun 26 '18 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.