Auditing – audit all except the SAPService

As the SAP database is one of most important assets you have, it makes sense to monitor any access to the database, that is not driven by the SAP System itself. You can do this by setting up an extended event session, that monitors all accesses, except these from the SAPService<SID> and SYSTEM account. Normally the SAP system is started by the SAPService<SID> account and the SQL Server from the SYSTEM account. If you have changed this, you have to change the Extended Event Session and set the correct accounts accordingly. Update: In my example I'm using the local account SAPServiceOMA from the WSI6443OMA server, as the installation of the SAP system is a local installation. If you are using a domain installation you have to use the domain user to filter e.g. MYDOMAIN\SAPServiceOMA.

The X-Event session has two targets, a ring buffer of 4 MB and a file target with 10 x 1GB files in a temp directory. Please ensure that there is enough space available on disk to hold these 10 GB of log files.
The ring buffer shows only the last few events, the file target can hold much more events.
The SAP System ID in this case is OMA, you have to change this and the server name (WSI6463OMA) for your system as well.

USE master

GO

IF EXISTS(SELECT * FROM sys.server_event_sessions

          WHERE name='NonSAPAccessAudit') BEGIN

    DROP EVENT SESSION NonSAPAccessAudit ON SERVER;

    EXEC xp_cmdshell 'del c:\temp\NonSAPAccessAudit*.*'

END

GO

CREATE EVENT SESSION NonSAPAccessAudit ON SERVER

ADD EVENT sqlserver.sql_statement_completed(

    ACTION (sqlserver.client_app_name,

            sqlserver.session_nt_username,

            sqlserver.sql_text,

            sqlserver.username,

            package0.collect_system_time)

    WHERE ([sqlserver].[username]!='WSI6443OMA\SAPServiceOMA'

        AND [sqlserver].[session_nt_user]!='SAPServiceOMA'

        AND [sqlserver].[username]!='NT AUTHORITY\SYSTEM'

        AND [sqlserver].[session_nt_user]!='SYSTEM'

        AND source_database_id= 5 ) -- database_id in SQL 11

)

ADD TARGET package0.asynchronous_file_target(

    SET filename='c:\temp\NonSAPAccessAudit.xel',

        max_file_size=1024,

        max_rollover_files=10,

        increment=128 ),

 

ADD TARGET package0.ring_buffer

    WITH (MAX_MEMORY= 4096KB,

          EVENT_RETENTION_MODE=NO_EVENT_LOSS,

          MAX_DISPATCH_LATENCY= 10 SECONDS,

          MAX_EVENT_SIZE= 0KB,

          MEMORY_PARTITION_MODE=NONE,

          TRACK_CAUSALITY=ON,

          STARTUP_STATE=ON)

GO

ALTER EVENT SESSION NonSAPAccessAudit ON SERVERSTATE=START

GO

/*ALTER EVENT SESSION NonSAPAccessAudit ON SERVER STATE = STOP

GO*/

 

Update: As the sqlserver.sql_statement_completed event has a special behaviour for password related content, you should consider Jonathans Blog.

 

As soon as the session is started, it records all accesses in the OMA database, that are from different users other than SAPServiceOMA and SYSTEM. This is save, as you cannot logon interactively to the system with any of these two logins. Update: It also prevents the attempt to create and use a SAPServiceOMA account from a different server (e.g. WSI7755OPA\SAPServiceOMA), as for this you have to have a valid login in the SQL Server. If it is a domain installation, you can't use the MYDOMAIN\SAPServiceOMA account, as you can't use it for an interactive logon to any server, it is a pure background account. Be aware, that if the attacker is a domain administrator, he can change this.

The used trace file should be secured against any direct access through a operating system user, to ensure that the files didn’t get tampered. This can be done by giving only the startup account of the SQL Server full control of the directory and revoking the right to delete and to delete files. This ensures, that the files are not deleted through the usage of xp_cmdshell. Be aware, that if the attacker is a domain administrator, he can change this.

As a test I ran the following script to read from some of the HR tables:

USE master

GO

EXEC sp_helplogins

GO

USE OMA

GO

SELECT * FROM oma.HRP1000

GO

SETUSER 'oma'

GO

SELECT * FROM HRP1033

SELECT * FROM HRP1032

GO

 

I was using the sidadm user (WSI6443OMA\omaadm) to connect to the SQL Server, then I switched to the master database and looked at the logins.

You can see, that after switching to master, there are no entries in the event session, as we limited the session to the SAP database (Database ID = 5 on my system).

Later I switched back to the SID database OMA and selected directly from oma.HRP1000. Even when I switch the schema to oma by running a setuser command, we can determine which NT user run the different statements. When we are in the oma schema, the SQLUser changes from WSI6443OMA\omaadm to oma, but he SessionNTUser stays the same.

To get the information out of the ring buffer and the file, I used this script:

-- For the Ring Buffer

SELECT

XEvent.value('(action/text)[5]','datetime') AS Date,

DB_NAME(XEvent.value('(data/value)[1]','int')) AS DB,

XEvent.value('(action/value)[1]','varchar(max)') AS Application,

XEvent.value('(action/value)[2]','varchar(max)') AS SessionNTUser,

XEvent.value('(action/value)[4]','varchar(max)') AS SQLUser,

XEvent.value('(action/value)[3]','varchar(max)') AS SQLText

FROM

(SELECT CONVERT(XML,st.target_data)AS TD

FROM

sys.dm_xe_sessionss ON s.address=st.event_session_address

WHERE s.name='NonSAPAccessAudit' and

st.target_name='ring_buffer') AS Data

CROSS APPLY TD.nodes('//RingBufferTarget/event') AS XEventData(XEvent)

GO

 

-- For the File Target

SELECT

