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


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.


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.


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:


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'-EndIpAddress '' 


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.

Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...