Sql-server – How to change user running audits mssql

auditscriptingsql server

I've been tasked with setting up our SQL server audits – despite having never done this before. So please forgive a really basic question.

I setup a specific user, sqlAudits, that I would like to own the audits.

How can I configure an audit to run under the sqlAudits account?

I've searched for hours but all I find it how to audit users, not how to change the account running the audit.

Can anyone point me in the right direction?

Best Answer

despite having never done this before

that's not a problem at all - I just would recommend you do all this on a test server first. the whole process. then you do it in live.

This is how I would do it:

you create the domain account sqlaudits as you mentioned and take note of its password as you will need it later on
create a login for sqlaudit on the server you want to audit
grant sqlaudits all the permissions it might need: on that server

Check the permissions for altering an audit:

a)To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

b)Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.
run ssms as a different user using the sqlaudits account credentials

to run ssms a different user press the shift and right click on the ssms icon and that will give the options below:

Now you are sqlaudits and the objects you create will be technically owned by you

Related Solutions

Can this be accomplished with Oracles Fine Grained Auditing

Get the client to call dbms_session.set_identifier('PHILTEST');. This will then be set in the CLIENT_ID audit field. Obviously you'l need to call it first from the client whenever a new connection is pulled from the pool.

For example:

PHIL@PHILL11G2 > conn / as sysdba
Connected.
Loading glogin.sql

Session altered.

SYS@PHILL11G2 AS SYSDBA> audit all by phil by access;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> TRUNCATE TABLE fga_log$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> conn phil/phil

PHIL@PHILL11G2 > exec dbms_session.set_identifier('PHILTEST');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from fgatest;

  COUNT(*)
----------
     1

PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;

CLIENT_ID
----------------------------------------------------------------
PHILTEST
PHILTEST

2 rows selected.

SYS@PHILL11G2 AS SYSDBA>

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

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.

Best Answer

Related Solutions

Can this be accomplished with Oracles Fine Grained Auditing

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

Related Question