Category: Microsoft
Category Archives: Microsoft
Future-Proofing AI: Strategies for Effective Model Upgrades in Azure OpenAI
TL;DR: This post navigates the intricate world of AI model upgrades, with a spotlight on Azure OpenAI’s embedding models like text-embedding-ada-002. We emphasize the critical importance of consistent model versioning ensuring accuracy and validity in AI applications. The post also addresses the challenges and strategies essential for effectively managing model upgrades, focusing on compatibility and performance testing.
Introduction
What are Embeddings?
Embeddings in machine learning are more than just data transformations. They are the cornerstone of how AI interprets the nuances of language, context, and semantics. By converting text into numerical vectors, embeddings allow AI models to measure similarities and differences in meaning, paving the way for advanced applications in various fields.
Importance of Embeddings
In the complex world of data science and machine learning, embeddings are crucial for handling intricate data types like natural language and images. They transform these data into structured, vectorized forms, making them more manageable for computational analysis. This transformation isn’t just about simplifying data; it’s about retaining and emphasizing the essential features and relationships in the original data, which are vital for precise analysis and decision-making.
Embeddings significantly enhance data processing efficiency. They allow algorithms to swiftly navigate through large datasets, identifying patterns and nuances that are difficult to detect in raw data. This is particularly transformative in natural language processing, where comprehending context, sentiment, and semantic meaning is complex. By streamlining these tasks, embeddings enable deeper, more sophisticated analysis, thus boosting the effectiveness of machine learning models.
Implications of Model Version Mismatches in Embeddings
Lets discuss the potential impacts and challenges that arise when different versions of embedding models are used within the same domain, specifically focusing on Azure OpenAI embeddings. When embeddings generated by one version of a model are applied or compared with data processed by a different version, various issues can arise. These issues are not only technical but also have practical implications on the efficiency, accuracy, and overall performance of AI-driven applications.
Compatibility and Consistency Issues
Vector Space Misalignment: Different versions of embedding models might organize their vector spaces differently. This misalignment can lead to inaccurate comparisons or analyses when embeddings from different model versions are used together.
Semantic Drift: Over time, models might be trained on new data or with updated techniques, causing shifts in how they interpret and represent language (semantic drift). This drift can cause inconsistencies when integrating new embeddings with those generated by older versions.
Impact on Performance
Reduced Accuracy: Inaccuracies in semantic understanding or context interpretation can occur when different model versions process the same text, leading to reduced accuracy in tasks like search, recommendation, or sentiment analysis.
Inefficiency in Data Processing: Mismatches in model versions can require additional computational resources to reconcile or adjust the differing embeddings, leading to inefficiencies in data processing and increased operational costs.
Best Practices for Upgrading Embedding Models
Upgrading Embedding – Overview
Now lets move to the process of upgrading an embedding model, focusing on the steps you should take before making a change, important questions to consider, and key areas for testing.
Pre-Upgrade Considerations
Assessing the Need for Upgrade:
Why is the upgrade necessary?
What specific improvements or new features does the new model version offer?
How will these changes impact the current system or process?
Understanding Model Changes:
What are the major differences between the current and new model versions?
How might these differences affect data processing and results?
Data Backup and Version Control:
Ensure that current data and model versions are backed up.
Implement version control to maintain a record of changes.
Questions to Ask Before Upgrading
Compatibility with Existing Systems:
Is the new model version compatible with existing data formats and infrastructure?
What adjustments, if any, will be needed to integrate the new model?
Cost-Benefit Analysis:
What are the anticipated costs (monetary, time, resources) of the upgrade?
How do these costs compare to the expected benefits?
Long-Term Support and Updates:
Does the new model version have a roadmap for future updates and support?
How will these future changes impact the system?
Key Areas for Testing
Performance Testing:
Test the new model version for performance improvements or regressions.
Compare accuracy, speed, and resource usage against the current version.
Compatibility Testing:
Ensure that the new model works seamlessly with existing data and systems.
Test for any integration issues or data format mismatches.
Fallback Strategies:
Develop and test fallback strategies in case the new model does not perform as expected.
Ensure the ability to revert to the previous model version if necessary.
Post-Upgrade Best Practices
Monitoring and Evaluation:
Continuously monitor the system’s performance post-upgrade.
Evaluate whether the upgrade meets the anticipated goals and objectives.
Feedback Loop:
Establish a feedback loop to collect user and system performance data.
Use this data to make informed decisions about future upgrades or changes.
Upgrading Embedding – Conclusion
Upgrading an embedding model involves careful consideration, planning, and testing. By following these guidelines, customers can ensure a smooth transition to the new model version, minimizing potential risks and maximizing the benefits of the upgrade.
Use Cases in Azure OpenAI and Beyond
Embedding can significantly enhance the performance of various AI applications by enabling more efficient data handling and processing. Here’s a list of use cases where embeddings can be effectively utilized:
Enhanced Document Retrieval and Analysis: By first performing embeddings on paragraphs or sections of documents, you can store these vector representations in a vector database. This allows for rapid retrieval of semantically similar sections, streamlining the process of analyzing large volumes of text. When integrated with models like GPT, this method can reduce the computational load and improve the efficiency of generating relevant responses or insights.
Semantic Search in Large Datasets: Embeddings can transform vast datasets into searchable vector spaces. In applications like eCommerce or content platforms, this can significantly improve search functionality, allowing users to find products or content based not just on keywords, but on the underlying semantic meaning of their queries.
Recommendation Systems: In recommendation engines, embeddings can be used to understand user preferences and content characteristics. By embedding user profiles and product or content descriptions, systems can more accurately match users with recommendations that are relevant to their interests and past behavior.
Sentiment Analysis and Customer Feedback Interpretation: Embeddings can process customer reviews or feedback by capturing the sentiment and nuanced meanings within the text. This provides businesses with deeper insights into customer sentiment, enabling them to tailor their services or products more effectively.
Language Translation and Localization: Embeddings can enhance machine translation services by understanding the context and nuances of different languages. This is particularly useful in translating idiomatic expressions or culturally specific references, thereby improving the accuracy and relevancy of translations.
Automated Content Moderation: By using embeddings to understand the context and nuance of user-generated content, AI models can more effectively identify and filter out inappropriate or harmful content, maintaining a safe and positive environment on digital platforms.
Personalized Chatbots and Virtual Assistants: Embeddings can be used to improve the understanding of user queries by virtual assistants or chatbots, leading to more accurate and contextually appropriate responses, thus enhancing user experience. With similar logic they could help route natural language to specific APIs. See CompactVectorSearch repository, as an example.
Predictive Analytics in Healthcare: In healthcare data analysis, embeddings can help in interpreting patient data, medical notes, and research papers to predict trends, treatment outcomes, and patient needs more accurately.
In all these use cases, the key advantage of using embeddings is their ability to process and interpret large and complex datasets more efficiently. This not only improves the performance of AI applications but also reduces the computational resources required, especially for high-cost models like GPT. This approach can lead to significant improvements in both the effectiveness and efficiency of AI-driven systems.
Specific Considerations for Azure OpenAI
Model Update Frequency: Understanding how frequently Azure OpenAI updates its models and the nature of these updates (e.g., major vs. minor changes) is crucial.
Backward Compatibility: Assessing whether newer versions of Azure OpenAI’s embedding models maintain backward compatibility with previous versions is key to managing version mismatches.
Version-Specific Features: Identifying features or improvements specific to certain versions of the model helps in understanding the potential impact of using mixed-version embeddings.
Strategies for Mitigation
Version Control in Data Storage: Implementing strict version control for stored embeddings ensures that data remains consistent and compatible with the model version used for its generation.
Compatibility Layers: Developing compatibility layers or conversion tools to adapt older embeddings to newer model formats can help mitigate the effects of version differences.
Baseline Tests: Create few simple baseline tests, that would identify any drift of the embeddings.
Azure OpenAI Model Versioning: Understanding the Process
Azure OpenAI provides a systematic approach to model versioning, applicable to models like text-embedding-ada-002:
Regular Model Releases:
New models are released periodically with improvements and new features.
More on model releases.
Version Update Policies:
Options for auto-updating to new versions or deploying specific versions.
Customizable update policies for flexibility.
Details on update options.
Notifications and Version Maintenance:
Advance notifications for new default versions.
Previous major versions maintained until retirement.
Information on version notifications.
Upgrade Preparation:
Recommendations to read the latest documentation and test applications with new versions.
Importance of updating code and configurations for new features.
Preparing for version upgrades.
Conclusion
Model version mismatches in embeddings, particularly in the context of Azure OpenAI, pose significant challenges that can impact the effectiveness of AI applications. Understanding these challenges and implementing strategies to mitigate their effects is crucial for maintaining the integrity and efficiency of AI-driven systems.
References
“Learn about Azure OpenAI Model Version Upgrades.” Microsoft Tech Community. Link
“OpenAI Unveils New Embedding Model.” InfoQ. Link
“Word2Vec Explained.” Guru99. Link
“GloVe: Global Vectors for Word Representation.” Stanford NLP. Link
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #475:Cross-Database Queries Read and Write in Azure SQL Database.
This week, we addressed a service request from a customer who wanted to keep two tables synchronized across different databases on the same Azure SQL Database server. Unfortunately, in Azure SQL Database, it’s not possible to directly call the database in the operation, for instance using the command select * from master.sys.sys_databases. For this reason, I’d like to share an alternative to tackle this limitation.
Sync data across two databases:
To synchronize data across two databases, we have two databases named “Source” and “Target.” Both of these databases contain a table called LocalTable with the following structure:
CREATE TABLE LocalTable (ID BIGINT PRIMARY KEY, NAME VARCHAR(200))
In Source database we are going to run the following script to update in Target database. The first thing we are going to create the external data source:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MyPwd!’;
CREATE DATABASE SCOPED CREDENTIAL ElasticQueryCred
WITH IDENTITY = ‘userName’,
SECRET = ‘pwd!’;
CREATE EXTERNAL DATA SOURCE MyElasticQueryDataTgt WITH
(TYPE = RDBMS,
LOCATION = ‘servername.database.windows.net’,
DATABASE_NAME = ‘target’, — remote database
CREDENTIAL = ElasticQueryCred,
);
Next we will a store procedure that will receive the parameters and call sp_execute_remote to perform updates in the target database.
CREATE PROCEDURE SyncOtherTable(@ID BIGINT, @Name Varchar(200))
AS
BEGIN
BEGIN TRANSACTION
IF EXISTS( SELECT TOP 1 ID FROM LocalTable WHERE ID = @ID)
BEGIN
UPDATE LocalTable SET Name = @Name WHERE ID = @ID
END
ELSE
BEGIN
INSERT INTO LocalTable (ID,NAME) VALUES(@ID,@Name)
END
EXEC sp_execute_remote
@data_source = N’MyElasticQueryDataTgt’,
@stmt = N’EXEC SyncOtherTable , @Names’,
@params = N’@IDs BIGINT, @Names VARCHAR(200)’,
= @ID,
@Names = @Name;
COMMIT TRANSACTION
END
In the target database we will create the store procedure:
create PROCEDURE SyncOtherTable(@ID BIGINT, @Name Varchar(200))
AS
BEGIN
BEGIN TRANSACTION
IF EXISTS( SELECT TOP 1 ID FROM LocalTable WHERE ID = @ID)
BEGIN
UPDATE LocalTable SET Name = @Name WHERE ID = @ID
END
ELSE
BEGIN
INSERT INTO LocalTable (ID,NAME) VALUES(@ID,@Name)
END
COMMIT TRANSACTION
END
At this point, to synchronize the data in the Target database, we need to run the following T-SQL command in the Source database:
EXEC SyncOtherTable 3,’Demo’
Read data across two databases:
To read data across two databases in a similar situation, consider an example where we have a stored procedure called GetDataFromLocalTableTarget defined in the Source database. The definition of this stored procedure is as follows:
CREATE PROCEDURE GetDataFromLocalTableTarget
@ID BIGINT,
@NameOut VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @TempTable TABLE (Name VARCHAR(200), [$ShardName] VARCHAR(200));
INSERT INTO @TempTable
EXEC sp_execute_remote
@data_source = N’MyElasticQueryDataTgt’,
@stmt = N’SELECT Name FROM LocalTable WHERE ID = ‘,
@params = N’@IDs BIGINT’,
= @ID;
— Selecciona el nombre desde la tabla temporal y lo asigna a la variable de salida
SELECT TOP 1 @NameOut = Name FROM @TempTable;
END;
Basically, we need to call this store procedure to obtain the data from the target database.
DECLARE @MyName VARCHAR(200);
EXEC GetDataFromLocalTableTarget @ID = 2, @NameOut = @MyName OUTPUT;
SELECT @MyName AS ResultName;
Other option is create another procedure sending the External Data Source, statement and parameters.
CREATE PROCEDURE GetDataFromLocalTableTargetDynamic
@ID BIGINT,
@stmt NVARCHAR(MAX),
@params NVARCHAR(MAX),
@DataSource NVARCHAR(MAX),
@NameOut VARCHAR(200) OUTPUT
AS
BEGIN
DECLARE @TempTable TABLE (Name VARCHAR(200), [$ShardName] VARCHAR(200));
INSERT INTO @TempTable
EXEC sp_execute_remote
@data_source = @DataSource,
@stmt = @stmt,
@params = @params,
= @ID;
— Selecciona el nombre desde la tabla temporal y lo asigna a la variable de salida
SELECT TOP 1 @NameOut = Name FROM @TempTable;
END;
and call it
DECLARE @MyName VARCHAR(200);
EXEC GetDataFromLocalTableTargetDynamic @ID = 2,
@stmt = N’SELECT Name FROM LocalTable WHERE ID = ‘,
@params = N’@IDs BIGINT’,
@DataSource = N’MyElasticQueryDataTgt’,
@NameOut = @MyName OUTPUT;
SELECT @MyName AS ResultName;
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
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