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