Lesson Learned #490: Monitoring Access from Specific Applications to Azure SQL Database

This week, I worked on a service request where our customer needed to ensure that specific applications could not connect to a particular database. Unfortunately, logon triggers are not supported in Azure SQL Database, but we can use an extended event to capture all logins. Here, I would like to share my experience with you.

First, we will create an extended event session that captures logon events and stores the information in the ring buffer.

CREATE EVENT SESSION [CaptureLogins] ON DATABASE 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.username))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)
GO

Once we have created the Extended Event session, we need to start it so that Azure SQL Database will begin capturing the logins.

ALTER EVENT SESSION [CaptureLogins] ON DATABASE
STATE = START

To retrieve and process the captured events, we will use the following SQL query. This query extracts the data from the ring buffer and places it into a temporary table for analysis.

-- Drop the temporary table if it exists
DROP TABLE IF EXISTS #EventBuffer;

-- Create a temporary table to store the event data
CREATE TABLE #EventBuffer (event_data XML);

-- Insert event data into the temporary table
INSERT INTO #EventBuffer (event_data)
SELECT CAST(target_data AS XML)
FROM sys.dm_xe_database_session_targets AS dst
JOIN sys.dm_xe_database_sessions AS ds ON ds.address = dst.event_session_address
WHERE ds.name = 'CaptureLogins'; -- Extended Event session name

-- Select and parse the event data from the temporary table
SELECT 
    events.event.value('@timestamp', 'datetime') AS EventTimestamp,
    events.event.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS ClientAppName,
    events.event.value('(action[@name="database_name"]/value)[1]', 'nvarchar(max)') AS DatabaseName,
    events.event.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS ClientHostName,
    events.event.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS UserName
FROM #EventBuffer
CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS events(event);

If we want to automate the process to notify if it detects connections from specific applications, we could use the following PowerShell script.

# Configuration
$subject = "Important Access - Results"
$jsonFilePath = "c:\temp\last_execution_date_p.json"
$appNamesToCheck = @("Microsoft SQL Server Management Studio*", "*SqlClient*")

# Retrieve the last execution date from the JSON file
$lastExecutionDate = [DateTime]::ParseExact('01/01/1900', "dd/MM/yyyy", $null)
if (Test-Path $jsonFilePath) {
    $jsonContent = Get-Content $jsonFilePath -Raw | ConvertFrom-Json
    $lastExecutionDate = $jsonContent.LastExecutionDate.value
}

# Convert the last execution date to the appropriate format for the query
$lastExecutionDateString = $lastExecutionDate.ToString("yyyy-MM-dd HH:mm:ss")

# Extended Event Query
$query = @"
DECLARE @LastExecutionDate DATETIME = '$lastExecutionDateString';
-- Drop the temporary table if it exists
DROP TABLE IF EXISTS #EventBuffer;

-- Create a temporary table to store the event data
CREATE TABLE #EventBuffer (event_data XML);

-- Insert event data into the temporary table
INSERT INTO #EventBuffer (event_data)
SELECT CAST(target_data AS XML)
FROM sys.dm_xe_database_session_targets AS dst
JOIN sys.dm_xe_database_sessions AS ds ON ds.address = dst.event_session_address
WHERE ds.name = 'CaptureLogins'; -- Extended Event session name

-- Select and parse the event data from the temporary table
SELECT 
    events.event.value('@timestamp', 'datetime') AS EventTimestamp,
    events.event.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS ClientAppName,
    events.event.value('(action[@name="database_name"]/value)[1]', 'nvarchar(max)') AS DatabaseName,
    events.event.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS ClientHostName,
    events.event.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS UserName
FROM #EventBuffer
CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS events(event)
WHERE events.event.value('@timestamp', 'datetime') >= @LastExecutionDate;
"@

# Database Connection
$connectionString = "Data Source=servername.database.windows.net;Initial Catalog=dbname;User ID=username;Password=pwd!;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataTable = New-Object System.Data.DataTable

$connection.Open()
$adapter.Fill($dataTable)
$connection.Close()

# Check if there are results to send via email
if ($dataTable.Rows.Count -gt 0) 
{
    # Create the email body with the results
    foreach ($row in $dataTable.Rows) 
    {
        $clientAppName = $row["ClientAppName"]
        foreach ($pattern in $appNamesToCheck) 
        { 
            if ($clientAppName -like $pattern) 
            {
                Write-Host "Event Timestamp: $($row["EventTimestamp"])
                            Client App Name: $($row["ClientAppName"])
                            Database Name: $($row["DatabaseName"])
                            Client HostName: $($row["ClientHostName"])
                            UserName: $($row["UserName"])"
            }
        }
    }
}

# Save the current execution date in the JSON file
$lastExecutionDateObject = [PSCustomObject]@{
    LastExecutionDate = (Get-Date)
}
$lastExecutionDateObject | ConvertTo-Json | Set-Content $jsonFilePath

This PowerShell script runs the query, checks if the name of the application is among the array defined in $appNamesToCheck. If it matches, it will show a message. To prevent historical data from being processed again, the execution date will be saved, ensuring that only new information is sent.

You could customized this code based on your needs and test in your preproduction environment before using. Using Extended Events and PowerShell in this manner provides a robust way to monitor and notify about specific application connections to Azure SQL Database. However, it is important to note that this method involves continuous querying of the event data, which may have performance implications depending on the volume of login events and the frequency of the checks. Ensure that this solution is tested thoroughly in a non-production environment before deploying it in a live scenario. Adjust the frequency of the PowerShell script execution as necessary to balance between timely notifications and system performance.

Finally, you can also define the extended event by directly filtering the name, so that only the entry in the ring buffer is generated.

CREATE EVENT SESSION [CaptureLogins2] ON DATABASE 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.username)
    WHERE sqlserver.client_app_name = N'SpecificAppName') -- Replace 'SpecificAppName' with the name of the application you want to filter
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON);
GO