What is SQL Server Audit?
Database auditing is a critical part of compliance and security audits, aimed at protecting corporate data. In environments where SQL Server is used, auditing is a basic requirement for SQL Server security, as well as for compliance standards including ISO27001, PCI DSS, BASEL3, GDPR, IG, HIPAA standards.
SQL Server instance auditing involves:
- Specifying who will perform the audit
- Specifying what is the focus of the audit and acceptable audit results
- Tracking and recording events on a SQL Server instance
- Analyzing events and reporting on them according to agreed criteria
Microsoft provides SQL Server Audit, a tool built into SQL Server, which can read database transaction logs to provide information about data and object changes affecting the database. You can use the tool to create server-level audits and database-level audits, with several auditing levels appropriate for different compliance standards.
In this article, you will learn:
- SQL Server Audit Components
- SQL Server Audit
- SQL Server Audit Specification
- Database Audit Specification
- Target
- SQL Server Audit Best Practices
- Audit Detail vs. Performance
- SQL Server Failed Logins
- Auditing Continuity and Schema Integrity
- Audit Archiving
- SQL Server Audit Q&A
- What is a SQL Server Audit Log?
- Why Are SQL Server Audit Logs Important?
- How Do You Create an Audit Log in SQL Server?
SQL Server Audit Components
SQL Server Audit consists of several object components, including SQL Server Audit, SQL Server Audit Specification, Database Audit Specification, and a target.
SQL Server Audit
SQL Server Audit is an object that collects a single instance of actions or groups of actions requested for monitoring. The process can work for either database-level or server-level actions, and the audit remains at the SQL Server instance level. You can run multiple audits for each SQL Server instance.
To define an audit, you need to specify an audit destination, which is essentially a location for the output of the results. An audit is first created in a disabled state. This means it does not automatically audit actions. Once an audit is enabled, the audit destination can receive data from the audit.
SQL Server Audit Specification
A Server Audit Specification object works at the SQL Server instance level. You can create only one object for each audit. A Server Audit Specification object collects action groups raised by Extended Events at the server level. Each audit action group contains a pre-specified group of actions, which occur at the Database Engine. The system sends the actions to the audit, where they are recorded inside a target.
Database Audit Specification
A Database Audit Specification object collects audit actions that are raised by Extended Events at the database level. You can add two main audit types to a database audit specification object:
- Audit events—atomic actions audited by the SQL Server engine
- Audit action groups—predefined groups of actions
Both audit events and audit action groups are located at the SQL database level. You can create one database audit specification per SQL Server database per audit. Once you send actions to the audit, it records them in a target location.
Target
Audit results are sent to a target location of your choice. You can define a file as the target, a Windows Application event log, or a Windows Security event log. If you choose a log as your target, note that logs need to be reviewed and archived periodically. This helps you make sure that your target always has sufficient space to write more records.
SQL Server Audit Best Practices
The following best practices can help you perform SQL Server auditing more effectively.
Audit Detail vs. Performance
When setting up SQL Server Audit, it is important to realize there is a tradeoff between auditing and performance. The more information you add to your audits, the slower the database may perform. Therefore, you should record only the minimum data that is required for your auditing purposes.
SQL Server Failed Logins
One of the most important events you should track in auditing is failed login attempts, such as username/password input errors and password expiration, which may indicate malicious access attempts. Always store this information, and ensure alerts are raised to the security team.
Auditing Continuity and Schema Integrity
It is critical to ensure that audits are continuously recorded, and track any events that interrupt the auditing itself. Set up policies in the SQL Server auditing tool to alert on any event that could affect auditing activity—such as connection issues, permission issues, and low disk space.
In addition, some compliance standards require that DBAs are alerted on events that can compromise database schema or endanger the integrity of the entire database—for example, deletion of schemas, changes to data structure, and deletion of key tables.
Audit Archiving
SQL Server audit data accumulates over time, making it necessary to archive older audit data. This should follow your organization’s standard data archiving and retention strategy, and should be in line with compliance requirements. A common practice is to keep all archived audits on a central SQL Server instance, making it convenient to query and retrieve historical audit data when needed.
SQL Server Audit Q&A
What is a SQL Server Audit Log?
The SQL Server audit log is a file listing the results generated during Microsoft SQL Server auditing. To demonstrate compliance, organizations audit SQL Server access records, report suspicious or malicious activity, track login attempts, security changes, and other potential attack vectors.
Why Are SQL Server Audit Logs Important?
The SQL audit log is important because it can be used to assess the security of SQL Server databases, as well as to provide you with the information needed for specific compliance standards. By using a central log management system to collect and normalize log data and audit logs, you can gain important insights to ensure your SQL Server instances are secure and do not violate your compliance obligations.
How Do You Create an Audit Log in SQL Server?
When managing auditing in SQL Server, the basic element is an audit specification object. This object defines the server and database events recorded in the audit report. When defining the objects to be audited, you also define what to write to the SQL audit log file. Audit logs can be written to your own log file, or directly to the Windows Security event log or Windows Application event log.
SQL Server Auditing with Satori
With Satori’s Universal Audit, data engineering and security teams no longer need to run ad-hoc scripts, worry about target location space, assemble disjointed and disorganized logs, decide if to trade details for performance or implement costly log collection and analysis processes in order to monitor sensitive data access for privacy, legal, and security reasons.
With Universal Audit, data and security teams can use a single tool to continuously monitor data access completely, uniformly and coherently for SQL server and other databases. Universal Audit classifies sensitive data as it’s being accessed, retains an audit of all data access for an infinite amount of time and makes answering questions such as “Who is accessing PII in our SQL server?” or “Who is modifying PHI?” immediate, simple, and thorough.
