Category: Microsoft
Category Archives: Microsoft
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #474:Identifying and Preventing Unauthorized Application Access to Azure SQL Database
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
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.
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.
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:tempLastCheck.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.
Microsoft Tech Community – Latest Blogs –Read More
Validate your skills with our new certification for Microsoft Fabric Analytics Engineers
We’re looking for Microsoft Fabric Analytics Engineers to take our new beta exam. Do you have subject matter expertise in designing, creating, and deploying enterprise-scale data analytics solutions? If so, and if you know how to transform data into reusable analytics assets by using Microsoft Fabric components, such as lakehouses, data warehouses, notebooks, dataflows, data pipelines, semantic models, and reports, be sure to check out this exam. Other helpful qualifications include the ability to implement analytics best practices in Fabric, including version control and deployment.
If this is your skill set, we have a new certification for you. The Microsoft Certified: Fabric Analytics Engineer Associate certification validates your expertise in this area and offers you the opportunity to prove your skills. To earn this certification, pass Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric, currently in beta.
Is this the right certification for you?
This certification could be a great fit if you have in-depth familiarity with the Fabric solution and you have experience with data modeling, data transformation, Git-based source control, exploratory analytics, and languages, including Structured Query Language (SQL), Data Analysis Expressions (DAX), and PySpark.
Review the Exam DP-600 (beta) page for details, and check out the self-paced learning paths and instructor-led training there. The Exam DP-600 study guide alerts you for key topics covered on the exam.
Ready to prove your skills?
Take advantage of the discounted beta exam offer. The first 300 people who take Exam DP-600 (beta) on or before January 25, 2024, can get 80 percent off market price.
To receive the discount, when you register for the exam and are prompted for payment, use code DP600Winfield. This is not a private access code. The seats are offered on a first-come, first-served basis. As noted, you must take the exam on or before January 25, 2024. Please note that this beta exam is not available in Turkey, Pakistan, India, or China.
The rescore process starts on the day an exam goes live—8 to 12 weeks after the beta period, and final scores for beta exams are released approximately 10 days after that. For details on the timing of beta exam rescoring and results, read my post Creating high-quality exams: The path from beta to live.
Get ready to take Exam DP-600 (beta)
Explore the Fabric Career Hub. Access live training, skills challenges, group learning, and career insights.
Join the Fabric Cloud Skills Challenge. Complete all modules in the challenge within 30 days and become eligible for 50% off the cost of a Microsoft Certification exam. This 50% discount can’t be used toward the Exam DP-600 (beta). If you miss the beta period, you can use it later once the exam goes live or for another live certification exam.
Looking for in-depth training? Check out the new course Microsoft Fabric Analytics Engineer. Connect with Microsoft Training Services Partners in your area for in-person training.
Need other preparation ideas? Check out my blog post Just How Does One Prepare for Beta Exams?
Did you know that you can take any role-based exam online? Online delivered exams—taken from your home or office—can be less hassle, less stress, and even less worry than traveling to a test center, especially if you’re adequately prepared for what to expect. To find out more, check out my blog post Online proctored exams: What to expect and how to prepare.
Ready to get started?
Remember, the number of spots for the discounted beta exam offer is limited to the first 300 candidates taking Exam DP-600 (beta) on or before January 25, 2024.
Related announcements
9 ways Microsoft Learn helps you with the skills-first economy
Introducing a new resource for all role-based Microsoft Certification exams
Microsoft Learn: Four key features to help expand your knowledge and advance your career
Meet learners who changed their career with the help of Microsoft Learn
Microsoft Tech Community – Latest Blogs –Read More
Introducing Automatic File and URL (Detonation) Analysis
The Microsoft Defender Threat Intelligence (MDTI) team continuously adds new threat intelligence capabilities to MDTI and Defender XDR, giving customers new ways to hunt, research, and contextualize threats.
Today, we are excited to share a new feature that enhances our file and URL analysis (detonation) capabilities in the threat intelligence blade within the Defender XDR user interface. If MDTI cannot return any results when a customer searches for a file or URL, MDTI now automatically detonates it to improve search coverage and add to our corpus of knowledge of the global threat landscape:
Here’s how it works:
The detonation request for the searched file or URL entity is processed asynchronously in the background in the United States region.
If the end user is not served with a reputation and detonation results at the time of the search request. A subsequent search request for the same entity is initiated in the background.
Although there are no fixed SLAs regarding the volume and availability of the auto-detonated results, we aim to provide the results within 2 hours, depending on the load.
Next time you search and don’t find anything, don’t worry. The system is working in the background to give you better results later!
Next steps
Whether you are just kick-starting a threat intelligence program or looking to augment your existing threat intelligence toolset, the MDTI standard version can add critical context to your existing security investigations, keep your organization informed on current threats through leading research and intel profiles, provide crucial brand intelligence, and help you to collect powerful threat intelligence associated with your organization or others in your industry – all free of charge.
To learn more about how you and your organization can leverage MDTI, watch our overview video and follow our “Become an MDTI Ninja” training path today.
Microsoft Tech Community – Latest Blogs –Read More
December ’23 Monthly M365 Webinar – Microsoft Collaboration Framework
Dan Carroll and Richard Wakeman supported a great discussion around the Microsoft Collaboration Framework and explored the current state of collaboration capabilities across your partner ecosystem.
Recording here: https://www.microsoft.com/en-us/videoplayer/embed/RW1g6Zt
Microsoft Tech Community – Latest Blogs –Read More