Introduction

Microsoft SQL is one of the most common workloads being deployed or migrated to Azure public cloud. The main reason why organizations are doing this is because it provides performance benefits, ease of management and deployment, and also because it provides several security features.

I will cover those security features in this article.

Note: I will be covering the SQL Azure PaaS solution only, whereas Azure also allows you to run your SQL Server service within Azure IaaS (Virtual Machines).

Securing an Azure SQL database

A database that’s running in Azure SQL has a lot of security features already built-in, all of which are easily usable as an Azure administrator. Several of these features are enabled by default, whereas other ones can be defined according your specific requirements.

The following security features are available:

-          Database connectivity is blocked by using a database firewall

-          Database connectivity using an encrypted connection string

-          Manage local database user access

-          Database encryption

-          Auditing, monitoring and threat detection

-          Data Masking and Row-level security


In this article, I will drill down into a few of these, walking you through the configuration steps in the Azure Portal, and explaining what makes these features interesting for an organization.

SQL Server Firewall

Like any typical behavior of a firewall, the SQL Server firewall makes sure no incoming connections to your database server are allowed, besides the ones you explicitly define. This is an interesting scenario, considering that you are using a public cloud service. A huge misconception in several organizations, is that – because a SQL database runs in a public cloud service like Azure – the whole world can automatically connect to it. Which obviously is not the case. However, even with this default behavior, there are still certain settings you need to be aware of, to give you the most restrictive configuration.

To be complete, I must mention this firewall configuration exists on both the SQL Database level, as well as on the underlying SQL Azure server level. Keep in mind, this is a Platform Service, not your own-managed Virtual Machine running SQL Server services.

Assuming you already have an Azure SQL Database running, let me show you where you can manage the SQL Azure firewall configuration options, on SQL Server level, by using the Azure Portal. (If you need some guidance on how to deploy an Azure SQL Database, here is a link to the Microsoft documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-portal)

1.  From the Azure Portal, browse to SQL Databases and select your SQL Database.

2. In the Overview Blade, click the “Set server firewall” button.


3. In the Firewall Settings blade that opens, you can add your public client IP, or integrate with an Azure Virtual Network

4. Press the “+Add Client IP” button, to have your public IP address from your internet connection added to the list of firewall rules. 

5. Save your settings.

6. When you now connect from your SQL Server Management Studio to this database, connection will be allowed. From here, you can manage your database in the exact same way as if your SQL database was running inside a Virtual Machine in your on-premises datacenter.

7. Another setting I want to point out here, which is also in the Firewall Settings, is “Allow Access to Azure Services”. By default, this setting is ON, whereas I would recommend turning this feature OFF. Having the setting “ON”, means that all Azure subscriptions can connect to your SQL Azure server (while still needing the appropriate authentication credentials as well, but still…)

8. This completes the configuration of the Azure SQL firewall settings. 

Again, this configuration is active on the Azure SQL Server level. If you want to define similar connection settings to specific databases, know that this is possible, but only by executing the sp_set_database_firewall statement from within your SQL Management Studio. This cannot be configured from within the Azure Portal.

SQL database encrypted connection string

Besides a secured firewall connection to your Azure SQL Server and Database, the next layer of security exists within the encrypted connection string, which is mainly used out of your applications, like a web service, or a client application.

To retrieve this connection string information using the Azure Portal, go through the following steps:

1. From the Azure Portal, browse to SQL Database / Settings and select Connection String

2. Using the ADO.NET tab, it shows you the detailed connection string to use:


3. Notice the following 2 settings:

- Encrypt=True; this points to an encrypted connection
- TrustServerCertificate=False; this means to not trust the server certificate

Out of both settings, a secured and encrypted TLS (Transport Layer Security) connection is established between the client and SQL database.

Encrypting the SQL Azure Database

In the previous topic, I described the encrypted connection string between the client, application and the Azure SQL Database. This topic will discuss the encryption of the actual Azure SQL Database itself. More specifically, there is a feature called Transparent Data Encryption (TDE), which automatically encrypts your data at rest.

When deploying an Azure SQL Database from scratch, TDE is enabled by default. To validate your database, if TDE is enabled, use the following steps

1. From the Azure Portal, browse to SQL Databases and select the database.

2. In the security settings, select Transparent Data Encryption (TDE)

To add to this feature, you must have similar configuration settings on the Azure SQL Server level, adding this capability to your own encryption keys:

1. From the Azure Portal, browse to SQL Servers and select the Azure SQL Server.

2. In the security settings, select Transparent Data Encryption (TDE)

3. Here you have the option to select an encryption key object from Azure Key Vault, or you can point to a HTTPS-published link to your own encryption key.

Summary

In this article, I wanted to emphasize some of the important security features provided by Azure SQL Servers and Azure SQL Databases, mainly operating on the infrastructure side of the platform. You learned about firewall and VNet connectivity options, encrypted database connection strings and Transparent Data Encryption. Want to learn more about Azure security? Register for PASS Summit where I’ll be leading both a Pre-Conference and General Session!