5. Auditing

Comments are closed
How can we help?
< All Topics
Print

Default audit

All schema, server- and SQL Server Agent job changes are audited by default.

Audit information is available in the dbo.v_audit view.

Audit information is retrieved every 30 seconds by default. This value can be changed in the “Collect audit information every” entry on the “Timers” tab in the Settings menu:

Changes can be visually presented in the dashboards by enabling “Changes”:

Note, only the last 100 changes in the selected time range will shown.

This makes it possible to see the immediate effect of schema changes, server configuration changes etc.:

It is possible to exclude servers, databases, objects, users etc. from the audit, by defining exclusions in the dbo.audit_whitelist table.

Note, the dbo.audit_whitelist table supports wildcards (%).

The Audit dashboard contains indepth information about changes.

Extended audit

Extended audit makes it possible to audit SQL statements e.g. SELECTS, UPDATES, INSERTS, DELETES, EXEC etc.

Enabling extended audit will help meet the requirements from e.g.:

  • General Data Protection Regulation (GDPR)
  • Health Insurance Portability and Accountability Act (HIPAA)
  • Payment Card Industry Data Security Standard (PCI DSS)
  • System and Organization Controls (SOC) 1 and 2
  • California Consumer Privacy Act (CCPA)
  • Sarbanes-Oxley Act (SOX)
  • Gramm-Leach-Bliley Act (GLBA)
  • Family Educational Rights and Privacy Act (FERPA)
  • Basel II
  • NIS2

Extended audit is disabled by default, but can be enabled by adding rows to the dbo.audit_extended_include table.

Example: To audit all statements by domain users where the first part of the user name is ADM- (e.g. ADM-LHG):

Example

To audit all statements from a specific SQL login user on a specific SQL Server:

When changing rows in the dbo.audit_extended_include table, a reinitialization of monitoring must be made, or disable- and enable monitoring again on the affected servers.

After the reinitialization, all statements that meets the requirements from dbo.audit_extended_include will be logged and available through the dbo.v_audit_extended view.

Note, statements with a duration greater than or equal to the “Slow executions threshold” are always available in the dbo.v_top_completed view no matter if extended audit is enabled or not. The “Slow executions threshold” value (default 5 seconds) can be set in the Settings menu in the Performance Store Control Center:

To exclude databases or users from the audit, add the statements to the dbo.audit_extended_exclude table.

The database_name and username columns in the dbo.audit_extended_include and dbo.audit_extended_exclude tables supports wildcards. This makes it possible to e.g. audit all statements except for statements executed by service accounts.

Note, the extended audit functionality will not include executions in system databases nor executions with 0 logical reads.

Adding rows to the dbo.audit_extended_include and dbo.audit_extended_exclude tables will increase the size of the Performance Store extended events session. Extended event sessions have a limitation on how big they can be, so keep the number of rows in these two tables as few as possible.

If reaching the size above the extended events session limit, the following error will be shown in the Performance Store Control Center when trying to enable monitoring on a server:

Example: Audit all statements except for statements executed by service accounts (service accounts starts with “SER-“), except for two databases, where service account statements should also be audited:

In dbo.audit_extended_include:

In dbo.audit_extended_exclude:

The values in dbo.audit_extended_include and dbo.audit_extended_exclude should be understood as:

Horizontal values: columns will use AND logic

Vertical values: rows will use OR logic

E.g. the above should be understood as statements should be excluded from the audit if:

server_name is LHG_SQL01\SS01 AND username LIKE ‘DEV\SER-%’ except if database_name is testdb1

OR

server_name is LHG_SQL01\SS01 AND username LIKE ‘DEV\SER-%’ except if database_name is testdb2

Audit technology

Audit is handled by two different processes: Server Event Notifications and XEvent sessions.

Server Event Notification includes all DDL server events, and the XEvent session handles custom made actions that are not available through DDL server events e.g. SQL Server Agent job changes.

Note, SQL Server Agent job audit is done by activating the “lock acquired” Extended Event class. In some scenarios, this can have a negative effect on performance. SQL Server Agent job audit can be enabled or disabled (default) in the Performance Store Control Center under “Montored servers”:

Table of Contents