Add and Monitor Transparent Data Encryption to Azure SQL Databases

By: Tim Smith   |   Updated: 2016-12-23   |   Comments (1)   |   Related: > Azure SQL Database

Problem

In this tip we look at how to turn on the Transparent Data Encryption settings through the portal in Azure, as well as how to automate both an audit of these settings with PowerShell and an update of these settings, if they're not already turned on. We will also look at some considerations regarding the impact of copying or restoring Azure SQL databases with TDE enabled.

Solution

Before setting the Transparent Data Encryption settings through the portal, if you don't have "SQL Databases" saved as a part of your toolbar when you start the Portal, I recommend selecting the option of "More Services" then searching for "SQL Databases" and clicking the start next to this - from now on it will appear in the toolbar (see image below). This will make this tip easier to complete, along with making it faster to see options on Azure SQL databases.

From here, click on the database you want to add encryption, which will bring up an option window. In this list, you will see the option "Transparent Data Encryption." Select this option. 

After moving the slider to "ON" you can click "Save" to save the encryption settings. You should see "Encryption in progress..." in the portal (see the second image below this).

Once the encryption status has been changed, this will be reflected when you use the below PowerShell script to check.

According to Microsoft, Azure SQL's transparent data encryption offers the following features:

1. Real time at rest encryption of the database.
2. Real time at rest encryption of the log files.
3. Restores to points in time with the transparent data encryption setting, provided that the point in time of the restore point had this setting enabled. An important point here is that if I select a point in time where transparent data encryption did not exist, this setting will be set to false.
4. Microsoft adjusts the server certificate - responsible for protecting the key - four times a year.

As a reminder, transparent data encryption does not mean objects are encrypted. Here is a simple example to create an encrypted stored procedure.

CREATE PROCEDURE stp_ReturnOne WITH ENCRYPTION
AS
BEGIN
 SELECT 1
END

The above procedure will be encrypted because it's specified during creation (the same would apply if I added WITH ENCRYPTION on an ALTER statement). If I want my objects to be encrypted, I will need to specify that during the creation or alter of each object.

Object encryption will show up with errors when you export a bacpac then importing it into another environment - a database with transparent data encryption enabled won't throw an error if no objects are encrypted and it won't carry the transparent data encryption settings for the import file. This means that if you want a copy of the database in an encrypted form in Azure SQL, you should use the copy database feature or restore point in time feature (see below image of a test copy) over importing a bacpac - otherwise, you will have to add the encryption after the import. However, if you have encrypted objects, the bacpac extraction will fail. These are fundamentally two different features: one encrypting code and the other encrypting data.

If we want a copy of an Azure SQL database with transparent data encryption, we can create a copy of the database or restore a database to a point in time, and disable the encryption without affecting the original database. In addition, we should still use strong security settings for access, such as least privilege, as encryption does not prevent someone who is able to obtain access through legitimate permissions.

PowerShell Function to Enable TDE for all Databases

One quick alternative to using the portal, if we're needing to loop through all databases on a server, or loop through databases by a filter name, and we have many databases, is to use the below PowerShell script - which outputs if databases are encrypted and updates them if they're not:

Function Execute-Sql {
    Param(
        [Parameter(Mandatory=$true)][string]$connectionstring
        , [Parameter(Mandatory=$true)][string][string]$command
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = $connectionstring
        
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandTimeout = 0
        $cmd.CommandText = $command

        try
        {
            $scon.Open()
            $cmd.ExecuteNonQuery()
        }
        catch [Exception]
        {
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
        }
    }
}

Function Audit-AzureDBEncrption {
    Param(
        [Parameter(Mandatory=$true)][string]$server
        , [Parameter(Mandatory=$true)][string]$connectionsettings
        , [ValidateSet("2008R2","2012","2014","2016")][string]$version
        , [Parameter(Mandatory=$false)][string]$filterdatabase
    )
    Process
    {
        $nl = [Environment]::NewLine
        switch ($version)
        {
            "2008R2" { 
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2012" { 
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2014" { 
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2016" { 
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }
        }

        $scon = "Data Source=$server.database.windows.net,1433;Initial Catalog=master;$connectionsettings"
  $servercon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
  $servercon.ConnectionString = $scon
        $sqlsrv = New-Object Microsoft.SqlServer.Management.SMO.Server($servercon)

        if ($filterdatabase -eq "")
        {
            foreach ($db in $sqlsrv.Databases | Where-Object ({$_.Name -ne "master"}))
            {
                if ($db.EncryptionEnabled -eq $false)
                {
                    $alter_addtde = "ALTER DATABASE [" + $db.Name + "] SET ENCRYPTION ON"
                    $connectionstring = "Data Source=$server.database.windows.net,1433;Initial Catalog=" + $db.Name + ";$connectionsettings"
                    Execute-Sql -connectionstring $connectionstring -command $alter_addtde
                }
                else
                {
                    $write = $db.Name + " encryption status is " + $db.EncryptionEnabled
                    Write-Host $write
                }
            }
        }
        else
        {   
            foreach ($db in $sqlsrv.Databases | Where-Object ({$_.Name -like "*$filterdatabase*"}))
            {
                if ($db.EncryptionEnabled -eq $false)
                {
                    $alter_addtde = "ALTER DATABASE [" + $db.Name + "] SET ENCRYPTION ON"
                    $connectionstring = "Data Source=$server.database.windows.net,1433;Initial Catalog=" + $db.Name + ";$connectionsettings"
                    Execute-Sql -connectionstring $connectionstring -command $alter_addtde
                }
                else
                {
                    $write = $db.Name + " encryption status is " + $db.EncryptionEnabled
                    Write-Warning $alter_addtde
                    Write-Host $write
                }
            }
        } 
    }
}

Audit-AzureDBEncrption -server "OurAzureServer" -version 2012 -filterdatabase "" -connectionsettings ""

If we only want to check one database, we can specify the database in the -filterdatabase parameter. When I can use the SMO library with PowerShell I will since Microsoft Azure PowerShell commands tend to change frequently and without notice, often breaking automation scripts.

Regardless of which approach you choose, you will see errors from time to time about failed attempts; the above script is designed to be called again as it won't affect databases with encryption already on, but it does report them. Depending on developer access and frequency of use, I recommend performing occasional audits of encryption settings, since this setting may change if access allows it. If you do not have the appropriate access to the server, however, this won't return information and encryption settings won't be updated.

Next Steps

• The above PowerShell script can audit all or some Azure SQL databases on a server and update their encryption settings.
• If you need to restore an Azure SQL database to another feature and you use bacpacs, you can run this script against that server and it will update it as well, provided that the executing user has the appropriate permissions.
• While rare, I've experienced cases where I had to exit and re-enter the portal to see the settings' change reflected.
• Check out these other tips:
  • Azure
  • PowerShell
  • Encryption

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips