SQL Server auditing is an essential practice for organizations to ensure data security, compliance, and accountability. ApexSQL Audit is a powerful tool that enables DBAs to audit SQL Server events and meet strict compliance standards.
What is ApexSQL Audit?
ApexSQL Audit is a comprehensive auditing tool that can be used to audit over 170 SQL Server events, including DDL and DML operations, SQL Server logins, security events, and query execution. It is designed to meet complex database auditing requirements and comply with various standards such as HIPAA, SOX, PCI, FISMA, FERPA, and FDA.
How Does ApexSQL Audit Work?
ApexSQL Audit utilizes SQL Server side trace technology to capture and store audited events that match the defined auditing criteria. These events are stored in a central repository database, which can be queried using SSMS or accessed through the application GUI or web interface to create predefined or custom reports.
In addition to SQL Server side trace technology, ApexSQL Audit also uses CLR triggers as a separate mechanism for auditing before-after changes. This allows for granular auditing of data value changes (DML changes) and ensures compliance with SQL Server standards for before-after auditing.
Architecture of ApexSQL Audit
ApexSQL Audit has a distributed architecture, consisting of two main components – the main application and the auditing instance. The main application includes the central service, GUI console, and central repository database. The central service handles communication, while the GUI console is used for auditing management and setup. The central repository database stores all audited data, auditing filter settings, report templates, and alerts.
The auditing instance component needs to be installed on remote machines to audit remote SQL Server instances. This component allows for auditing of SQL Server events on remote machines and sends the audited data to the central repository database.
Installation and Setup
The installation process of ApexSQL Audit is straightforward. It is recommended to install the main application on a dedicated machine with a local SQL Server. The main application includes the application core, GUI console, and central repository database.
During the installation, the user needs to choose the SQL Server instance that will host the central repository database. Sufficient disk space should be allocated for the database, as it can grow rapidly depending on the amount of audited events.
To audit remote SQL Server instances, the user needs to install the auditing instance component on the remote machine hosting the SQL Server instances. The central auditing address should be provided during the installation to establish the connection with the central repository database.
Auditing Setup
Once the main application and auditing instances are installed, the user can set up the auditing using the GUI console. The user needs to add the SQL Server instances to be audited and define the auditing filters based on the desired events, applications, logins, databases, and objects.
ApexSQL Audit provides both simple and advanced filtering options. The advanced filter allows for maximum granularity but requires extra effort to set up. The simple filter offers a quicker setup but with less precision.
Before-after auditing, which is separate from the standard server-side trace-based auditing, can also be set up using ApexSQL Audit. This feature allows for auditing of data value changes using CLR triggers.
Reporting and Alerting
ApexSQL Audit offers flexible reporting options. Reports can be generated from the GUI console or accessed through a web browser. Users can choose from pre-defined reports or create fully customized reports based on logical conditions.
The reporting module allows non-technical users to create SQL Server compliance reports without affecting the auditing process. Reports can be exported in various file formats such as PDF, CSV, Excel, and Word.
Alerting in ApexSQL Audit enables users to set up system or custom alerts for specific events. These alerts can be auditing alerts, before-after alerts, or custom script alerts. Users can customize the information included in the alert, set alert severity, and choose the SQL Server instances where alerts will be active.
Conclusion
SQL Server auditing is crucial for maintaining data security and compliance. ApexSQL Audit provides a comprehensive solution for auditing SQL Server events and meeting strict compliance standards. With its distributed architecture, advanced filtering options, and flexible reporting capabilities, ApexSQL Audit empowers DBAs to effectively audit and monitor SQL Server instances.