Microsoft Azure has many Platform-as-a-Service (PaaS) features, with one of the oldest being Azure SQL Database (which has had many variations on that name in it’s time!)
Over the last few months Microsoft has released a raft of updates to Azure SQL Database that bolster its security chops. As a result I thought it would be good to cover off some basic best practices along with an overview of the new features and how they can help you improve your security stance when implemented.
Basics for any Azure SQL DB engine version
The first thing to note: any TDS connection to Azure SQL Database is running over an encrypted transport. This reduces the chance of your session being hijacked or suffering from Man-in-the-Middle (MitM) style attacks.
Beyond this there are a few configuration items we can cover off that are not tied to the new v12 database engine – namely Firewalls and Auditing.
Firewalls
When you create a new Azure SQL Database server it has a firewall associated with the server instance that blocks any inbound connection to your server and any databases it hosts. There are two options relating to the Azure SQL Database firewall:
- Allow other Azure services: this option will allow any service deployed into an Azure Region to connect to your server instance. Note this is not limited to just your subscription – any service resolving to a public IP address of an Azure Region will be allowed to at least open a TDS connection to your server.
- Allowed IP addresses: this is a way to restrict client access to being only from public IP addresses (or ranges) you explicitly list. This could include Azure services deployed using static IPs if you wanted to really lock down the clients accessing your database server.
Auditing
This feature is available in both releases of the Azure SQL Database engine and is useful for tracking activity in databases. You can enable at the server level and then set each child database to inherit the server setting, or you can turn on just for those databases you want to run auditing for. The result is a series of log files written to a storage account of your configuration.
You can report on these logs using an Excel/PowerQuery-based solution you can download for free from Microsoft. This setup also requires all your clients to connect to the database using a secured connection string.
Say hello to your new friend – the secured connection string
A lot of these newer security features are available only when clients connect using secured connection strings. More modern client applications that support TDS redirection don’t need to be changed, with others (such as JDBC and Tedious) needing an explicit change in connection string.
The old “insecure” connection strings contain:
{servername}.database.windows.net.
with the newer secured strings containing:
{servername}.database.secure.windows.net.
Next step: update to v12 engine
The newer features for Azure SQL Database are being driven out of the v12 engine that is now generally available. If you have an older “v11″ database you can upgrade it to v12 with a minor service disruption. If you are unsure of the version of your database you can run this PowerShell Cmdlet to check:
[code language=”powershell”]
# ARM Mode (pre 1.0.0.0 Cmdlets)
Get-AzureSqlServer -ServerName ‘servername’ `
-ResourceGroupName ‘Default-SQL-WestUS’
[/code]
Note that you can get the ResourceGroupName value by viewing the database in the newer Preview Portal (https://portal.azure.com/). Older pre-Resource Group items in Azure will have been assigned a default group based on their resource type (SQL) and Azure Region (West US).
This call will return the following metadata about your Server instance. If “ServerVersion” displays “2.0” then you are not running on the v12 engine and it’s time to upgrade!
ResourceGroupName : Default-SQL-WestUS ServerName : servername Location : West US SqlAdministratorLogin : lolcatz SqlAdministratorPassword : ServerVersion : 2.0 Tags :
You can upgrade an existing server by running the following Cmdlet:
[code language=”powershell”]
# ARM Mode (pre 1.0.0.0 Cmdlets)
Start-AzureSqlServerUpgrade -ServerName ‘servername’ `
-ResourceGroupName ‘Default-SQL-WestUS’ `
-ServerVersion 12.0
[/code]
Microsoft’s guidance is that you can run this on in-use databases and that you will only have an outage at the very end of the upgrade process. That outage lasts between 40 seconds and 5 minutes, depending on size of the databases and load on the server.
The above Cmdlet returns output immediately the request has been made and unless you provide additional arguments to the Cmdlet the upgrade will be queued and will be undertaken ASAP.
If you want to find out the status of your upgrade request you can run the following Cmdlet which will return ‘Completed’ for the status when your server has moved to v12.
[code language=”powershell”]
# ARM Mode (pre 1.0.0.0 Cmdlets)
Get-AzureSqlServerUpgrade -ServerName ‘servername’ `
-ResourceGroupName ‘Default-SQL-WestUS’
[/code]
Moar! options from the v12 engine
Now that we have upgraded to the v12 engine we have a few more things we can do to protect our data – Dynamic Data Masking, Row Level Security, Transparent Data Encryption (TDE) and Azure Active Directory user credentials.
Dynamic Data Masking (DDM)
I blogged earlier in the year about DDM, so I’m not going to repeat myself here.
The important items to note, however, about the use of DDM are the following restrictions:
- Your clients must be using the new secured connection string mentioned earlier in this post.
- Administrator logins will never be included in the data masking (you’re not using admin users in your connection strings anyway right?!)
Row Level Security (RLS)
Like DDM, I already blogged about RLS earlier in the year so I’m not going into detail here.
The key activity when starting to use RLS is to plan your permissions structure properly and map to users/roles or consuming applications which are then used in the predicate functions to filter results returned from the database.
Transparent Data Encryption (TDE)
This has been one of those feature requests since forever for Azure SQL Database. The good news is that it’s here and it’s already generally available. The small downside is that you must decrypt any on-premises database you might move to Azure SQL Database first and then re-encrypt once uploaded. The process to enable TDE is pretty easy – either use the new Azure Preview Portal, T-SQL:
[code language=”sql”]
ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;
GO
[/code]
or via some Azure PowerShell-fu:
[code language=”powershell”]
# ARM Mode (pre 1.0.0.0 Cmdlets)
Set-AzureSqlDatabaseTransparentDataEncryption -ServerName ‘servername’ `
-ResourceGroupName ‘Default-SQL-WestUS’ `
-DatabaseName ‘YourDatabase’ `
-State "Enabled"
[/code]
Then you need to wait while the database is initially encrypted (note that the database can be online and in use while this process occurs). You can utilise:
Get-AzureSqlDatabaseTransparentDataEncryption
to check on the progress of encryption (or view in the Portal).
Azure AD user credentials
This is a new (in preview at time of posting) feature that introduces the ability to use Azure Active Directory credentials to manage and access databases.
The important items to note about this feature are:
- You must be using SQL Server Management Studio 2016 (currently in CTP) if you want to manage databases and connect using Azure AD credentials.
- Currently only the most recent .Net ADO libraries support using these credentials in connection strings for applications.
Always Encrypted
I’m cheating a little with this one – it’s not actually yet available (even in preview format) but has been announced by Microsoft.
It’s interesting to see Microsoft learning from their SaaS services and driving true multi-tenant capabilities back into offerings such as SQL Server.
Always Encrypted is supported in the upcoming SQL Server 2016 release, along with Azure SQL Database and allows clients to leverage “Bring Your Own Key” (BYOK) so their data remains always encrypted and the keys used are inaccessible to DBAs and other SQL admins.
This is worth looking at if you are architecting and delivering multi-tenant applications sitting on Azure SQL Database.
But, wait, there’s more!
The new Preview Portal and its associated Role Based Access Control (RBAC) provides us with additional capabilities to lock down and control who can perform service management features on the databases and servers we deploy.
The key role for us is the SQL Security Manager role which “can manage the security related policies of SQL servers and databases” (without the need to be a server or database admin).
So, there we have it, all the ways you can help secure your Azure SQL Database instances and ensure you minimise the chances that someone is either going to break into your Azure SQL Database, view or steal your sensitive data, or take a back-up and restore to their own server. Keep an eye out in this space for future announcements from Microsoft as they are innovating quite a lot in this space at the moment.
Comments are closed.