Track unauthorized backups using Azure SQL Managed Instance Audits

Database Managed Instance provides T-SQL surface area functionalities that are very close to the functional surface area. One of the feature that is available in Managed Instance is taking the COPY_ONLY backups of the databases to the Azure container. This feature can be used for manual transfer of databases (if you don't use cross instance point-in-time-restore), or to keep the backups longer than 35 days.

The rich SQL surface area that supports COPY_ONLY backup might also introduce a risk that someone might take unauthorized copy of the database by creating a backup to Azure . Ony high privileged users can take the backups of the databases, so make sure that you have given proper permissions for the users. However, in some cases you must be sure that even the privileged users cannot do malicious actions. This is the same risk that you have on your on-premises SQL Server where someone can execute a T-SQL BACKUP statement to some folder and take the database content.

One mechanism to ensure that nobody can take the COPY_ONLY backup of your database is to use Transparent Data that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep your key in Azure Key-Vault. User-initiated COPY_ONLY backups are (currently) not allowed if you are using Service-managed TDE.

If you don't use TDE on the database or there is a risk that someone can remove TDE from database and then take a backup, Managed Instance provides auditing mechanism that enables you to track who performed a backup and when. This way you can always track if some unauthorized action is happening.

In the following sections you will find the script that can applied on the Managed Instance to setup Auditing that will track any backup action performed on Managed Instance.

Setup

The first step you need to do to setup auditing is to create a credential to the Azure where audit log records will be stored:

DROP CREDENTIAL [https://.blob.core.windows.net/mycontainer]

CREATE CREDENTIAL [https://.blob.core.windows.net/mycontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ”

This credential will enable Managed Instance to write the audit log records into the file on Azure Blob container. See create here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-auditing#set-up-auditing-for-your-server-to-azure-storage.

Once you have prepared your storage account, you can create Audit to the Azure Blob Storage container and the Audit specification that will capture Backup/Restore events and Changes to any audit. The following objects are required to setup the auditing:

  • Create server audit that will write audit logs in the Azure Blob Storage container.
  • Create Audit Specification that will capture BACKUP_RESTORE_GROUP and AUDIT_CHANGE_GROUP

In the following example you can find the script that setup these objects:

— Create server audit
CREATE SERVER AUDIT [BackupRestoreAudit]
TO URL (PATH = ‘https://MYSTORAGE.blob.core.windows.net/mycontainer‘,
RETENTION_DAYS = 30);

— Define events to audit
CREATE SERVER AUDIT SPECIFICATION BackupRestoreAuditSpec
FOR SERVER AUDIT [BackupRestoreAudit]
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE=ON);

ALTER SERVER AUDIT [BackupRestoreAudit]
WITH (STATE=ON);

This Audit will add a log record whenever someone backup the database to any blob storage. It will also add a log record if someone with high DB Admin rights disables the audit.

You can find the file path where the audit events are logged using this query:

select name, audit_file_path
from sys.dm_server_audit_status
where name = ‘BackupRestoreAudit' and audit_file_path is not null

Using the audit file path returned by this query (something like ‘https://MYSTORAGE.blob.core.windows.net/mycontainer/*****.xel‘) you can read the audit log events using the following query:

SELECT event_time, succeeded, statement, server_instance_name,
server_principal_name, client_ip, application_name, duration_milliseconds
FROM sys.fn_get_audit_file
(‘https://MYSTORAGE.blob.core.windows.net/mycontainer/*****.xel‘, default, default);

This query will return all audit records written in this audit log file.

Testing the audit

Let's imagine that someone is trying to take an unauthorized backup of the database WideWorldImportersStandardRefresh using the T-SQL code shown below:

BACKUP DATABASE WideWorldImportersStandardRefresh
TO URL = N'https://mibackup.blob.core.windows.net/mycontainer/WWI.bak
WITH COPY_ONLY

If you read the events from the audit log, you will see the following entries:

 

backup-database-audit.PNG

Managed Instance added an audit log entry in the file on blob storage with the event time, T-SQL statement that is executed, login name of the user who executed the statement and client IP address. This way you can track unauthorized backups of the databases.

Make sure to only grant the necessary permissions for your user. In the most of cases, the users who can perform backups and have BACKUP DATABASE permission should not have ALTER ANY SERVER AUDIT permission. Otherwise, they would be able to disable auditing before taking a backup to prevent Managed Instance to write the audit records in the log, and then enable it again once the BACKUP is completed.

If you want to ensure that some high-privileged user who has BACKUP DATABASE+ ALTER ANY SERVER AUDIT or CONTROL SERVER permission cannot do this, the Audit Specification must also include AUDIT_CHANGE_GROUP. In this case you will be able to see who disabled the specification:

 
 

disable-audit.PNG

All audit logs are stored on Azure Blob Storage account that should not be available to any Database Administrator regardless of the privileges. Any other database user would be able to download the audit log files from Azure Blob Storage container, or query them directly via some other Managed Instance or even SQL Server instance. This way, you are able to monitor who is accessing your database and to track who made the potentially malicious actions.

Cleanup

If you created an Audit only for the testing purposes, you could use the following script to remove it:

ALTER SERVER AUDIT SPECIFICATION BackupRestoreAuditSpec
WITH (STATE=OFF);
DROP SERVER AUDIT SPECIFICATION BackupRestoreAuditSpec

ALTER SERVER AUDIT [BackupRestoreAudit]
WITH (STATE=OFF);
DROP SERVER AUDIT [BackupRestoreAudit]

 

This article was originally published by Microsoft's Core Infrastructure and Security Blog. You can find the original article here.