This article will explain the capability to automatically configure newly created databases for auditing with the ApexSQL Audit tool
To minimize the necessity for micromanagement and possible long communication chains from database creation to database auditing configuration, ApexSQL Audit offers the capability to automatically detect newly created databases and deploy auditing configuration on that event. The option works on the SQL Server level and this article will explain how to utilize it.
Default configuration template
Before enabling the feature to add SQL audit configuration automatically first the audit configuration will have to be defined as the default template. This is the audit configuration that will be applied automatically to a database upon creation.
The Database configuration template is located under the Automatic auditing tab in the main Options window. From here the configuration is defined in the same way as when manual SQL audit deployment is applied – desired database operations should be checked for auditing and when this is done the setup can be confirmed with the OK button:
When the default configuration template is configured the automatic auditing can be enabled now. This functionality can be enabled for every audited instance separately. To enable it, perform the following operations:
- Select desired SQL Server instance from the audited instance tree in the Server panel
- Click on the Additional drop-down button
- Check the Add database automatically option
In case a default configuration template was not defined previously when checked, the Add database automatically option will offer to define one before enabling it:
Additionally, next to the Add database automatically option the Edit template link can be found which will, when clicked, open the Database configuration template dialogue to change the current template before enabling automation:
When everything is configured as previously explained, every created database on the selected SQL Server instance will be automatically added for auditing. Further editing of applied audit configuration will, naturally, be possible when needed as with any other database manually added for auditing.
When this event (a database is added for auditing) occurs it is possible to receive an email notification through built-in (preconfigured) alert which can be seen as one of the system alert items in the main panel of the Manage view.
Enabling email notification will just require to update the alert configuration to assign an email profile that contains a list of recipients for this notification. To do that, click on the Automatic auditing alert item to select it, then click the Edit button to open the configuration wizard for this alert:
More details about alert customization wizard can be found in the article: How to create a custom alert with an email notification
Using this database auto-auditing capability will reduce the chance for oversight and missed events due to time lapse between database creation and manual audit application in critical audited environments.