HANDLE AZURE SQL AUDITING
WITH EASE
Josephine Bush
She/Hers
DBA, MBA, Author
ALPINE SKI HOUSE
ABOUT ME
Josephine Bush
10+ years DBA
experience
MBA IT Management
MS Data Analytics @hellosqlkitty
sqlkitty.com ALPINE SKI HOUSE 2
WHAT IS AUDITING?
Collecting and examining
information to determine
proper use or misuse
ALPINE SKI HOUSE 3
WHY AUDIT?
Maybe your
company says they
don’t value knowing
what’s going on in
your databases,
but….
ALPINE SKI HOUSE 4
PROBLEMS AUDITING CAN SOLVE
Who broke this?
Who changed this?
Who used this?
You can audit pretty much
everything anyone does in
SQL Server!
ALPINE SKI HOUSE 5
CLOUD SQL AUDITING OPTIONS
Cloud solution SQL Server Audit Extended Events Available Auditing differences
Available
Azure SQL No Yes SQL Server audit quasi equivalent
via Azure portal
Azure SQL Managed Yes Yes Need to use cloud storage
Instance
SQL Server VM Yes Yes Uses disk storage
Amazon Web Services Yes Yes Need to use cloud storage
RDS
ALPINE SKI HOUSE 6
AZURE SQL AUDITING
Audit at server and database level
via the portal
Use these to see queries run by
users on Azure SQL
ALPINE SKI HOUSE 7
AZURE SQL AUDITING POLICY
Audits all queries and stored procedures executed
against the database, and all successful and failed
logins
Using these audit actions:
BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
ALPINE SKI HOUSE 8
MODIFY AZURE SQL AUDITING POLICY
Allows you to audit fewer actions and filter those
actions using Azure PowerShell
Set-AZSqlServerAudit to modify server auditing
policy
Get-AZSqlServerAudit to see current server
auditing policy
ALPINE SKI HOUSE 9
GET AZURE SQL AUDITING POLICY
To get your current auditing policy:
Get-AzSqlServerAudit -ResourceGroupName
'dbops' -Servername 'jbauditing'
ALPINE SKI HOUSE10
AZURE SQL AUDIT ACTION GROUPS
If you are used to SQL
Server Audit, some of
these audit action
groups are the same
and some are not
ALPINE SKI HOUSE11
SET AZURE SQL AUDITING POLICY
To change your current auditing policy:
Set-AzSqlServerAudit -ResourceGroupName 'dbops' -ServerName 'auditingtest' `
-AuditActionGroup APPLICATION_ROLE_CHANGE_PASSWORD_GROUP,
DATABASE_CHANGE_GROUP, `
DATABASE_OBJECT_CHANGE_GROUP, DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP, `
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, DATABASE_OWNERSHIP_CHANGE_GROUP, `
DATABASE_PERMISSION_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, `
DATABASE_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, `
SCHEMA_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP, `
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, USER_CHANGE_PASSWORD_GROUP
ALPINE SKI HOUSE12
ENABLING AZURE SQL AUDITING
ALPINE SKI HOUSE 13
AZURE SQL AUDITING OPTIONS
ALPINE SKI HOUSE14
AZURE SQL AUDITING STORAGE
ALPINE SKI HOUSE15
AZURE SQL AUDITING STORAGE FILES
ALPINE SKI HOUSE16
AZURE SQL AUDITING EVENT HUB
ALPINE SKI HOUSE17
AZURE SQL AUDITING EVENT HUB DATA
ALPINE SKI HOUSE18
AZURE SQL AUDITING LOG ANALYTICS
ALPINE SKI HOUSE19
AZURE LOG ANALYTICS PRICING
Ingesting data
Retaining data
ALPINE SKI HOUSE20
AZURE LOG ANALYTICS COSTS
ALPINE SKI HOUSE21
AZURE LOG ANALYTICS RETENTION
ALPINE SKI HOUSE22
VIEW LOG ANALYTICS AUDIT DATA
View audit data at the database level 2
1
3
ALPINE SKI HOUSE 23
VIEW LOG ANALYTICS WORKSPACE
View audit data in workspace summary
ALPINE SKI HOUSE 24
QUERY LOG ANALYTICS AUDITING DATA
Go to your log analytics workspace AzureDiagnostics
Click Logs and run a Kusto query | where Category == 'SQLSecurityAuditEvents'
and TimeGenerated > ago(1d)
| project
event_time_t,
database_name_s,
statement_s,
server_principal_name_s,
You may need to filter on this if succeeded_s,
you are seeing a lot of entries for client_ip_s,
application_name_s,
this user: additional_information_s,
and server_principal_name_s != data_sensitivity_information_s
'NT AUTHORITY\\SYSTEM' | order by event_time_t desc
ALPINE SKI HOUSE 25
VIEW LOG ANALYTICS AUDITING DATA
ALPINE SKI HOUSE 26
ENABLING AZURE SQL AUDITING
Enabling at database level
instead of server level to
audit only one database
Don’t do this if you already
enabled at server level
ALPINE SKI HOUSE 27
AZURE SQL AUDITING DEMO
ALPINE SKI HOUSE
CENTRALIZING AUDITING DATA
Store Azure SQL audit data in the same log analytics workspace
ALPINE SKI HOUSE 29
REPORTING ON AUDITING DATA
ALPINE SKI HOUSE 30
SETUP LOGIC APP - RECURRENCE
Setup a schedule with Recurrence step
ALPINE SKI HOUSE 31
SETUP LOGIC APP –KUSTO QUERY
Setup a Run query and list
results step with your
kusto query
ALPINE SKI HOUSE 32
SETUP LOGIC APP – CSV FILE
Create CSV table step to create a CSV file attachment
ALPINE SKI HOUSE 33
SETUP LOGIC APP – SEND EMAIL
Setup an Outlook Send an
Email step to send an
email with the CSV
attachment
ALPINE SKI HOUSE 34
AZURE SQL AUDITING EXTENDED EVENTS
Script GUI
CREATE EVENT SESSION [audit] ON DATABASE
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostna
me,sqlserver.database_name,sqlserver.sql_text,sqlserver.
username)
WHERE ([sqlserver].[username]=N'josephine')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostna
me,sqlserver.database_name,sqlserver.sql_text,sqlserver.
username)
WHERE ([sqlserver].[username]=N'josephine'))
ADD TARGET package0.event_file(SET
filename=N'https://StorageAccount.blob.core.windows.net/ You need a credential
Container/audit.xel')
WITH (STARTUP_STATE=ON)
setup to use the URL to
the storage account in
ALPINE SKI HOUSE
the filename 35
MANAGED INSTANCE AUDITING
SQL Server Audit Extended Events
Need a storage Or use
account for all diagnostic
these options settings
and a with Log
credential to Analytics
read/write to workspace
this storage
URL
ALPINE SKI HOUSE 36
DIAGNOSTIC SETTINGS
Diagnostic setting to store audit events in a Log Analytics
workspace
ALPINE SKI HOUSE 37
USING DIAGNOSTIC SETTING
Create SQL Server Audit Query SQL Server Audit
with diagnostic setting To access the audit data, you will
USE [master]; need to go to the Log Analytics
CREATE SERVER AUDIT [miaudit] TO workspace you chose in your
EXTERNAL_MONITOR; diagnostic setting
ALTER SERVER AUDIT [miaudit] WITH
(STATE = ON);
Setup server and/or database
audit to use the server audit
ALPINE SKI HOUSE
AWS RDS AUDITING SETUP
Required components
S3 bucket – To store audit files
Option group – To allow RDS SQL Server to use audit functionality.
This also determines which S3 bucket and IAM role to use.
IAM role – This will allow your RDS instance to access your S3
bucket
SQL Server Audit and Server or Database Audit – To audit actions
on SQL Server
ALPINE SKI HOUSE 39
AWS RDS SQL AUDIT SETUP
The audit has to write to this path: D:\rdsdbdata
Don’t use MAX_FILES at all. Don’t configure SQL Server
to shut down the DB
USE [master]; instance if it fails to write
CREATE SERVER AUDIT [AuditSpecification]
the audit record
TO FILE (
FILEPATH = N'D:\rdsdbdata\SQLAudit\',
MAXSIZE = 10 MB, /* must be between 2 MB and 50 MB */
MAX_ROLLOVER_FILES = 2147483647, /* don’t change this setting */
RESERVE_DISK_SPACE = OFF )
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
WHERE ([database_name]<>'rdsadmin');
ALPINE SKI HOUSE 40
AWS RDS QUERY SQL AUDIT
SELECT DISTINCT event_time, aa.name as audit_action, statement,
succeeded, database_name, server_instance_name, schema_name,
session_server_principal_name, server_principal_name,
object_Name, file_name, client_ip, application_name, file_name
FROM msdb.dbo.rds_fn_get_audit_file ('D:\rdsdbdata\SQLAudit\*.
sqlaudit',default,default) af
INNER JOIN sys.dm_audit_actions aa ON aa.action_id = af.action_id
WHERE event_time > DATEADD(HOUR, -1, GETDATE())
ORDER BY event_time DESC;
ALPINE SKI HOUSE 41
AWS RDS XEVENTS
You have to put your extended events in the path
D:\rdsdbdata\Log\
Otherwise, setup and query is like SQL Server on a VM
ALPINE SKI HOUSE 42
RESOURCES
Azure SQL Audit Overview AWS RDS Auditing
https://docs.microsoft.com/en-us/azure/azure- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuid
sql/database/auditing-overview
e/Appendix.SQLServer.Options.Audit.html
Azure SQL Audit Modify Auditing Policy
https://docs.microsoft.com/en-us/azure/azure- https://aws.amazon.com/blogs/database/set-up-extended-
sql/database/auditing-overview#manage-auditing events-in-amazon-rds-for-sql-server/
Kusto Query Language (KQL) Azure SQL Managed Instance Auditing Setup
https://docs.microsoft.com/en-us/azure/data- https://docs.microsoft.com/en-us/azure/azure-
explorer/kusto/query/ sql/managed-instance/auditing-configure
Create Azure Logic Apps in the Azure portal
Azure SQL Extended Events
https://docs.microsoft.com/en-us/azure/logic-
apps/quickstart-create-first-logic-app-workflow https://docs.microsoft.com/en-us/azure/azure-
sql/database/xevent-db-diff-from-svr
Get started with log queries in Azure Monitor
https://docs.microsoft.com/en-us/azure/azure- Azure SQL Create or Update Database Auditing
monitor/logs/get-started-queries Policy
Log analytics pricing https://docs.microsoft.com/en-us/azure/azure-
https://azure.microsoft.com/en- sql/database/auditing-overview#using-azure-powershell
us/pricing/details/monitor/#pricing ALPINE SKI HOUSE 43
Session evaluation
Your feedback is important to us
Evaluate this session at:
www.PASSDataCommunitySummit.com/evaluation
Thank you
Thank you for taking part in the
PASS Data Community Summit 2022.
Any questions?
Please email
[email protected]Josephine
@hellosqlkitty / sqlkitty.com
