This Blog will provide information about running SAP applications on SQL Server and Windows. The Blog is written by folks of Microsoft who are working with SAP and SQL Server for more than a decade.
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.
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*.*'
CREATE EVENT SESSION NonSAPAccessAudit ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
AND [sqlserver].[username]!='NT AUTHORITY\SYSTEM'
AND source_database_id= 5 ) -- database_id in SQL 11
ADD TARGET package0.asynchronous_file_target(
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY= 4096KB,
MAX_DISPATCH_LATENCY= 10 SECONDS,
ALTER EVENT SESSION NonSAPAccessAudit ON SERVERSTATE=START
/*ALTER EVENT SESSION NonSAPAccessAudit ON SERVER STATE = STOP
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:
SELECT * FROM oma.HRP1000
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
XEvent.value('(action/text)','datetime') AS Date,
DB_NAME(XEvent.value('(data/value)','int')) AS DB,
XEvent.value('(action/value)','varchar(max)') AS Application,
XEvent.value('(action/value)','varchar(max)') AS SessionNTUser,
XEvent.value('(action/value)','varchar(max)') AS SQLUser,
XEvent.value('(action/value)','varchar(max)') AS SQLText
(SELECT CONVERT(XML,st.target_data)AS TD
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)
-- For the File Target
'datetime') AS Date,
'int')) AS DB,
'varchar(max)') AS Application,
'varchar(max)') AS SessionNTUser,
'varchar(max)') AS SQLUser,
'varchar(max)') AS SQLTextFROM
(SELECT CONVERT(XML,event_data) AS TD
'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:
In SQL Server release 2012 the X-Event Session can be replaced by a server wide audit specification:
-- Create the Server Audit with a filter
CREATE SERVER AUDIT[mySAP]
,MAXSIZE= 0 MB
-- 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]
-- Create the database specific audit for the SAP database
CREATE DATABASE AUDITSPECIFICATION[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,
FROM fn_get_audit_file('E:\Audit\mySAP*',NULL,NULL) ORDER BY event_time DESC
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_LOGWITH(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.
I'd recommend being a little more specific about there where clause as I could log onto the SQL Server using the SAP account from another machine (assuming that it's a domain account which it needs to be for SQL to be on another server).
WHERE (( [sqlserver].[username]!='WSI6443OMA\SAPServiceOMA'
AND [sqlserver].[username]!='NT AUTHORITY\SYSTEM'
AND [sqlserver].[session_nt_user]!='SYSTEM' )
AND [sqlserver].[host_name]<> 'YourSAPServer')
AND source_database_id = 5 ) -- database_id in SQL 11
(Not sure if the [sqlserver].[host_name] is correct or not, I'm typing this off the top of my head.)
Other than that, great post.
@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.