SQLMIauditingforLinkedServer.md

Configure Server Auditing for Azure SQL Managed Instance to capture Linked Server activity

Auditing is a very important part of security configuration on any Database service and it becomes more critical in a cloud environment. Azure SQL Managed Instance Server audit can be written to Azure Storage or Log analytics workspaces. In this setup we will be showcasing Log analytics workspace so that we can use the native functionality to easily alert and notify teams when any suspcious activities happen related to Linked server execution. Here is the direct link to the T-SQL script to enable auditing on a SQL Managed Instance

Step 1: To enable Diagnostic Logging at Azure SQL Managed Instance level.

Enable Diagnostic Settings under the Monitoring category under Azure SQL Managed Instance properties. Make sure you select the SQLSecurityAuditEvents under logs. For the Destination details, please click Send to Log Analytics and select the right Log analytics workspace where you want to direct your Audit logs. Finally Click Save button

Step 2: To configure Auditing at Azure SQL Managed Instance level

On the SQL MI instance, Log on with the Privileged Admin rights, Enable the SQL MI Instance for Server auditing and select the location as EXTERNAL_MONITOR to direct your logs to Log Analytics workspace instead of an Azure storage location which is the default. Once you run this code, the next step is to create to Server Audit specification

CREATE SERVER AUDIT [LinkedServer] TO EXTERNAL_MONITOR;
GO

Step 3: To create the Server Audit specification

Create a server audit specification to capture all the Linked server activity on the SQL instance and anything to do with the existing audit as well. In this way, no one can tamper with the audit before they create a linked server

CREATE DATABASE AUDIT SPECIFICATION [LinkedServerMaster]
FOR SERVER AUDIT [LinkedServer]
ADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_addserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_dropserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedsrvlogin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_droplinkedsrvlogin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_serveroption] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_setnetname] BY [dbo])
WITH (STATE = ON);
GO

Step 4: To enable the server audit

Enable the server audit created in the earlier step. This is the final step and Auditing has been enabled for your Azure SQL Managed Instance.

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

Monitoring the Audit logs

All the logs show up in the Log Analytics workspace. Click on the Logs option under General Category to open the Kusto Query explorer where in you can write your Kusto queries to explore your data and then create alerts accordingly

This query will show all the logs captured by SQL Auditing option which we have enabled on the Azure SQL Managed instance in the earlier steps

AzureDiagnostics | where Category == "SQLSecurityAuditEvents" 

Create Alerts to notify on any suspicious activities

Here are steps to create Alerts based on a Custom log query. You can customize this accordingly to your SLA requirements and create an Action group to notify when certain thresholds are hit. Click on the New Alert option on the top of the query window.

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and action_name_s =="EXECUTE"  and object_name_s =="sp_addlinkedserver" | count

Create an action group to get notified on the alert. This could be an email, SMS, Webhook or Logic apps etc.

Finally, provide an Alert name and Create the Alert rule

Test the Alert

Run the following script to enable "clr enabled" or "Database Mail XPs" on the Azure SQL Managed instance

sp_addlinkedserver'test'

After sometime, you will get an email or text message with the information as an Alert.