Updated 26th October 2020
Introduction
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’, ‘1.2.3.4’, ‘1.2.3.4’;
–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.
VNET service endpoints
Many enterprises will not allow designing services based on IP address access (as seen in the firewall section). The next step from this is moving into a VNET (Virtual Network) service endpoint architecture.
The advantage of this is that it allows you to isolate connectivity to your logical SQL server to a given subnet (within your VNET) you define and also it means that traffic to your database always stays on the Azure backbone rather than the internet, which again many companies want. A typical need of using this is where for example you build an application server within a VNET and map that VNET into the Azure SQL Server. This means that access from the application server to the SQL Server is permitted, this is shown below.
To set this up you need to configure this at the server level. So, within your Azure SQL Server, you navigate to the security section.
Here you will find firewall and virtual networks. Then you have a choice of creating one or adding an existing VNET. The below shows an example VNET that I map into this SQL Server by clicking “Add existing virtual network”.
Please note the informational warning. The service endpoint for Microsoft.SQL must be enabled, so you must make sure that you click enable.
Private Links
The most secure way of communicating with the Azure SQL Database is by using the private link feature. This is the next step beyond VNET service endpoints and ultimately, we are using a private IP address (from our subnet) and removing the need of connecting to public endpoints (which we still do with VNET service endpoint).
With VNET service endpoint, if we do an NSLOOKUP to the Azure SQL server, you will see that it still connects to the control ring (cr2) and uses a public IP address in some capacity.
To set up private links it will involve some configuration work, please see https://docs.microsoft.com/en-us/azure/private-link/create-private-endpoint-powershell
If you successfully complete the setup then when connecting to your Azure SQL Database, this changes to a similar structure below (NSLOOKUP difference shown below).
We are no longer using public DNS but now a private IP address from our subnet, this is quite a difference.
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 [[email protected]]
FROM EXTERNAL PROVIDER;
GRANT CONNECT TO [[email protected]]
EXEC sp_addrolemember ‘db_datareader’, ‘[email protected]’;
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.
Vulnerability Assessments
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.
SELECT
sys.all_objects.name AS TableName, sys.all_columns.name As ColumnName,
Label, Information_Type
FROM
sys.sensitivity_classifications
left join sys.all_objects on sys.sensitivity_classifications.major_id =
sys.all_objects.object_id
left join sys.all_columns on sys.sensitivity_classifications.major_id =
sys.all_columns.object_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.
Azure Policy
Azure policy should be part of your cloud security strategy as this helps with compliance. It evaluates your resources (in our case Azure SQL Server) against a set of business rules, these are known as policy definitions.
There are many definitions that can be applied and checked against your Azure database platform. Please see the following link for the full list. https://docs.microsoft.com/en-us/azure/azure-sql/database/policy-reference. These range from have you enabled advanced database security, to finding out whether you have set public access to your databases.
The below example shows how I am applying a policy against a specific subscription and resource group.
Here we are checking if an Azure AD administrator has been set for the target SQL Servers, something that we should be doing. Once you have created this it will then check against any SQL server within the scope you define.
As you can see, I am non-compliant. This is a great feature to analyse your current infrastructure to see where changes are needed.
In Closing
Get more information on Azure SQL Database here:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview
https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/
Find out more about how Ballard Chalmers can help you with Azure or get in touch.
