Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database

[Jose_Manuel_Jurado]

In recent scenarios encountered with our customers, we have come across a specific need: restricting certain users from using SQL Server Management Studio (SSMS) or other applications to connect to a designated database in Azure SQL Database. A common solution in traditional SQL Server environments, like the use of LOGIN TRIGGERS, is not available in Azure SQL Database. This limitation poses a unique challenge in database management and security.

To address this challenge, I'd like to share an alternative that combines the power of Extended Events in Azure SQL Database with PowerShell scripting. This method effectively captures and monitors login events, providing administrators with timely alerts whenever a specified user connects to the database using a prohibited application, such as SSMS.

How It Works

1. Extended Events Setup: We start by setting up an Extended Event in Azure SQL Database. This event is configured to capture login activities, specifically focusing on the application name used for the connection. By filtering for certain applications (like SSMS), we can track unauthorized access attempts.

2. PowerShell Script: A PowerShell script is then employed to query these captured events at regular intervals. This script connects to the Azure SQL Database, retrieves the relevant event data, and checks for any instances where the specified users have connected via the restricted applications.

3. Email Alerts: Upon detecting such an event, the PowerShell script automatically sends an email notification to the database administrator. This alert contains details of the unauthorized login attempt, such as the timestamp, username, and application used. This prompt information allows the administrator to take immediate corrective measures.

Advantages

• Proactive Monitoring: This approach provides continuous monitoring of the database connections, ensuring that any unauthorized access is quickly detected and reported.

• Customizable: The method is highly customizable. Administrators can specify which applications to monitor and can easily adjust the script to cater to different user groups or connection parameters.

• No Direct Blocking: While this method does not directly block the connection, it provides immediate alerts, enabling administrators to react swiftly to enforce compliance and security protocols.

This article provides a high-level overview of how to implement this solution. For detailed steps and script examples, administrators are encouraged to tailor the approach to their specific environment and requirements.

Extended Event

CREATE EVENT SESSION Track_SSMS_Logins
ON DATABASE
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username, sqlserver.session_id)
    WHERE (sqlserver.client_app_name LIKE '%Management Studio%')
)
ADD TARGET package0.ring_buffer
(SET max_events_limit = 1000, max_memory = 4096)
WITH (EVENT_RETENTION_MODE = NO_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS);
GO

ALTER EVENT SESSION Track_SSMS_Logins ON DATABASE STATE = START;

Query to run using ring buffers

	 SELECT 
    n.value('(@timestamp)[1]', 'datetime2') AS TimeStamp,
    n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Application,
    n.value('(action[@name="username"]/value)[1]', 'varchar(max)') AS Username,
    n.value('(action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS HostName,
    n.value('(action[@name="session_id"]/value)[1]', 'int') AS SessionID
FROM 
    (SELECT CAST(target_data AS xml) AS event_data
     FROM sys.dm_xe_database_session_targets
     WHERE event_session_address = 
         (SELECT address FROM sys.dm_xe_database_sessions WHERE name = 'Track_SSMS_Logins')
     AND target_name = 'ring_buffer') AS tab
     CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS q(n);

Powershell Script

# Connection configuration
$Database = "DBNAme"
$Server = "Servername.database.windows.net"
$Username = "username"
$Password = "pwd!"

$emailFrom = "EmailFrom@ZYX.com"
$emailTo = "EmailTo@XYZ.com"
$smtpServer = "smtpservername"
$smtpUsername = "smtpusername"
$smtpPassword = "smtppassword"
$smtpPort=25


$ConnectionString = "Server=$Server;Database=$Database;User Id=$Username;Password=$Password;"

# Last check date
$LastCheckFile = "c:\temp\LastCheck.txt"
$LastCheck = Get-Content $LastCheckFile -ErrorAction SilentlyContinue
if (!$LastCheck) {
    $LastCheck = [DateTime]::MinValue
}


# SQL query
$Query = @"
SELECT 
    n.value('(@timestamp)[1]', 'datetime2') AS TimeStamp,
    n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Application,
    n.value('(action[@name="username"]/value)[1]', 'varchar(max)') AS Username,
    n.value('(action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS HostName,
    n.value('(action[@name="session_id"]/value)[1]', 'int') AS SessionID
FROM 
    (SELECT CAST(target_data AS xml) AS event_data
     FROM sys.dm_xe_database_session_targets
     WHERE event_session_address = 
         (SELECT address FROM sys.dm_xe_database_sessions WHERE name = 'Track_SSMS_Logins')
     AND target_name = 'ring_buffer') AS tab
     CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS q(n)
WHERE 
    n.value('(@timestamp)[1]', 'datetime2') > '$LastCheck'
"@

# Create and open SQL connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()

# Create SQL command
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $Query

# Execute SQL command
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

# Process the results
$Results = $DataSet.Tables[0]

# Check for new events
if ($Results.Rows.Count -gt 0) {
    # Prepare email content
    $EmailBody = $Results | Out-String
    $smtp = New-Object Net.Mail.SmtpClient($smtpServer, $smtpPort)
    $smtp.EnableSsl = $true
    $smtp.Credentials = New-Object System.Net.NetworkCredential($smtpUsername, $smtpPassword)
    $mailMessage = New-Object Net.Mail.MailMessage($emailFrom, $emailTo)
    $mailMessage.Subject = "Alert: SQL Access in database $Database"
    $mailMessage.Body = "SQL Access Alert in database $Database on server $Server at $LastCheck."
    $smtp.Send($EmailBody)

    # Save the current timestamp for the next check
    Get-Date -Format "o" | Out-File $LastCheckFile
}

# Remember to schedule this script to run every 5 minutes using Windows Task Scheduler

Of course, that using SQL auditing o Log analytics will be another alternative.