Why you need to integrate data auditing into your SQL Server database continuous integration process

Why auditing?

Providing a detailed, accurate and current audit trail for your database is no longer optional. With GDPR and other corporate governance requirements, as well as regular risk-mitigation, auditing is a requirement that can no longer be ignored, especially if it can be integrated quickly and easily, even in a dynamic environment using continuous integration and delivery.

Why auditing triggers?

There are many commercial auditing packages for SQL Server and we offer some ourselves (see below) but for many applications, particularly dynamic systems that are continuously being developed, SQL Server triggers provide a viable, and in many cases, the best solution

Trigger based auditing for sensitive tables and/or low throughput systems is a great value add to most systems. Triggers are part of SQL Server itself, can be easily changed and modified and can be used successfully to implement totally customized audit plans. The audit repository can easily be queried and reported against and the reports can even be integrated into client applications. For shrink-wrapped commercial software that ships with a database, triggers are the only viable option to make them self-auditing, which is why this is a popular approach for commercial software vendors.

Troubles with triggers

Although performance concerns are often raised with triggers, they are generally associated with misuse. Triggers shouldn’t be applied to every table, and certainly not tables involved in bulk copy or import operations or extremely high throughput tables. But for the majority of tables with sensitive data, triggers offer a virtually zero-cost potential to provide complete DML auditing.

The biggest drawback with triggers is maintenance. Nobody wants to write thousands of triggers or track which ones need to be updated because the underlying table has changed. But not maintaining triggers can result in holes in your audit trail and even unexpected errors in the database and client application.

Note: for transaction log based auditing see ApexSQL Log. For an enterprise auditing solution that uses a combination of SQL profile and extended events, see ApexSQL Audit.

Why ApexSQL Trigger?

ApexSQL Trigger was designed to address that problem. ApexSQL Trigger is a Rapid Application Development (RAD) template-based trigger creation tool. For the chosen database, you can create a template (or use ours), create an audit plan of selected tables, columns and audit operations e.g. insert and it will instantly create all of the triggers needed to fully apply the audit plan. If a table changes, recreating all of the triggers is just as easy. For a more detailed look see the article A high level overview of data auditing with ApexSQL Trigger

Why auditing in continuous integration?

Triggers have been difficult to maintain in a static database development environment, but when this environment is made dynamic by the introduction of continuous integration and delivery, the problem becomes much worse. But this just means that the solution provided by ApexSQL Trigger becomes much more useful.

By being able to integrate ApexSQL Trigger seamlessly into a continuous integration workflow, triggers can be automatically added, quickly and easily to every test/temporary database that is built as part of a CI pipeline, regardless of any changes made to the underlying tables.

Any changed triggers will automatically be picked up in the continuous delivery pipelines comparison and synchronization process and ported to production

CI and auditing – easier than you think

To add auditing to your CI pipeline is incredibly simple.

Once your temporary/test database has been created, process the previously constructed audit plan of selected tables, columns and operations e.g. Inserts, along with any value added features like Watches and Lookups against it with ApexSQL Trigger. ApexSQL Trigger will create the auditing triggers and automatically execute them against the database

Since ApexSQL Trigger has a console application, it can be run unattended. It has a rich command line interface as well as the ability to consume ApexSQL Trigger configuration projects, to customize the trigger generation process fully and easily.

If you want to modify your audit plan, simply open ApexSQL Trigger, make the necessary changes and save the project. You won’t need to make any changes to your CI pipeline as it will automatically pick up the changes from the saved project.

Once the audit step has been processed a variety of artifacts will have been created including

1. The SQL file of CREATE TRIGGER statements that will add all of the triggers
2. A summary of the execution of the SQL file
3. A summary of the step itself, including return/error codes and any command line output, generally set to VERBOSE by default

CI auditing in action

Here is what the Audit step looks like in the PowerShell implementation of the ApexSQL CICD toolkit

For the step’s pipeline designation:

$options = New-ApexSqlOptions -PipelineName "CI_Pipeline"

For the audited database connection:

$dsQA = New-ApexSqlDatabaseConnection -ConnectionName "qaDB_dest" -Server 
"DEVOPS\SQLEXPRESS" -Database "QA_db" -WindowsAuthentication

For the step initialization:

Invoke-ApexSqlAuditStep -Options $options -Database $dsQA | Out-Null

Here it is in the Web dashboard

The inputs include the name of the test/temporary database, a NuGet package to add information to, the path to the ApexSQL Trigger configuration settings project file and any additional command line parameters

And again in the TeamCity build server plug-in

What are the costs, requirements?

To add trigger based auditing to your CI pipeline requires at least one license of ApexSQL Trigger, regardless of the number of developers. It is licensed per user, so a license is required for anyone actively working on managing the audit plan and working with ApexSQL Trigger directly. A license isn’t required for every developer on your team, nor is a license required for a larger bundle, toolkit

Conclusion

I hope we demonstrated both the power and ease of auditing dynamic databases for data changes and how easy it is to add to your existing SQL Server database continuous integration pipelines

For more information, see

• The ApexSQL CICD toolkit product page
• The ApexSQL Trigger product page

As well as the following articles

• How to track DDL/schema changes
• How to manage triggers in ApexSQL Trigger
• Using the template editor in ApexSQL Trigger
• ApexSQL Trigger Command Line Interface (CLI) switches
• Auditing reports in ApexSQL Trigger
• How to automate adding SQL auditing triggers to a new, or changed database
• How to automate trigger-based SQL Server auditing
• How can I make my custom applications self auditing?
• How to create and use DML triggers to audit data changes
• How to add self data change auditing capabilities to commercial, shrink-wrapped software with a SQL Server backend
• How to create and use DML triggers in SQL Server using real world examples