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 an Azure Blob Storage container. This feature can be used for a 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 an unauthorized copy of the database by creating a backup to Azure Blob Storage. Only high privileged users can take the backups of the databases, so make sure that you have given the proper permissions to 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 this way copy 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 do not use TDE on the database or there is a risk that someone can remove TDE from database and then take a backup, you can use the Auditing functionality of SQL Database Managed Instance provides to track who performed a backup and when. Using Auditing, you can always track if an unauthorized action is happening.
In the following sections you will find the scripts that can executed on the Managed Instance to setup Auditing that will track any backup action performed on the Managed Instance.
Setup
The first step you need to do to set up auditing is to create a credential with the name of the Azure Blob Storage where audit log records will be stored, including the SAS key:
DROP CREDENTIAL [https://.blob.core.windows.net/mycontainer]
CREATE CREDENTIAL [https://.blob.core.windows.net/mycontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'PUT SECRET HERE'
This credential will enable Managed Instance to write the audit log records into the file on the Azure Blob Storage container. For more information on how to create SQL Server Audits, see here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-auditing.
Once you have prepared your storage account, you can create the Audit to the Azure Blob Storage container and the Audit specification that will capture Backup and Restore events and Changes to any audit. The following objects are required to setup the auditing:
- Create a Server Audit that will write audit logs in the Azure Blob Storage container.
- Create an Audit Specification that will capture the BACKUP_RESTORE_GROUP and the 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 up to any blob storage account. 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:
Managed Instance added an audit log entry in the file on the Azure 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 a 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.
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]
Microsoft
