How to automatically revert database changes based on SQL auditing alerts using SQL Server transaction logs (4263131)

One of the problems that can be solved with SQL auditing technologies in conjunction with SQL Server transaction logs is finding specific events, i.e. specific database or data change, capturing the event, and resolving it as wanted or unwanted. Depending on methodology and tools this can be an easy or complicated task. The ApexSQL Audit tool provides the solution wherein a few easy steps a specific event can be audited, with high granularity and precision. The additional problem surfaces out in case unwanted change occurs, how to easily revert those changes? The usual methodology consists of creating SQL Server transaction log backups and restoring them. But restoring implies reverting the database to a specific moment in time, thus reverting even wanted changes.

This article will explain and provide an example on how to utilize auditing options that ApexSQL Audit offers and recovery options that ApexSQL Log has to offer and set the automation process via Windows Task scheduler to create undo scripts on every potentially unwanted change.

Setting the SQL audit configuration

The first problem that needs to be resolved is to define and capture critical database events through auditing. Let’s take an example where a specific database needs to be monitored for all table alterations and data access and changes:

Including INSERTs, UPDATEs, and DELETEs, on the specific table, in this example the Person table:

And, for this instance, we will consider all non-system logins that have access to the database’s server:

Now the SQL auditing is set, the ApexSQL Audit will be able to collect the information about occurred events that match the criteria. Based on that, the building of a fail-safe mechanism can proceed.

Setting the audit alert

When general SQL auditing is enabled, the next step is to introduce the alerting. This function can be set to target more specific events, i.e. narrow down the scope of events that we consider critical and should be covered with some fail-safe process. For this example, we will set the Before-after type alert to be triggered when specific login (e.g. the ‘Administrator’ account) performs a DML operation against the Person table:

When the event occurs, this alert can be set to provide notification about it via email, but what is more important for setting the recovery automation is that it will log the event in the Windows log, which is enabled by default:

• For more details about configuring custom alerts, see the article: How to create a custom alert with an email notification

Here is how the alert will work now: if the ‘Administrator’ account executes a statement like this one:

UPDATE [Person].[Person]SET [Title] = N'Mrs',[FirstName] = N'Alex',[LastName] = N'Brown'
WHERE ([BusinessEntityID] = 1)

Against the audited database, besides receiving a notification about it, the additional result for this event, (that will be used later) will look like on the screenshot below:

The before-after auditing report will show this event also and will reflect changes described with the UPDATE statement above:

Since we created the alert that is triggered on such exact type of events, this usually means that the event is unwanted. But the damage is already done, and a response is required.

Reverting database changes

Basic practice in database maintenance jobs is creating backups. Along with the option to recover a database due to a SQL Server failure or some other critical environmental events, database backups, with the SQL Server transaction logs, can be used to revert unwanted changes like explained in this example.

The procedure to revert changes is done in a few simple steps, providing that database backup exists. Using the SSMS’ Object Explorer, right-click on the database where SQL audit is activated. From the context menu, expand the Tasks option, expand the Restore option, and click Database:

This will open the database restore dialogue. In the dialogue, click on the Timeline button to invoke the SQL Server transaction log timeline access:

In the shown dialogue, set the database restore time to a moment that precedes the time of the event and confirm with OK to get back to the previous dialogue:

Make sure that all connections to this database are dropped and it is in the single-user mode or the restore process will fail. Confirm with OK and the restore process will be executed:

The reversion result can be easily checked using a simple SELECT statement to check at least one record with parameters used in the example UPDATE statement:

SELECT *FROM [AdventureWorks2014].[Person].[Person] WHERE BusinessEntityID = 1

And the resulting values can be compared with values in the before-after report, in other words, the before value should be found in the table again.

After the restore, another problem came to the surface. What if some other changes were introduced to the database before we were able to react? For example, someone altered a table, and in the audit report the event can be seen with a timestamp later than the before-after event:

This means that we reverted these changes even though there was intention to keep it. Situations like this can be resolved by pinpointing the exact set of records in the SQL Server transaction log and creating an undo script that can be achieved and automated using the ApexSQL Log.

Creating the Undo script

