Edit

Share via

Enable transparent data encryption on SQL Managed Instance enabled by Azure Arc (preview)

  • Article

This article describes how to enable and disable transparent data encryption (TDE) at-rest on a SQL Managed Instance enabled by Azure Arc. In this article, the term managed instance refers to a deployment of SQL Managed Instance enabled by Azure Arc and enabling/disabling TDE will apply to all databases running on a managed instance.

For more info on TDE, please refer to Transparent data encryption.

Turning on the TDE feature does the following:

  • All existing databases will now be automatically encrypted.
  • All newly created databases will get automatically encrypted.

Note

As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.

The latest updates are available in the release notes.

Prerequisites

Before you proceed with this article, you must have a SQL Managed Instance enabled by Azure Arc resource created and connect to it.

Limitations

The following limitations apply when you enable automatic TDE:

  • Only General Purpose Tier is supported.
  • Failover groups aren't supported.

Create a managed instance with TDE enabled (Azure CLI)

The following example creates a SQL Managed Instance enabled by Azure Arc with one replica, TDE enabled:

Azure CLI 
az sql mi-arc create --name sqlmi-tde --k8s-namespace arc --tde-mode ServiceManaged --use-k8s

Turn on TDE on the managed instance

When TDE is enabled on Arc-enabled SQL Managed Instance, the data service automatically does the following tasks:

  1. Adds the service-managed database master key in the master database.
  2. Adds the service-managed certificate protector.
  3. Adds the associated Database Encryption Keys (DEK) on all databases on the managed instance.
  4. Enables encryption on all databases on the managed instance.

You can set SQL Managed Instance enabled by Azure Arc TDE in one of two modes:

  • Service-managed
  • Customer-managed

In service-managed mode, TDE requires the managed instance to use a service-managed database master key as well as the service-managed server certificate. These credentials are automatically created when service-managed TDE is enabled.

In customer-managed mode, TDE uses a service-managed database master key and uses keys you provide for the server certificate. To configure customer-managed mode:

  1. Create a certificate.
  2. Store the certificate as a secret in the same Kubernetes namespace as the instance.

Enable

The following section explains how to enable TDE in service-managed mode.

To enable TDE in service managed mode, run the following command:

Azure CLI 
az sql mi-arc update --tde-mode ServiceManaged

Turn off TDE on the managed instance

When TDE is disabled on Arc-enabled SQL Managed Instance, the data service automatically does the following tasks:

  1. Disables encryption on all databases on the managed instance.
  2. Drops the associated DEKs on all databases on the managed instance.
  3. Drops the service-managed certificate protector.
  4. Drops the service-managed database master key in the master database.

To disable TDE:

Azure CLI 
az sql mi-arc update --tde-mode Disabled

Back up a TDE credential

When you back up credentials from the managed instance, the credentials are stored within the container. To store credentials on a persistent volume, specify the mount path in the container. For example, var/opt/mssql/data. The following example backs up a certificate from the managed instance:

Note

If the kubectl cp command is run from Windows, the command may fail when using absolute Windows paths. Use relative paths or the commands specified below.

  1. Back up the certificate from the container to /var/opt/mssql/data.

    SQL 
    USE master;
GO

BACKUP CERTIFICATE <cert-name> TO FILE = '<cert-path>'
WITH PRIVATE KEY ( FILE = '<private-key-path>',
ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>');

    Example:

    SQL 
    USE master;
GO

BACKUP CERTIFICATE MyServerCert TO FILE = '/var/opt/mssql/data/servercert.crt'
WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/servercert.key',
ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>');

  2. Copy the certificate from the container to your file system.

    Console 
    kubectl cp --namespace <namespace> --container arc-sqlmi <pod-name>:<pod-certificate-path> <local-certificate-path>

    Example:

    Console 
    kubectl cp --namespace arc-ns --container arc-sqlmi sql-0:/var/opt/mssql/data/servercert.crt $HOME/sqlcerts/servercert.crt

  1. Copy the private key from the container to your file system.

    Console 
    kubectl cp --namespace <namespace> --container arc-sqlmi <pod-name>:<pod-private-key-path> <local-private-key-path>

    Example:

    Console 
    kubectl cp --namespace arc-ns --container arc-sqlmi sql-0:/var/opt/mssql/data/servercert.key $HOME/sqlcerts/servercert.key

  1. Delete the certificate and private key from the container.

    Console 
    kubectl exec -it --namespace <namespace> --container arc-sqlmi <pod-name> -- bash -c "rm <certificate-path> <private-key-path>

    Example:

    Console 
    kubectl exec -it --namespace arc-ns --container arc-sqlmi sql-0 -- bash -c "rm /var/opt/mssql/data/servercert.crt /var/opt/mssql/data/servercert.key"

Restore a TDE credential to a managed instance

Similar to above, to restore the credentials, copy them into the container and run the corresponding T-SQL afterwards.

Note

If the kubectl cp command is run from Windows, the command may fail when using absolute Windows paths. Use relative paths or the commands specified below. To restore database backups that have been taken before enabling TDE, you would need to disable TDE on the SQL Managed Instance, restore the database backup and enable TDE again.

  1. Copy the certificate from your file system to the container.

    Console 
    kubectl cp --namespace <namespace> --container arc-sqlmi <local-certificate-path> <pod-name>:<pod-certificate-path>

    Example:

    Console 
    kubectl cp --namespace arc-ns --container arc-sqlmi $HOME/sqlcerts/servercert.crt sql-0:/var/opt/mssql/data/servercert.crt

  1. Copy the private key from your file system to the container.

    Console 
    kubectl cp --namespace <namespace> --container arc-sqlmi <local-private-key-path> <pod-name>:<pod-private-key-path>

    Example:

    Console 
    kubectl cp --namespace arc-ns --container arc-sqlmi $HOME/sqlcerts/servercert.key sql-0:/var/opt/mssql/data/servercert.key

  2. Create the certificate using file paths from /var/opt/mssql/data.

    SQL 
    USE master;
GO

CREATE CERTIFICATE <certicate-name>
FROM FILE = '<certificate-path>'
WITH PRIVATE KEY ( FILE = '<private-key-path>',
    DECRYPTION BY PASSWORD = '<UseStrongPasswordHere>' );

    Example:

    SQL 
    USE master;
GO

CREATE CERTIFICATE MyServerCertRestored
FROM FILE = '/var/opt/mssql/data/servercert.crt'
WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/servercert.key',
    DECRYPTION BY PASSWORD = '<UseStrongPasswordHere>' );

  3. Delete the certificate and private key from the container.

    Console 
    kubectl exec -it --namespace <namespace> --container arc-sqlmi <pod-name> -- bash -c "rm <certificate-path> <private-key-path>

    Example:

    Console 
    kubectl exec -it --namespace arc-ns --container arc-sqlmi sql-0 -- bash -c "rm /var/opt/mssql/data/servercert.crt /var/opt/mssql/data/servercert.key"

Related content

Transparent data encryption