Azure – Backup and Auditing to Blob Storage

Posted on by

–Configure Backup BLOB Storage
— First create the Blob container on a storage account (best to use a new storage account for this)
— https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-auditing
/*
CREATE CREDENTIAL []
WITH IDENTITY=’SHARED ACCESS SIGNATURE’,
SECRET = ”
GO
*/

CREATE CREDENTIAL [https://devsadbcvfdaasdb0003.blob.core.windows.net/auditing]
WITH IDENTITY=’SHARED ACCESS SIGNATURE’,
SECRET = ‘sv=2018-03-28&ss=b&srt=sco&sp=rwdlac&se=2026-02-21T18:26:07Z&st=2019-02-21T10:26:07Z&spr=https&sig=Uyqk2%2FnyxeI6RdsfsdfswibblezdIDCWGk8vnsQvPTlelsROxJG5lb90%3D’
GO

CREATE CREDENTIAL [https://devsadbcvfdaasdb0003.blob.core.windows.net/sqlbackups]
WITH IDENTITY=’SHARED ACCESS SIGNATURE’,
SECRET = ‘sv=2018-03-28&ss=b&srt=sco&sp=rwdlac&se=2026-02-21T18:26:07Z&st=2019-02-21T10:26:07Z&spr=https&sig=Uyqk2%2FnyxeI6RzdfsdfswibbledfdIDCWGk8vnsQvPTlelsROxJG5lb90%3D’
GO

backup database [model] to url=’https://devsawibble0003.blob.core.windows.net/sqlbackups/model2.bak’
with copy_only
go

restore database [model_copy] from url=’https://devsawibble0003.blob.core.windows.net/sqlbackups/model2.bak’
go

–Configure Auditing to BLOB Storage
/*
CREATE CREDENTIAL [https://devsawibble0003.blob.core.windows.net/auditing]
WITH IDENTITY=’SHARED ACCESS SIGNATURE’,
SECRET = ‘sv=2018-03-28&ss=b&srt=sco&sp=rwdlac&se=2026-02-21T18:10:56Z&st=2019-02-21T10:10:56Z&spr=https&sig=yYeFX4Bwqs1kO4xfdsfdswibblefrx900Xl7B7t0E1MtkFkU3jlDCkvM%3D’
GO
/ /
CREATE SERVER AUDIT []
TO URL ( PATH =” [, RETENTION_DAYS = integer ])
GO
*/
use master
go
CREATE SERVER AUDIT [SecAudit]
TO URL ( PATH =’https://devsawibble0003.blob.core.windows.net/auditing’ )
GO

CREATE SERVER AUDIT SPECIFICATION [SecAudit_spec]
FOR SERVER AUDIT [SecAudit]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (TRACE_CHANGE_GROUP)
WITH (STATE = OFF)
GO

— You can also create auditing to Log analytics by following the steps in https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-auditing to turn diagnostics on and select — SQLSecurityAuditEvents with destination being Log Analytics.

CREATE SERVER AUDIT SQL_Audit_Log_Analytics TO EXTERNAL_MONITOR;
GO

CREATE SERVER AUDIT SPECIFICATION [SQL_Audit_Log_Analytics_spec]
FOR SERVER AUDIT [SQL_Audit_Log_Analytics]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (TRACE_CHANGE_GROUP)
WITH (STATE = OFF)
GO

— If audit logs are written to Log Analytics, they are available in the Log Analytics workspace, where you can run advanced searches on the audit data.
— As a starting point, navigate to the Log Analytics and under General section click Logs and enter a simple query, such as: search “SQLSecurityAuditEvents” to view the audit logs.

— Log Analytics gives you real-time operational insights using integrated search and custom dashboards to — readily analyze millions of records across all your workloads and servers. For additional useful information — about Log Analytics search language and commands, see Log Analytics search reference.