TD.value('(/event/action[@name=''collect_system_time'']/text)[1]',

'datetime') AS Date,

DB_NAME(TD.value('(/event/data[@name=''source_database_id'']/value)[1]',

'int')) AS DB,

TD.value('(/event/action[@name=''client_app_name'']/value)[1]',

'varchar(max)') AS Application,

TD.value('(/event/action[@name=''session_nt_username'']/value)[1]',

'varchar(max)') AS SessionNTUser,

TD.value('(/event/action[@name=''username'']/value)[1]',

'varchar(max)') AS SQLUser,

TD.value('(/event/action[@name=''sql_text'']/value)[1]',

'varchar(max)') AS SQLTextFROM

(SELECT CONVERT(XML,event_data) AS TD

FROM sys.fn_xe_file_target_read_file('c:\temp\NonSAPAccessAudit*.xel',

'c:\temp\NonSAPAccessAudit*.xem',null,null)) AS Data

GO

With this extended event session all DBA activity in the SID database is audited, but as the SAP Administrator (sidadm) is a sysadmin on the SQL Server, you cannot prevent this user to stop and delete this trace, to obscure a suspicious activity. A sysadmin always can change or drop any session, as he can take over the ownership of the trace file on the operating system level and then delete or modify it. There are ways to monitor these changes more deeply (e.g. OS file auditing, additional X-Event session to monitor X-Event session modifications), but currently there is no way, to ensure that all activity can be monitor and that only one dedicated user can control this session

As this X-Event session might have a measurable performance impact, you should test this on your Test or Development system, before you bring this into production.

SQL Server 2012:

In SQL Server release 2012 the X-Event Session can be replaced by a server wide audit specification:

 

USE[master]

GO

-- Create the Server Audit with a filter

CREATE SERVER AUDIT[mySAP]

TO FILE

(FILEPATH=N'E:\Audit'

,MAXSIZE= 0 MB

,MAX_ROLLOVER_FILES= 2147483647

,RESERVE_DISK_SPACE=OFF

)

WITH

(QUEUE_DELAY= 1000

,ON_FAILURE=CONTINUE

,AUDIT_GUID='fd079bdc-bc2b-4eba-93fe-822ff655facc'

)

WHERE ([server_principal_name]<>'WSI6443OMA\SAPServiceOMA')

GO

-- start the audit

ALTER SERVER AUDIT[mySAP] WITH (STATE=ON)

GO

USE[master]

GO

-- Create the audit specification for login attempts and audit changes

-- and start the audit

CREATE SERVER AUDIT SPECIFICATION[mySAP_server]

FOR SERVER AUDIT[mySAP]

ADD (AUDIT_CHANGE_GROUP),

ADD (FAILED_LOGIN_GROUP)

WITH (STATE=ON)

GO

USE[OMA]

GO

-- Create the database specific audit for the SAP database

-- and start the audit

CREATE DATABASE AUDITSPECIFICATION[mySAP_database]

FOR SERVER AUDIT[mySAP]

ADD (UPDATE ON DATABASE::[OMA]BY[dbo]),

ADD (SELECT ON DATABASE::[OMA]BY[dbo]),

ADD (INSERT ON DATABASE::[OMA]BY[dbo]),

ADD (DELETE ON DATABASE::[OMA]BY[dbo])

WITH (STATE=ON)

GO

The replacement of the X-Event session through a server audit only is possible, as we then can filter the audit by an server principal name, as shown above. This server audit will also monitor changes to the audit itself.

You can read the generated audit file with:

SELECT

  event_time AS c,

  server_principal_name,

  database_principal_name,

  object_name,

  statement,*

FROM fn_get_audit_file('E:\Audit\mySAP*',NULL,NULL) ORDER BY event_time DESC

GO

Update: If you need the Audit Events in the Windows Application Log, you have to replace the FILE clause with an APPLICATION_LOG clause: 

-- Create the Server Audit with a filter
CREATE SERVER AUDIT[mySAP]
TO APPLICATION_LOG
WITH
(QUEUE_DELAY= 1000 ..

When writing into the Windows Security Log you have to configure additional setting in windows. See this TechNet article for more details.

Regards

  Clas

Comments

• Anonymous
  January 10, 2012
  The comment has been removed

• Anonymous
  September 26, 2013
  @mrdenny: Many thanks for the feedback, but you can't logon with that domain account, as it has no "logon locally" priviledge granted. And if you create a new local user SAPServiceOMA on the remote host, you have to have a SQL Server Login for it. I updated the Blog accordingly. Many thanks Clas

• Anonymous
  January 15, 2014
  There is a bug in the Ring buffer query.  Here is the correct on: SELECT XEvent.value('(action/text)[5]','datetime') AS Date, DB_NAME(XEvent.value('(data/value)[1]','int')) AS DB, XEvent.value('(action/value)[1]','varchar(max)') AS Application, XEvent.value('(action/value)[2]','varchar(max)') AS SessionNTUser, XEvent.value('(action/value)[4]','varchar(max)') AS SQLUser, XEvent.value('(action/value)[3]','varchar(max)') AS SQLText FROM (SELECT CONVERT(XML,st.target_data) AS TD FROM sys.dm_xe_session_targets AS st  join sys.dm_xe_sessions s ON s.address=st.event_session_address  WHERE s.name='NonSAPAccessAudit' and  st.target_name='ring_buffer') AS Data CROSS APPLY TD.nodes('//RingBufferTarget/event') AS XEventData(XEvent) GO

• Anonymous
  April 10, 2014
  Hi, Good article, just small correction. SQL Server Audit is available since SQL Server 2008.

• Anonymous
  September 30, 2014
  Hi Peter, SQL Server Audit is available since SQL Server 2008 indeed, but audit can be filtered to login,account since sql 2012 only.