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.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
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 made easy on the 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...