Security is the foundation for building successful solutions in Microsoft Azure. Not only has Microsoft invested over $1 Billion in this area but they also implement a multi-layered security strategy. This mindset from Microsoft means highly secure products and features, especially for Azure SQL Database. Azure SQL Database offers many features to build a comprehensive, secure and stable data platform. These include TDE (Transparent Data Encryption), Data Discovery Classification, SQL Vulnerability Assessments and Advanced Threat Detection. Coupled with highly secured infrastructure and integration with other products such as Azure Active Directory and Azure Key Vault you will see why people are confident in deploying data solutions in the cloud with Microsoft.
Transparent Data Encryption
Transparent Data Encryption (TDE) is also known as “encryption at rest” which uses an AES encryption algorithm using keys sized between 128, 192 and 256 bits. This feature performs real-time I/O encryption and decryption of the database files and encrypts backups. Classically with an on-premises SQL Server, a common approach to implementing TDE would be as shown below.
This setup does require planning and code. A part of your TDE strategy should also consider the ways to rotate your keys/certificates, whether that is a yearly process or not, it can become an overhead. One of the advantages of Azure SQL Database is that TDE is enabled by default and certificate management is provided by Microsoft, the built-in server certificate is unique for each server and the encryption algorithm used is AES 256.
You also have the option of bringing your own key, this is done by integrating with Azure Key Vault. This means that you as the user will then be able to leverage the functionality of the Azure Key Vault to carry out key rotations, key backups and reporting capabilities.
The below screenshot shows that with a click of button TDE can be enabled or disabled.
Deny All by Default
Microsoft implements a “deny all by default” policy for Azure SQL Database. This means further changes are required to allow access to Azure SQL Database. There are many ways to do this. You can leverage IP addresses (scoped to server or database level) in the form of firewall rules, Virtual Networks (VNETs) and also private end connections.
Server level firewall rules enable access to your entire Azure SQL server for a given IP address (or range), that is, all the databases within the same logical server. These rules are stored in the master database. Database level rules enable access to certain databases within the same logical server, typically scoped to the user database in question. See the below image for the relationship between these rules and IP based access.
It is straightforward to implement these rules with T-SQL. The following code snippet shows how to add both a server level and database level rule.
— SERVER LEVEL RULE
EXEC sp_set_firewall_rule N’WorldIP’, ‘188.8.131.52’, ‘184.108.40.206’;
–DATABASE LEVEL RULE
EXECUTE sp_set_database_firewall_rule N’USAIP’, ‘0.0.0.9’, ‘0.0.0.9’;
There are many use cases where IP addresses are okay to use, if this is the case then database level rules are the preferred approach, especially if using a failover group configuration for your Azure SQL Database.
However, if your architecture does not allow for access to be based on IP addresses, then you will most likely require the uses of VNETs. Typically, you would define a subnet range within a VNET, thus allowing you to isolate connectivity to these subnets. Further developments in this area have led to the evolution of a feature called Azure Private Link, with this, you can set up a “private link” service within your virtual network and deliver it to your customers, a great way to allow access to consumers for specific services in a very secure manner.
Azure Active Directory (AD) Authentication
One of the key developments of Azure SQL Database is the ability to integrate databases with Azure Active Directory for authentication. This was a very important milestone for the product, meaning that you no longer need to rely on SQL authentication only. Configuring this feature will allow you to connect to the database using MFA (Multi-Factor Authentication), centrally manage users from one location and most importantly enable password rotation policies.
The trust architecture is shown below and is a must when developing your security strategy.
Once this process has been completed, creating Azure AD users is straightforward via TSQL. The below shows the code needed to create a user (under the context of a user database) that has read access. Please note this code will only execute if logged in as the Azure Active Directory Administrator.
CREATE USER [Arun.Sirpal@domain.com] FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [Arun.Sirpal@domain.com]
EXEC sp_addrolemember ‘db_datareader’, ‘Arun.Sirpal@domain.com’;
Advanced Data Security (ADS)
One of the unique selling points of Azure SQL Database, in my opinion, is Advanced Data Security (ADS). This is a collection of features that you can enable for a cost of 11 GBP per month/server. Once enabled you will have access to:
- Advanced Threat detection
- Vulnerability Assessment
- Data Discovery & Classification
To enable this, you will need to navigate to the security section of the database via the Azure portal.
Advanced Threat Detection
Microsoft applies advanced machine learning algorithms to incoming telemetry to gather insights for this threat detection service. It is best practice to enable this setting. Even though there is cost associated with it, the benefits are clear to see.
What does it protect and alert you from?
- SQL Injection attacks.
- Access from an unusual location.
- Access from unfamiliar principal.
- Access from a potentially harmful application.
- Brute force attacks.
Inevitably moving into the cloud brings its own set of security challenges, that is why it is important to leverage this feature. If Microsoft does suspect something suspicious expect real-time email alerts to be sent to designated users.
I believe that using vulnerability assessments is a great starting point for your database security strategy. Why? Once enabled it can help discover, track and provide recommendations for vulnerabilities within your database estate. Then once rectified, you can then establish this as the company baseline thus anything that deviates from this baseline will get flagged as an issue.
These scans can take place on-demand or on a schedule, which can give you peace of mind. Let’s look at an example, below I am enabling ADS.
Once enabled, navigate to your database and initiate a full scan.
As you can see, I am failing 4 checks. Let’s look at a high risk one.
Let’s quickly analyse the issue here. If you look at the blue box you see that one of Microsoft’s best practice is to try not to use server-level firewall rules. Now, this might be fine for non-production / non-sensitive servers where many databases need access to a service such as Data Factory. Most times, administrators are not aware that they have mapped in IP addresses to critical servers using this technique. If you decide this is okay, you can then mark this as your baseline (as indicated by the red box). If you want to address the issue, the orange box provides the code needed to fix the issue.
Data Discovery and Classification
With this feature, you can classify your database, which can be extremely useful for GDPR based exercises. There are two key attributes to the classification process which are important components. These are labels and information types. Labels are used to define the sensitivity level of the data stored in the column and information types being the type of data stored in the column.
To start this process, you need to navigate to the security section of your SQL Database. As you can see below it will return a list of recommended columns to classify with the type and label.
Whilst connected to the database via SQL Server Management Studio (SSMS) you can issue the below query to get a good summary of classification that has just taken place.
sys.all_objects.name AS TableName, sys.all_columns.name As ColumnName,
left join sys.all_objects on sys.sensitivity_classifications.major_id =
left join sys.all_columns on sys.sensitivity_classifications.major_id =
and sys.sensitivity_classifications.minor_id = sys.all_columns.column_id
After reading this overview article on security features for Azure SQL Database, you can see the amount of effort Microsoft has invested in this product.