In one of our recent articles published on this forum, we described the main characteristics of row-level security – a new feature introduced in Azure SQL Database V12 as well as in SQL Server 2016 Community Technology Preview 2. As we pointed out, this was another sign that the trend manifested by a lack of feature parity between these two products, with the cloud service placing distant second behind its well-established on-premises counterpart is consistently declining. In this article, we will review another security-related feature known as Transparent Data Encryption, which also exemplifies this trend, although, unlike row-level security, it made its first appearance long before Azure SQL Database emerged as a potential substitute for a full-fledged SQL Server installation (more specifically, it became available starting with its 2008 version).
Transparent Data Encryption offers the ability to encrypt content of the database, its transaction logs, as well as backups while at rest. Encryption and decryption are performed in real-time, at the individual page level, as the database is being written to and read from storage, without necessitating changes to applications accessing their data. In the case of Azure SQL Database, its performance impact is minimized by relying on the Intel AES-NI hardware acceleration, included as an inherent part of the service.
Transparent Data Encryption relies on AES-256 symmetric encryption (symmetric algorithms perform significantly better than their asymmetric counterparts), leveraging the so called database encryption key. The traditional approach used in SQL Server implementations relies on the Database Master Key (residing in the master database) to generate a certificate that subsequently uses asymmetric encryption to protect the database encryption key. The Database Master Key is, in turn, protected in a similar manner by the Service Master Key, created at the time of installing the SQL Server instance (which is itself encrypted by using the Windows operating system-level Data Protection API). In the case of Azure SQL Database, the equivalent functionality is provided by the Azure-resident service managed certificate, leaving customers with the task of generating their respective database encryption keys for individual user databases. Effectively, customers need to simply enable encryption for arbitrarily chosen databases by using the Azure Preview Portal, Azure PowerShell Module, or Transact-SQL, which gets automatically applied to database backups and restores, copies, and replicas. As a result, each encrypted database has its own symmetric key protected by a common certificate generated on the server level. Note that this implies that encryption keys of databases configured for either Standard or Active Geo-Replication will be protected by different certificates (since the scope of an individual Azure SQL server cannot span multiple regions).
To further increase security, certificates are transparently rotated every 90 days. Currently there is no support for storing Transparent Data Encryption keys or certificates in Azure Key Vault (although you can certainly take advantage of its capabilities when encrypting content of SQL Server instances running in Azure Infrastructure-as-a-Service virtual machines).
Permissions to manage Transparent Data Encryption leverage Role Based Access Control and are granted by default to users and groups associated with the built-in roles of Owner, Contributor and SQL Security Manager. In general, there are three different methods of enabling this functionality:
- Azure Preview Portal– provides a graphical interface for managing Azure SQL Database configuration. You can enumerate all SQL databases by using the Browse entry in the vertical navigation bar on the left hand side of the portal page and then selecting SQL database in the Filter blade. Once you click on the target instance in the result set, you will be presented with the blade displaying its current Settings. From here, you need to click the Transparent data encryption entry, which will open the corresponding blade. At that point, you simply click on the ON button underneath the Data encryption label, followed by Save in the command bar at the top of the blade. Encryption status will display progress of the operation and eventually change to Encrypted. Conversely, to turn off encryption, you would click on the OFF button underneath the Data encryption label, followed by Save in the command bar at the top of the blade.
- Windows PowerShell – the same objective can be accomplished in a scripted manner by leveraging Azure PowerShell Module. The actual syntax depends on the version of the module, which has changed starting with the version 1.0 (availabile in preview at the time of writing of this article). The primary difference between this and earlier versions is the way in which Azure Resource Manager cmdlets are handled. The former requires explicit switch from the default Service Management mode (which is accomplished by running the Switch-AzureMode cmdlet with the -Name AzureResourceManager parameter), while the latter provides equivalent functionality by using a separate set of cmdlets, which incorporate the AzureRM string into the standard combination of verb followed by noun (for example, Select-AzureRmSubscription is the replacement of Select-AzureSubscription, which would need to follow a switch to AzureResourceManager mode).
All versions of Azure PowerShell modules prior to version 1.0 are available for download from the GitHub repository. Version 1.0 is published in PowerShell Resource Gallery. In order to take advantage of its capabilities, you need to first download and install the Windows Management Framework 5.0 (in Production Preview at the time of writing of this article), available from Microsoft Download Center (unless you are using Windows 10 which has WMF 5.0 integrated into the operating system). Once this step is completed, you can run Install-Module AzureRM, which will automatically handle provisioning of the requested module (as well as any missing prerequisites). The distribution mechanism of the module and its dependencies relies on the PowerShellGet extension of the OneGet unified software package management framework component, which takes over the responsibility of software discovery, installation, and inventory. The Install-Module PowerShellGet cmdlet serves as a wrapper to the corresponding Install OneGet command. By invoking this cmdlet, you initiate a search across the PowerShell Resource Gallery for the relevant module. For more information regarding this mechanism, refer to the MSDN Blogs.
If you decided to employ version 1.0 of the Azure PowerShell module, you could use the following sequence of commands to enable encryption and monitor its progress on an Azure SQL Database named sqldb1 hosted on the server sqlsrv1 residing in the resource group sqlrg1.
Set-ExecutionPolicy RemoteSigned Install-Module AzureRM # at this point, you might be prompted to download and install NuGet-anycpu.exe, which is needed to interact with NuGet based galleries Install-AzureRM Import-AzureRM Login-AzureRmAccount # at this point, you will be prompted to provide credentials to sign in to your Azure subscription Set-AzureRMSqlDatabaseTransparentDataEncryption ` -ServerName 'sqlsrv1' ` -ResourceGroupName 'sqlrg1 ` -DatabaseName 'sqldb1' ` -State 'Enabled' Get-AzureRMSqlDatabaseTransparentDataEncryptionActivity ` -ServerName 'sqlsrv1' ` -ResourceGroupName 'sqlrg1 ` -DatabaseName 'sqldb1'
If you prefer to continue using earlier versions of Azure PowerShell, then you can produce the same outcome by running the following cmdlets (assuming that the target Azure SQL Database, Azure SQL server, and the resource group are named the same as in the previous example):
Add-AzureAccount # at this point, you will be prompted to provide credentials to sign in to your Azure subscription Switch-AzureMode -Name AzureResourceManager # at this point, you will receive a warning (as expected) stating that the Switch-AzureMode cmdlet is deprecated and will be removed in a future release Set-AzureSqlDatabaseTransparentDataEncryption ` -ServerName 'sqlsrv1' ` -ResourceGroupName 'sqlrg1 ` -DatabaseName 'sqldb1' ` -State 'Enabled' Get-AzureSqlDatabaseTransparentDataEncryptionActivity ` -ServerName 'sqlsrv1' ` -ResourceGroupName 'sqlrg1 ` -DatabaseName 'sqldb1'
- Transact-SQL – another option you have is managing encryption while connected directly to the target database. Note that this requires the membership in the dbmanager role defined in the master database. To enable encryption of an Azure SQL Database named sqldb1, you would run ALTER DATABASE [sqldb1] SET ENCRYPTION ON Transact-SQL statement. To monitor the progress of this task, you can query the value of encryption_state column of the sys.dm_database_encryption_keys view.
This concludes our overview of Transparent Data Enryption in Azure SQL Database. In our upcoming articles, we will continue presenting recently introduced security-related features.
