This article first appeared in the SQLServerGeeks Magazine.
Author: Josephine Bush
Subscribe to get your copy.
In this article, you learn about the differences between Extended Events and SQL Server Audit. You will also get the basics of how to setup an Extended Event. For a summary of what auditing is and how to use SQL Server Management Studio to create SQL Server Audit via the GUI, please refer to the July SQL Server Geeks magazine. To see a summary of how to script out SQL Server auditing, please refer to the August SQL Server Geeks magazine.
You have a couple of choices when you want to audit SQL Server:
1. Extended Events – Commonly referred to as xevents. This is a lightweight and flexible option for auditing. You can also collect performance information with this. If you’ve used Profiler or Trace, this will feel familiar to you. It’s considered a replacement for those. It’s available since 2008, and a GUI was added in 2012. You can use the GUI or scripts to create and query the results of xevents.
2. SQL Server Audit – Also a lightweight and flexible option for auditing. You can’t collect performance information with this, though, but the auditing options are more nuanced. SQL Server Audit has been available since 2008, and by 2016, is available in all editions. You can use the GUI or scripts to create and query the results of SQL Server Audit.
Extended Events (xevents)
You can setup xevents via the GUI by right-clicking Sessions under the Management -> Extended Events section in SSMS as shown in the following screenshot:
You will get a dialog box or wizard to walk you through step-by-step setup. Once you have extended events configured you can view the audit data in the GUI by right-clicking the xevent and choosing Watch Live Data as shown in the following screenshot:
This will bring up a tab in SSMS query window with a quick view of what’s being collected in your xevent as shown in the following screenshot:
Nicely, you can also setup and query your xevent via scripts. This makes it a lot easier to duplicate your xevent on multiple servers.
The following script will setup an xevent that captures what the sa login is doing on your server:
CREATE EVENT SESSION [audit_sa] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(package0.event_sequence,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE (([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[session_server_principal_name]=N'sa'))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.event_sequence,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE (([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[session_server_principal_name]=N'sa')))
ADD TARGET package0.event_file(SET filename=N'e:\xevents\audit_sa.xel',max_file_size=(50),max_rollover_files=(4))
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [audit_sa]
ON SERVER STATE = START;
GO
When you want to see the audited records, you can use the following script:
SELECT n.value('(@timestamp)[1]', 'datetime') as timestamp,
n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as [sql],
n.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(50)') as [client_hostname],
n.value('(action[@name="server_principal_name"]/value)[1]', 'nvarchar(50)') as [user],
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(50)') as [database_name],
n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(50)') as [client_app_name]
FROM (select cast(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file(N'e:\xevents\*.xel', NULL, NULL, NULL)) ed
CROSS APPLY ed.event_data.nodes('event') as q(n)
WHERE n.value('(@timestamp)[1]', 'datetime') >= DATEADD(HOUR, -1, GETDATE())
ORDER BY timestamp desc
You may or may not see any records depending on what you chose to audit or you may see a lot of stuff you weren’t expecting because there’s a lot of stuff that SQL Server is doing behind the scenes. Here’s a screenshot to give you an idea of what the results could look like:
SQL Server Audit
To audit SQL Server, you can use built-in functionality called SQL Server audit. It’s good for capturing pretty much anything that happens on the database server. It’s very flexible and pretty easy to set up. I primarily use this for seeing any permissions or schema changes. You access it via SSMS and can also use scripts to set up and query the audit data. This article will only cover setting it up via SQL scripts. For a quick review of when and how you can use SQL Server audit, let’s go over a few things first.
Audit Requirements
To make SQL Server auditing work you need two or three things depending on what you want to audit.
• You’re required to create an audit specification, and this will determine where you store audit data.
• You will also need one server and/or one database audit for audit data to write to the audit specification. Each audit specification can have one server and one database audit. Those server and database audits are not dependent on each other.
SQL Server Audit Use Cases
The server audit is generally good for:
• Auditing server-level changes and/or all the databases at the same time.
The database audit is good for:
• Auditing one database or a subset of activities in one database.
For a summary of what auditing is and how to use SQL Server Management Studio to create SQL Server Audit via the GUI, please refer to the July SQL Server Geeks magazine. To see a summary of how to script out SQL Server auditing, please refer to the August SQL Server Geeks magazine.
Pros and Cons of Extended Events
Pros and Cons of SQL Server Audit
Extended Events vs SQL Server Audit
The following table shows you a side-by-side comparison. Topics highlighted in bold denote where the functionality of each differs.
Use Cases
The following table outlines some ways I use each of these auditing methods.
Disclaimer on Auditing
Be very careful how and what you audit. You can overload or freeze up a production server. It happened to me when I didn’t even think it was possible to crash a production server with an audit. I just thought somehow, I could stop the audit in between things it was auditing. Sometimes it’s going to be hard to stop your audit if it’s auditing too much or going to be so hard to weed through all the data, it will be like trying to find a needle in a haystack with all the stuff you’re auditing. I just go with the less is more method of auditing.
Additional Information
More information on Extended is provided in these links:
https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver15
More information on server and database audit is provided in these links:
This article first appeared in the SQLServerGeeks Magazine.
Author: Josephine Bush
Subscribe to get your copy.
