In today’s time where data breaches are highly expected to happen, there is a high need to ensure sensitive data in the Azure SQL databases is secured and which is why data security implementation is gaining momentum across each community. We covered one of the features of Advanced Data Security here in Advanced Data Security in Azure SQL Database – Data Discovery & Classification. Let’s dive further into the other two features that Azure SQL offers with this capability in this article.
Vulnerability Assessment
SQL professionals have to incessantly access ever changing and growing databases to check for any potential vulnerability (like SQL injection, misconfigurations etc.) in order to improve database security. There were times when SQL developers had to use external tools or even write customized scripts to discover, monitor and remediate these vulnerabilities, but fortunately, SQL Server came up with a built-in solution named Vulnerability Assessment tool via SSMS a couple of years ago and this feature was made available for Azure SQL Database users as well almost a year ago. Let’s keep the ball rolling and implement Vulnerability Assessment to identify, track and remediate vulnerabilities in Azure SQL Database. Select Azure Database (AdventureWorksDW2017 in our case) and click on Vulnerability Assessment tile present on the Advanced Data Security blade as shown below.
If you don’t see any records on the Vulnerability Assessment report, click on Scan to get the assessment report. You can also use the ‘Export Scan Results’ option to share the complete assessment report results with leads and auditors. An excel file gets ready to be downloaded on clicking this button. Additionally, you can click on Scan History to view all scans happened previously in a database.
Vulnerability Assessment Report
The summary results of the Vulnerability Assessment report show two metrics – Total failing checks vs Total passing checks, along with the categorizations on the Risk (High, Medium and Low risk). It addresses issues row-wise in the report which are actionable. You can click on any result (Failed or Passed) to understand more insights on the security check. This tool provides the best practices that Azure SQL thinks should be implemented on the SQL Databases from the data security perspective. The failed results are self-descriptive and are organized in particular categories as shown above.
Identifying and remediating issues with Vulnerability Assessment
Let’s drill down on the failed result that talks about Firewall rules to see why it failed in the first place and what steps we can take to remediate it in the Azure SQL Database. This drill down report gives the complete description on to what went wrong, in our case, it recommends to set a firewall on the server level to protect our data.
It provides the remediation script that we can run against query editor to resolve this issue, alternatively, this report also provides a remediation link for the quick resolution. This way in the Vulnerability Assessment report, we get clear visibility and actionable remediation steps to manage and resolve issues in the Azure SQL Databases.
Approve as Baseline in Vulnerability Assessment
We can set the selected security check result as an approved baseline by clicking Yes in the screenshot below. Next time the scan runs, this alert is no longer considered as a failure and gets the status as PASS (per custom baseline). In our case, there are just 4 failed results, but in a real scenario, there are failures in large numbers. This allows us to focus more on the relevant issues once baseline security is made on such large assessment results.
Run a new scan to view the customized report with failures being 3 in number and the security check with applied baseline rule is moved to the count of ‘Passed’ metrics.
Vulnerability Assessment in Azure SQL Database is gaining popularity in monitoring databases for a higher level of security. This tool is extremely helpful in discovering, tracking and managing vulnerabilities in the database. This provides in-depth actionable remediation steps for any issue found in the assessment report. Since this tool is intuitive to work with, one doesn’t have to be a security expert to work on this.
Advanced Data Security on Server level
Before we talk about our last yet important security feature, let’s quickly peek at how we can manage these security traits on the server level.
Alternately, these features except Data Discovery & Classification can be configured on the server level too. By configuring them on the server level, these will be enabled for all the SQL databases present in the server. Click the SQL Server name, (sqlshackdemoserver in our case) and go to the Advanced Data Security blade. Toggle the advanced data security switch to ON, and select the subscription in the subscription textbox. Configure the storage account and you can select the frequency of the vulnerability assessment scan, by default, scan happens every Sunday at 12 midnight. So apart from the manual scan showed earlier, we can set up a periodic scan on this. Additionally, you can share the scan summary reports with the email address(s) provided.
Configuring Vulnerability Assessment settings on the Server level
Configuring Advanced Threat Protection on the Server level
You can enable Advanced Threat Protection that comes under Advanced Threat protection settings by providing the Email Address(s) where you want alerts to be sent and can also customize the type of alerts (like SQL injection, Data exfiltration etc.) experienced in the Azure SQL Databases as shown below. Do not forget to hit the Save button to save these changes.
Once these server level configurations are done, you can see the below notification confirming Advanced Data Security is configured on the Server dashboard page.
Let’s move forward and look at the last chunk of the Advanced Data Security feature.
Advanced Threat Protection
Advanced Threat Protection is basically a real-time threat detection service. Not so long ago, this security feature was briefly demonstrated by one of my fellow authors in this article, Security considerations for your Azure SQL Databases. At that time, this feature was under “Auditing & Threat detection” heading and as of today, it is known as “Advanced Threat Protection” in Advanced Data Security blade in Azure SQL Databases. Click on the third tile in the Advanced Data Security blade to see the list of Security Alerts addressed by this feature as shown below.
This feature works continuously in the background to detect or monitor any threats or any suspicious activities happening on the Azure SQL database. As soon as it senses any unusual behavior, it will automatically send an email with in-depth details on the activity, investigation and remediation steps. The best part of this feature is its integration with the Azure Security Center, which is a centralized center responsible for managing all of the Azure security. The reported alerts are viewed from this center.
Advanced Threat Protection demo – Brute force login attack
Since I have created a new server and a couple of databases in it for the demo purpose here, this feature hasn’t accounted for any potential security threats as of now. I am going to stimulate a brute force login- attack on one of my databases by continuously attempting to login with invalid passwords. As we have configured the email address above in the Advanced Protection settings on the server level, I receive the below email notifying the potential threat being acknowledged by this feature.
Information on this security alert like database name, server name, severity etc. is provided as shown below.
Additionally, it helps with the investigation and remediation steps as well to mitigate this anomalous activity.
Click on the View recent alerts link in the email received to navigate to Azure portal and locate these details in the centralized dashboard view – Azure Security Center alerts page. Below is the overview of the detected threats in the SQL Database, fortunately, we just have one.
We can drill down the specific alert – SQL brute force attack by clicking on it. Select View suspicious activity to gain more insights on where and what has gone wrong. It also offers to run relevant queries in the query editor to understand what all transactions took place in this event in the database.
Click on the Remediation steps to mitigate this alert. For this alert, this feature not only recommends to turn on the firewall settings but also assists user in making the required changes.
Apart from the brute force SQL credentials attack seen above, I would like to highlight other most commonly occurring potential security threats or anomalous activities below here:
- SQL injection – This attack is basically an attempt to insert specially coded sql commands into a data field, to try to cause the database to run the command. Usually, this happens when data is passed into a SQL statement without being checked. for eg – select * from dimcustomer where columname = ‘drop table tablename’
- Login anomalies– This is the case when someone is trying to log in to the Azure SQL Database from a strange location or the unusual location that no one has used before. This will cause a detection alert
- Potential vulnerabilities like database misconfigurations, managing sensitive data etc.
To learn more on Advanced Threat Protection, read over here.
Conclusion
We covered 3 main pillars of Advanced Data Security available with Azure SQL Database. It is evident that how easy it is implementing these security features with just a few clicks. It also provides great visibility into our SQL databases thus strengthening the ‘Azure Security structure’. I hope this series will encourage readers to keep their data more safe and secure.
Table of contents
| Advanced Data Security in Azure SQL Database – Data Discovery & Classification |
| Vulnerability Assessment and Advanced Threat Protection in Azure SQL Database |
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023