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.
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
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)
ALTER EVENT SESSION NonSAPAccessAudit ON SERVER STATE = START
/*ALTER EVENT SESSION NonSAPAccessAudit ON SERVER STATE = STOP
GO*/
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. 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.
As a test I ran the following script to read from some of the HR tables:
EXEC sp_helplogins
USE OMA
SELECT * FROM oma.HRP1000
SETUSER 'oma'
SELECT * FROM HRP1033
SELECT * FROM HRP1032
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_session_targets 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)
-- For the File Target
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
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 Preview:
In the upcoming new SQL Server release 2012 the X-Event Session can be replaced by a server wide audit specification:
USE [master]
-- 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')
-- start the audit
ALTER SERVER AUDIT [mySAP] WITH (STATE = ON)
-- 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)
USE [OMA]
-- Create the database specific audit for the SAP database
CREATE DATABASE AUDIT SPECIFICATION [mySAP_database]
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])
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:
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
Regards
Clas