ApexSQL Audit is a tool which is used to audit SQL Server events for the purpose of general auditing as well as to meet strict SQL Server compliance standards. ApexSQL Audit can be used to audit more than 170 SQL Server events, including DDL and DML operations, SQL Server logins, security events, query execution… ApexSQL Audit can be used by DBA to meet complex database auditing requirements as well as several compliance standards, including HIPAA, SOX, PCI, FISMA, FERPA, FDA and more.
ApexSQL Audit uses SQL Server side trace technology (and soon extended events) to audit events. Collected audited events that match the auditing criteria are stored in the central repository database which can be queried from SSMS or accessed through application GUI or web to create predefined or custom reports. Additionally, CLR triggers are used as a separate mechanism for auditing of before-after changes which can be implemented separately from the ‘regular’ auditing.
Architecture:
ApexSQL Audit has a distributed architecture as the auditing instance agent components must be distributed to every auditing instance, and consists of two basic components – main application and auditing instance.
- The main application is the application core, and includes the central service, GUI console and central repository database. While the central service is responsible for communication, the GUI console is used for auditing management to setup and execute auditing, alerting and reporting jobs, central service also enables auditing of all local SQL Server instances.
- The other ApexSQL Audit component is the ‘auditing instance’ – this component needs to be installed on remote machines in order to audit remote SQL Server instances
- In addition, there are two other components – web reports and before-after auditing mechanism, which we’ll look into a bit later
Central repository database:
ApexSQL Audit’s central repository database is used to store all audited data, auditing filter settings, report templates and alerts. This database is a tamper-evident, which means that when any other user or application beside ApexSQL Audit made any change to this database, the integrity breach will be reported to the user with the information on the possible tampering. ApexSQL Audit uses a 256bit hash encryption with chaining algorithm to ensure that every integrity breech is caught so that SQL Server compliance can be met.
Since this database can grow very large over long time periods, it can be archived and taken offline from SQL Server and transferred to some storage, and brought back online on demand if the need requires users to read audited information stored within
Installation:
The installation process is fairly simple, but the installation of the main application is strongly recommended to be performed on a dedicated machine with local SQL Server.
Once the installer is started, user needs to agree to EULA, and to choose the installation folder.
Once this is done, the choice between installing the main application or auditing instance should be made, and user can choose to install additional component – web reporting module, which enables users to create and run ApexSQL Audit reports from their web browser. Information on permissions and requirements can be checked in this ApexSQL Audit knowledgebase article.
If the ‘main application’ is selected, the application core, GUI and central repository will be installed on the local machine. User will first need to choose a local SQL Server instance which will host central repository database.
Note: when making a choice on the SQL Server which will host central repository database, ensure that there is a sufficient amount of free disk space, since the database can grow at a good pace, depending on the amount of audited events.
In the next of the installation wizard, the user needs to provide windows login credentials that will install and run the ApexSQL Audit service.
In the final step of the installation wizard, the user needs to specify a folder which will store all the ApexSQL Audit temporary files which include all audited information from all audited instances. This completes the installation process of ApexSQL Audit, and allows the user to audit all local SQL Server instances.
In order to audit remote SQL Server instances with ApexSQL Audit, the user needs to install the ‘auditing instance’ components on the remote machine which hosts SQL Server instances which will be audited remotely. To do this, run the ApexSQL Audit installer and choose the ‘auditing instance’ component when faced with the choice of which components to install and provide the information on the central ApexSQL Audit instance address.
Note: the central auditing address points to the actual machine, not to the SQL Server instance that hosts central repository database
To complete the wizard, simply follow the remaining steps which are the same as when main application was installed.
Auditing setup:
Once the main application and all auditing instances have been installed, the user can setup the auditing, which is done to the application GUI. Note that the auditing has yet not started – the user needs to define which events need to be audited in order to satisfy both internal and auditors requirements for SQL Server compliance.
Once the GUI console is started, the user must first add servers for auditing by clicking on the ‘Add server’ button in the main ribbon and ticking SQL Server instances from the list he wants to audit.
Once this is done, the added SQL Server instances are shown in the server list
To setup auditing of a SQL Server instance, select the instance from the server list, which will show the status of application SQL Server instance, and disk information
Now, click on the ‘Auditing’ tab where the user can choose between simple and advanced filters.
The advanced filter is based on the conditions and allows maximum granularity and setting it up may require some extra effort, to achieve the desired level of granularity.
The other option is to choose a simple auditing filter. The simple filter allows quicker setup of auditing filters, but doesn’t allow such high precision as advanced filter. Simply select event, applications, logins, databases and objects to audit and apply the filter.
Whether you opt for simple or advanced filter, once applied will initiate auditing immediately, and specified events will be captured and stored in the central repository database.
The final auditing setting is related to before-after auditing, which is, as already stated, completely separated from the standard server side trace based auditing. To set it up, click on the ‘Before-after’ tab, add database for before after auditing, and specify which events on which tables will be audited for before-after values. Before-after auditing uses CLR triggers for auditing of the data values changes (DML changes) and the audited data will be encrypted all the way since the moment it was captured by the CLR trigger, via temporarily stored in the before-after database and until it is finally transferred and stored obfuscated into the central database, allowing readability to ApexSQL Audit reports only and ensuring that SQL Server compliance standards for before-after auditing can be met.
Reporting:
ApexSQL Audit reports can be used from two different instances – from the GUI console or from a web browser. There is no limit to number of simultaneous web browser report users, as long as they are within the same domain as the main application. In addition, both web and GUI based reports can be created at the same time without any difference.
To preview audited data simply select a report and press the preview button. Additionally, user can fine-tune the report with filters, or export report in PDF, CSV, Excel, Word file formats.
In addition to a number of common (pre-defined) reports, users can create fully customized reports. Custom report filters are based on logical conditions (same as in auditing filters) so that reports can be created with accuracy that allows matching almost every specific user requirement. Teb-reporting module is a convenient feature which can be used to allow non-technical users to create SQL Server compliance reports without having to worry that they will affect auditing in any way, since web reports can only be used to create reports and not to change any auditing or application settings
Alerting:
Alerting in ApexSQL Audit allows users to set up system or custom alerts to be raised on specific events. These can be auditing alerts, before-after alerts or custom script alerts. To create an alert, click on the ‘New’ button and choose desired alert type.
Use various options to customize information that will be included in the alert, set alert severity, choose the SQL Server instances where alerts will be active and specify conditions when the alert will be raised. Again, this is achieved by utilizing logical conditions based filter, as in auditing and reporting features to ensure almost unlimited precision. In the final step, user can choose to have ApexSQL Audit send an email or write raised alert to the Windows event log.
Once the alert is raised, it can be inspected from within the application directly via ‘History’ tab.
Reference: Pinal Dave (https://blog.sqlauthority.com)
