Ahmad Yaseen
SQL Server database auditing - ApexSQl Audit installation Wizard

Perform a SQL Server Audit using ApexSQL Audit

May 15, 2019 by

This is the last article, but not least, in the SQL Server Audit series. In this series (see TOC at the bottom), we explained deeply the concept of the SQL Server Audit, why it becomes mandatory for each company to have a SQL Server Audit solution to track and log the different types of events at both the SQL Server instance and database levels. We also went through and compare the different methods that can be used to audit the SQL Server instances and databases.

In this article, we will discuss how to easily audit your SQL Server instances and databases using ApexSQL Audit tool. ApexSQL Audit is a SQL Server audit tool that uses extended events and profiler traces, depending on the SQL Server version to track and log audit changes

ApexSQL Audit Overview

SQL Server provides us with a number of features, that can be used to audit the different aspects of the SQL Server instance. Some of these features allow us to audit specific events at the database level and other methods allow us to audit events at both the SQL Server instance and the database levels. But as discussed deeply in the previous articles of this series, each one of these methods has specific limitations, that makes it harder to consider it as a complete SQL Server Audit solution.

ApexSQL Audit is a comprehensive SQL Server Audit solution that can be used to track and log the different types of actions performed at both the SQL Server instance and the database levels, in a way that overcomes all the limitations of the other features.

ApexSQL Audit can be easily installed to your server and automatically deployed to all machines that will be audited without the need for any manual installation from the user side. After installing it, ApexSQL Audit provides us with the ability to use the existing auditing templates to make sure that all your SQL Server instances and database meet these compliance standards. You can also customize the existing templates to meet your company requirements or simply create your own template and apply it to your SQL Servers and databases with a single click.

SQL Server database auditing - ApexSQL Audit compliance

With a large number of customizable filters, ApexSQL Audit allows you to track only what you want to audit at the deepest possible level. You can track the actions performed by a specific SQL Server login on a specific database object from a specific host while running a specific application. Again, all these configurations can be performed in a minute, as we will see later in this article. You can track each user from the time he logged in to the SQL Server instance till he finished and logged out from the system, with the ability to audit the data changes from the insert, update and delete operations and compare the values changing.

SQL Server database auditing - ApexSQL Audit Filter to perform a SQL Server audit

In order to review the captured information, ApexSQL Audit provides you with a library of built-in reports that you can run or create your own custom reports from these existing reports, with the ability to schedule these reports based on your requirements. All these auditing and reporting operations will be performed with the minimal impact on performance on the audited SQL Server instances and on the central repository server. You can also get notified, in real time, using existing alerts or your own custom alters, when an important event occurs on the audited SQL Servers, with the ability to log these alerts to the windows event log or send it via email.

SQL Server database auditing - ApexSQL Audit Alerts

ApexSQL Audit Installation

Before installing ApexSQL Audit to your server, you need to download it from the ApexSQL Download Center then run the installation media file on the server with administrator permissions. When the installation wizard initiated, you will be requested to choose from three main options:

  • ApexSQL Audit Main Application: that includes installing both the GUI and the Central repository database for SQL Server Audit purposes
  • ApexSQL Audit Main GUI: that is used to configure SQL Server instances auditing with the different auditing filters, create new alerts and manage existing ones, create and schedule reports, checking the auditing instances status information, and performing the different maintenance tasks
  • Server-side Components: A Windows service that allows auditing of SQL Server instances remotely via the ApexSQL Audit main application. See how to Audit remote instance

SQL Server database auditing - ApexSQl Audit installation Wizard

Choosing to install the first option, you will be prompted to specify where the central repository database will be hosted, administrative username and password to connect to the selected SQL Server instance and a temporary files location for the repository files, as shown below:

SQL database auditing - Setup repository

The central repository database is used by ApexSQL Audit to keep all configuration information and audited data for all audited SQL Server instances, both local and remote, with the ability to archive the repository database at any point, by creating an archived database to be used also as data source for the reports, to preserve the disk space or for safe keeping. For security purposes, the SQL Server Audit repository database will be protected using a tamper-evident design that provides complete insight into any potential tampering with both configuration and auditing information stored in that database.

After completing the installation process successfully, ApexSQL Audit installation wizard will notify you and provide you with an option to run the tool directly, as shown below:

SQL database auditing - Installation wizard successfully

Add New Server

The first window that will be shown when you run ApexSQL Audit is the Status page, that summarizes the general status for the ApexSQL Audit components and the server where these components installed:

SQL database auditing - ApexSQL Audit status

In order to track the SQL Server Audit actions that are performed on a specific SQL Server instance, the required ApexSQL Audit components should be installed to that server. To install the ApexSQL Audit components on the SQL Server to be audited, click on the Configure button under the Home functions menu, as shown below:

Database auditing tool menu

From the Server Explorer, click on the Add Server button and provide the name of the SQL Server instance, as below:

Database auditing tool - Add New SQL Server

When clicking on the Add button, you will be asked to provide administrator credentials to connect to the SQL Server instance to be tracked and install all required components. You will be also asked to specify the location for the ApexSQL Audit temporary files and the timeout settings when connecting to the SQL Server instance, as shown below:

