Rediger

Del via

Migrate a SQL Server TDE certificate to Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

In this article, learn how to migrate the certificate before you migrate your TDE-protected SQL Server database to Azure SQL Managed Instance by using the native restore option.

When you migrate a database protected by Transparent Data Encryption (TDE) from SQL Server to Azure SQL Managed Instance by using the native restore option, you must first migrate the corresponding certificate before you restore the database to the SQL managed instance.

Alternatively, you can use the fully managed Azure Database Migration Service to seamlessly migrate both a TDE-protected database and the corresponding certificate.

This article focuses on migrating databases from SQL Server to Azure SQL Managed Instance. To move databases between SQL managed instances, see:

Prerequisites

To complete the steps in this article, you need the following prerequisites:

  • Pvk2Pfx command-line tool installed on the on-premises server or other computer with access to the certificate exported as a file. The Pvk2Pfx tool is part of the Enterprise Windows Driver Kit, a self-contained command-line environment.
  • Windows PowerShell version 5.0 or higher installed.

Make sure you have the following prerequisites:

Run the following commands in PowerShell to install or update the module:

Install-Module -Name Az.Sql
Update-Module -Name Az.Sql

Export the TDE certificate to a .pfx file

You can export the certificate directly from the source SQL Server instance, or from the certificate store if you're keeping it there.

Export the certificate from the source SQL Server instance

The following steps export the certificate by using SQL Server Management Studio and convert it into .pfx format. The generic names TDE_Cert and full_path are placeholders for certificate names, file names, and paths. Replace them with the actual names.

  1. In SSMS, open a new query window and connect to the source SQL Server instance.

  2. Use the following script to list TDE-protected databases and get the name of the certificate protecting encryption of the database to be migrated:

    USE master
GO
SELECT db.name as [database_name], cer.name as [certificate_name]
FROM sys.dm_database_encryption_keys dek
LEFT JOIN sys.certificates cer
ON dek.encryptor_thumbprint = cer.thumbprint
INNER JOIN sys.databases db
ON dek.database_id = db.database_id
WHERE dek.encryption_state = 3

    Screenshot in SSMS that shows a list of TDE certificates.

  3. Execute the following script to export the certificate to a pair of files (.cer and .pvk), keeping the public and private key information:

    USE master
GO
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'c:\full_path\TDE_Cert.cer'
WITH PRIVATE KEY (
  FILE = 'c:\full_path\TDE_Cert.pvk',
  ENCRYPTION BY PASSWORD = '<SomeStrongPassword>'
)

    Screenshot in SSMS that shows the backed up TDE certificate.

  4. Use the PowerShell console to copy certificate information from a pair of newly created files to a .pfx file by using the Pvk2Pfx tool:

    .\pvk2pfx -pvk c:/full_path/TDE_Cert.pvk  -pi "<SomeStrongPassword>" -spc c:/full_path/TDE_Cert.cer -pfx c:/full_path/TDE_Cert.pfx

Export the certificate from a certificate store

If you keep the certificate in the SQL Server local machine certificate store, use the following steps to export it:

  1. Open the PowerShell console and run the following command to open the Certificates snap-in of Microsoft Management Console:

    certlm

  2. In the Certificates MMC snap-in, expand the path Personal > Certificates to see the list of certificates.

  3. Right-click the certificate and select Export.

  4. Follow the wizard to export the certificate and private key to a .pfx format.

Upload the certificate to Azure SQL Managed Instance by using an Azure PowerShell cmdlet

Important

Use a migrated certificate only to restore the TDE-protected database. Shortly after the restore finishes, the migrated certificate is replaced by a different protector. The new protector is either a service-managed certificate or an asymmetric key from the key vault, depending on the type of TDE you set on the instance.

  1. Start with preparation steps in PowerShell:

    # import the module into the PowerShell session
Import-Module Az
# connect to Azure with an interactive dialog for sign-in
Connect-AzAccount
# list subscriptions available and copy id of the subscription target the managed instance belongs to
Get-AzSubscription
# set subscription for the session
Select-AzSubscription <subscriptionId>

  2. After you complete all preparation steps, run the following commands to upload the base-64 encoded certificate to the target SQL managed instance:

    # If you are using PowerShell 6.0 or higher, run this command:
$fileContentBytes = Get-Content 'C:/full_path/TDE_Cert.pfx' -AsByteStream
# If you are using PowerShell 5.x, uncomment and run this command instead of the one above:
# $fileContentBytes = Get-Content 'C:/full_path/TDE_Cert.pfx' -Encoding Byte
$base64EncodedCert = [System.Convert]::ToBase64String($fileContentBytes)
$securePrivateBlob = $base64EncodedCert  | ConvertTo-SecureString -AsPlainText -Force
$password = "<password>"
$securePassword = $password | ConvertTo-SecureString -AsPlainText -Force
Add-AzSqlManagedInstanceTransparentDataEncryptionCertificate -ResourceGroupName "<resourceGroupName>" `
    -ManagedInstanceName "<managedInstanceName>" -PrivateBlob $securePrivateBlob -Password $securePassword

The certificate is now available to the specified SQL managed instance, and you can restore the backup of the corresponding TDE-protected database.

Note

The uploaded certificate isn't visible in the sys.certificates catalog view. To confirm successful upload of the certificate, run the RESTORE FILELISTONLY command.

Related content

  • Last updated on