ApexSQL Log is the tool that can access SQL Server transaction logs and specific records in them and create undo scripts based on those records. Even more, the tool has the capability to be triggered through CLI so its process can be automated in conjunction with the SQL audit technologies.

Based on the example used to update the record in the Person table, the following walkthrough example will show how to pinpoint and read the event from the audited SQL Server transaction log with ApexSQL Log.

When started, the ApexSQL Log will immediately start the configuration wizard. From the initial step, connect to the server and database that has SQL audits applied:

In the following step select the scope of data sources to search the event from. In most cases, the current, Online transaction log would be sufficient. The tool will scan for audited events based on defined criteria that are certainly logged in the current SQL Server transaction log as they occurred a few moments ago:

Further on, select the Undo/Redo type of operations to process:

The following step is where details about events should be set. First, define the time frame of the events to be considered for processing. Continuous auditing is recommended as a more optimized method to search for events through logs by performing a differential scan based on previous log state, per event scope, that is stored internally in the designated .axtr file. For every scope that an undo script should be created, another name should be designated for the .axtr file:

Further, the scope of events should be defined which means that the filtering setup should match the criteria used for SQL audit alert. So, in the Tables tab select the Person table:

And in the Users tab pick the ’Administrator’ account:

The rest of the options can remain unchanged with their defaults.

At the final step, we will skip the finalization of the process, and instead, use the configured parameters to create the automation script which can be in the Batch or the PowerShell form:

Setting the undo automation

The generated CLI script will have the parameters that include the scope of events that the undo script should envelop, the connection parameters for audited instances, data sources to use, reference to the .axtr file, and the name of the exported undo script. For the undo script file name, in order to preserve undo history, it is recommended that the file name should be manually corrected to include a variable, for example a timestamp, otherwise, it will be overwritten with every CLI execution.

So the parameter that would look like this:

/undo:”C:\Users\Milan\Documents\ApexSQL\ApexSQL Log\Undo.sql”

should be changed to look like this:

/undo:”C:\Users\Milan\Documents\ApexSQL\ApexSQL Log\Undo_%SAVESTAMP%.sql”

Where %SAVESTAMP% will have to be manually defined in order to prevent M/D/Y and H:M:S format in case that system date-time settings are in that specific format as the file name cannot use ’/’ and ’:’ characters. The definition should be inserted at the top of the script:

set SAVESTAMP=%DATE:/=-%@%TIME::=-%

With the CLI script modified as explained, we can proceed with creating the scheduled task. In summary, the scheduled task will be configured to be triggered on Windows application log event and execute the CLI script.

To create the scheduled task, on the machine where ApexSQL Audit and ApexSQL Log are installed, open the Windows Task Scheduler and click on the Create task item:

In the General tab of the shown dialogue, fill in the information about the task name, and make sure that the task is executed with the highest, administrator-level privileges. This is the requirement for the ApexSQL Log CLI execution:

In the Triggers tab, click the New button, and in the opened dialogue set the task to be run On an event, choose the Application log type and in the Source field, set the ApexSQL Audit application. Optionally, a small delay can be introduced between the SQL audited event and SQL Server transaction log-based undo script creation:

In the Actions tab, click the New button and in the opened dialogue set the Start a program action and provide the path to the CLI script created with the ApexSQL Log:

With the OK button confirm the action creation and again the task creation.

At soon as SQL audit alerts and undo script scheduled tasks are created, the fail-safe mechanism is set and will enable the reversion of every defined event in case it is unwanted. The generated undo script will look like this:

The script will target that one specific event that triggered its creation but the example provided will only create this undo script, it will have to be run manually, which is the recommended method in order to maintain full control and leave room for the situation review.

In case it is required to execute the undo script immediately and automatically, that can be easily set by adding the following line at the end of the example batch script:

sqlcmd -S ServerName\InstanceName -i PathToUndoScript:\Undo_%SAVESTAMP%.sql -o PathToExecutionOutputFile:\UndoLog.txt

Conclusion

The procedure to set SQL audit alerts and scanning the SQL Server transaction log should be repeated for every critical situation that might emerge as a real problem. This way the high granularity and surgical precision can be achieved in case reverting changes is required.