Azure SQL Database Managed Instance provides T-SQL surface area functionalities that are very close to the SQL Server functional surface area. One of the SQL Server feature that is available in Managed Instance is taking the COPY_ONLY backups of the databases to the Azure Blob Storage 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 Blob Storage. 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 Encryption that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep your encryption 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.
The first step you need to do to setup auditing is to create a credential to the Azure Blob Storage where audit log records will be stored:
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 Storage container. See how to 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 [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]
ALTER SERVER AUDIT [BackupRestoreAudit]
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:
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:
server_principal_name, client_ip, application_name, duration_milliseconds
(‘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:
TO URL = N’https://mibackup.blob.core.windows.net/mycontainer/WWI.bak‘
If you read the events from the audit log, you will see the following entries:
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.
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:
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.
If you created an Audit only for the testing purposes, you could use the following script to remove it:
DROP SERVER AUDIT SPECIFICATION BackupRestoreAuditSpec
ALTER SERVER AUDIT [BackupRestoreAudit]
DROP SERVER AUDIT [BackupRestoreAudit]