Database auditing tool - Auditing Agent Properties page

If the provided credentials and information are valid, the ApexSQL Audit agent components will be installed to the added SQL Server, with a progress bar showing the agent installation status, as follows:

Database auditing tool - Installing Auditing agent components

Once the ApexSQL Audit agent components installed successfully to the SQL Server, the server name will be shown in the SQL Server instances list under the Server Explorer, as below:

New SQL Server Added

Add Databases

After adding a new SQL Server instance, an option to add databases for auditing purposes under that server will be displayed. If you click on the Add Database option under the Server Explorer, a list of all databases available under the selected instance will be displayed. Select the databases that you plan to perform a SQL Server Audit on it then click on the Add button, as shown below:

Add SQL Server databases

And the selected databases will be shown as a list under the SQL Server name, with the ability to add more databases or remove the database from the same Server explorer. You can also stop auditing the selected SQL Server, edit the SQL Server information or completely remove that SQL Server from the audit list, as shown below:

Data auditing - Edit SQL Server and databases

Audit SQL Server Instance Level Actions

ApexSQL Audit provides us with a wide range of actions that can be tracked at the SQL Server instance level. To achieve that, select the SQL Server that you manage to audit, click on the Configure button, under the Home menu, and a list of all types of server-level actions will be displayed. You need to select the group of actions that you are interested to audit and optionally the list of applications and logins to be audited. After customizing your SQL Server Audit solution, click on the Apply button to save these changes then review the selected SQL Server Audit actions in the action’s summary window, as shown below:

Data auditing - Configure Server Level actions to perform sql server audit

To check if there are actioned captured by the ApexSQL Audit from that server, go back to the Status window and you will see the number of actions that are performed and captured on the selected SQL Server instance, under the Operations Overview below:

Data auditing - Operations overview

To review the captured actions, click on the Reports button, under the Home menu. From the displayed report configuration window, draw your report by specifying the source, criteria and types of the captured information that you are interested in, includes, the source server, database, login, action type…etc, with the ability to specify the list of columns that you need to view and the time range for that report as below:

Database auditing tool - Columns and Time Range specifications

After drawing your criteria, review your SQL Server Audit filter selections from the Report Summary window then click Preview button to display the capture information, based on your filtration or Save button to keep the selected criteria for future use:

Database auditing tool - Server level actions SQL Server Audit report

Audit SQL Server Database Level Actions

ApexSQL Audit provides us also with a wide range of actions that can be tracked and audited at the SQL Server database level. To audit a specific SQL Server database, choose that database from the databases list under the Server Explorer, click on the Configure button, select the group of actions that you are interested in auditing, includes DML, DDL, Execute and SELECT operations, review your choices in the Summary section then click on the Apply button to save the changes, as shown below:

Database auditing tool - Database level actions to perform sql server audit

You can also expand your selection by specifying the group of SQL Server Audit actions to be audited per each table. Select your database, click on the All button, then under the Objects section, click Include to specify the list of tables to audit or Exclude to specify the list of tables not to audit. After specifying the tables list, review the choices under the Summary section then click on the Apply button to save your changes, as below:

Database change auditing tool - Database level audit on specific tables

To review the actions that are captured by ApexSQL Audit based on the configured Audit criteria, click on the Reports button, under the Home menu. From the displayed report configuration window, configure your report by specifying the criteria of the captured information that you are interested to display, includes, the source server, database, login, action type…etc, with the ability to specify the list of columns that you need to view and the time range for that report. After configuring the report selection, review your choices under the Report Summary section then click Preview to display the data or Save to keep the report for future use, as shown below:

Database change auditing tool - Database level SQL Server Audit  actions review

Export Audit Result

ApexSQL Audit allows us also to export the audit data to different files formats, including PDF, Word, Excel and CSV formats. To achieve that, configure a new report or open an existing report then click on the Generate button and choose from the available files’ formats. In a few seconds, you will see that the generated file will be saved automatically to the ApexSQL Audit folder, and a link to open that file directly will be provided, as shown below:

Database change auditing tool - Export Options

We reached the last article in the SQL Server Audit series. Hope you enjoyed it. Stay tuned for my next articles series ?

Table of contents

SQL Server Audit Overview
Implementing a manual SQL Server Audit
Creating a SQL Server audit using SQL Server Extended Events and Triggers
Auditing by Reading the SQL Server Transaction Log
Change Data Capture for auditing SQL Server
Creating a SQL Server audit using SQL Server Change Tracking
SQL Server Audit Feature Components
Using the SQL Server Audit Feature to Audit Different Actions
Performing a SQL Server Audit using System-Versioned Temporal Tables
Perform a SQL Server Audit using ApexSQL Audit
SQL Server Auditing Best Practices
Ahmad Yaseen
Latest posts by Ahmad Yaseen (see all)
Auditing

About Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields. He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer. Also, he is contributing with his SQL tips in many blogs. View all posts by Ahmad Yaseen

168 Views