Securing the data tier of an Azure SQL Sever Database – Part 1

One of the most prominent questions when working with PaaS databases is how to secure them. In these defence series, I will talk about limiting access to objects, protecting your data and auditing. Also, I’ll add some extra resources for people looking for a complete overview of the available security features on an Azure SQL Server Database. Ok, let’s get started with the first layer. Enjoy!

The first layer of defence: securing your connection

In order to secure your connection, you have two layers of protection. The first one is setting up your firewall, the second one is encryption.

Firewalls

The Azure firewall rules generally reject connection attempts from IP addresses that have not been explicitly whitelisted. So, the first thing you should do is creating a server-level firewall rule, preferable by using PowerShell. Common sense dictates that this rule should be as restrictive as possible, especially when allowing IP ranges through your server firewall.

Prerequisites

Many new features of SQL Database are only supported when you use the Azure Resource Manager deployment model. Scripts in these series of blog posts use the Azure SQL Database PowerShell cmdlets for Resource Manager. Once you have installed it, you can run the following commandlet:

Add-AzureRmAccount 

Afterwards, you will be presented with a sign-in screen to enter your credentials. Use the same credentials that you use to sign in to the Azure portal. If you have multiple subscriptions, use the Set-AzureRmContext cmdlet to select which subscription your PowerShell session should use.

Creating a firewall rule for your server

New-AzureRmSqlServerFirewallRule-ResourceGroupName 'KoheraResourceGroup'-ServerName 'KoheraServer'-FirewallRuleName "AllowAppServerRange" -StartIpAddress 192.168.1.8'-EndIpAddress '192.168.1.10' 

Encryption

Per default, all connections to Azure SQL Databases use the Always Encrypted feature as known in an on-premise SQL 2016 Server. Due to this, all Azure SQL Database connections require encryption (SSL/TLS) while data is in transit to and from the database. Because we’re using a PaaS database, we cannot trust the server certificate and the decryption parameters must be specified in the connection string. To facilitate this process, this is done for you when you copy your connection string out of the Azure Portal. You can easily verify it, because connection string parameters include Encrypt=True and TrustServerCertificate=False.