Hello. And welcome to this apex SQL audit general overview video. ApexSQL Audit is a SQL server auditing tool capable of auditing nearly 200 SQL server events on both server and database levels. Audited events are stored in a single-tamper evidence central repository database regardless of the number of audited servers or databases.
Collected information can then be reviewed or exported through existing or user-defined reports in various formats, which allow ApexSQL Audit to be a solid solution for users that need to meet different compliance standards ApexSQL Audit can be used to help achieve the following compliance standards-- HIPAA, Sarbanes-Oxley, PCI, Basel II and more.
ApexSQL Audit consists of three components-- the main application, the server-side components, and the ApexSQL Audit reporting application. The server-side components are a simple Windows service that needs to be installed on remotely-located machines to allow auditing of all SQL servers that reside on that computer.
While there is no limit regarding how many SQL server instances can be audited, there can only be one main instance which includes the central service that communicates with remote instances and central repository database, tamper evidence central repository database used to store configuration data in tamper evident form and detect all changes made to it. Changes to the repository not done through ApexSQL Audit are considered as tampering.
Next, the graphical user interface is used to configure auditing, set up alerts, create reports, and other tasks. The already-mentioned server-side components are also integrated into the main application. So all local SQL server instances can be easily added for auditing.
The final component, the ApexSQL Audit reporting application, is a separate component, which can be installed on any machine within the local network or domain to enable the creation of auditing reports without the need to directly access the graphical user interface. Once the ApexSQL Audit main application has been installed in addition to any number of other components, auditing does not start automatically.
This is done in order to prevent auditing of large quantities of events that occur on the SQL server and have no real value to the user or auditors. To set up the auditing, the user first needs to add SQL server instances that will be audited via the user interface and then to configure auditing filters. To do so, click on the Configure button in the auditing group in the main ribbon.
Next, click on the Add Server button in the servers panel on the left. Select a SQL server instance in the server name dropdown menu or type the server name manually and click on the Add button. Next, in the server properties panel, provide a valid username and password and choose a location for temporary files. And then click the OK button.
Now that the SQL server instance has been added for auditing, the user needs to specify which server and database-level events are going to be audited. Click on the SQL server instance and check operations on the server level for auditing. Include or exclude specific applications or SQL server logins. To configure auditing on a database level, click on the Add Database button.
Check databases that need to be audited and click on the Add button. Check operations and configure object filters for each database separately. And click the Apply button. When configuration is completed, auditing can be started.
In addition to manual configuration, users can choose from a variety of existing, premade filters based on compliance standards and implement them by clicking on the compliance drop menu, choosing a specific compliance standard, and applying it to multiple databases at once. An alternative to the simple filter is the advanced auditing filter which can be used to achieve even greater granularity in precision when required.
It utilizes logical conditions, which can be combined without any limitations to achieve desired precision. Simply add conditions and configure them in the most suitable way for your environment. And click Apply to initiate auditing. If advanced filter is used, it will apply to the selected SQL server instance in full including all databases and will not be used in conjunction with simple filters.
Now that we've seen how to address general auditing, let's briefly address an additional but separate auditing feature-- before-after auditing. While general auditing and ApexSQL Audit utilizes SQL server traces and extended events to audit SQL server events, the before-after auditing uses database CTE triggers to capture the before-after data values.
To set up before-after auditing, click on the Before-After button in the main application ribbon. And then click on the Add Database button. From the databases list, check the database for auditing, and click the Add button. Now, click on the Add Table and choose the tables for before-after auditing. And then check Insert.
Update and delete checkboxes on specific table columns that will be used for auditing for before-after changes. Finally, click on the Apply button to start the auditing. General auditing and before-after auditing are entirely separate features and can be used in conjunction or separately depending on the auditing requirements.
Even so, regardless of the auditing type, audited information is consolidated and collected in the single central repository database for all audited SQL server instances. And now that the auditing has been configured, let's quickly look at how to extract audited information and create various auditing reports.
ApexSQL Audit comes with many out-of-the-box reports, which can be quickly previewed or generated by simply clicking on the Reports button in the main application ribbon, selecting a report from the Reports dropdown menu, and creating a report preview by clicking on the Preview button or choose to immediately generate a report in any of the supported formats.
All existing common reports can be filtered with various filters in order to get specific reports. So feel free to use server, database, login, application, operation, event or any other available filters to fine-tune the report information. This means that users may customize their reports as their needs require.
Users can
09:50