Skip to content

Latest commit

 

History

History
140 lines (103 loc) · 7.22 KB

sql-database-managed-instance-migrate-tde-certificate.md

File metadata and controls

140 lines (103 loc) · 7.22 KB
title description services ms.service ms.subservice ms.custom ms.devlang ms.topic author ms.author ms.reviewer manager ms.date
Migrate TDE certificate - Azure SQL Database Managed Instance | Microsoft Docs
Migrate certificate protecting Database Encryption Key of a database with transparent Data Encryption to Azure SQL Database Managed Instance
sql-database
sql-database
security
conceptual
MladjoA
mlandzic
carlrab, jovanpop
craigg
08/09/2018

Migrate certificate of TDE protected database to Azure SQL Database Managed Instance

When migrating a database protected by Transparent Data Encryption to Azure SQL Database Managed Instance using native restore option, the corresponding certificate from the on-premises or IaaS SQL Server needs to be migrated before database restore. This article walks you through the process of manual migration of the certificate to Azure SQL Database Managed Instance:

[!div class="checklist"]

  • Export certificate to a Personal Information Exchange (.pfx) file
  • Extract certificate from file to base-64 string
  • Upload it using PowerShell cmdlet

For an alternative option using fully managed service for seamless migration of both TDE protected database and corresponding certificate, see How to migrate your on-premises database to Managed Instance using Azure Database Migration Service.

Important

Transparent Data Encryption for Azure SQL Database Managed Instance works in service-managed mode. Migrated certificate is used for restore of the TDE protected database only. Soon after restore is done, the migrated certificate gets replaced by a different, system-managed certificate.

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. Pvk2Pfx tool is part of the Enterprise Windows Driver Kit, a standalone self-contained command-line environment.

  • Windows PowerShell version 5.0 or higher installed.

  • AzureRM PowerShell module installed and updated.

  • AzureRM.Sql module version 4.10.0 or higher. Run the following commands in PowerShell to install/update the PowerShell module:

    Install-Module -Name AzureRM.Sql
Update-Module -Name AzureRM.Sql

Export TDE certificate to a Personal Information Exchange (.pfx) file

The certificate can be exported directly from the source SQL Server, or from the certificate store if being kept there.

Export certificate from the source SQL Server

Use the following steps to export certificate with SQL Server Management Studio and convert it into pfx format. Generic names TDE_Cert and full_path are being used for certificate and file names and paths through the steps. They should be replaced with the actual names.

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

  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

    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>'
)

    backup TDE certificate

  4. Use PowerShell console to copy certificate information from a pair of newly created files to a Personal Information Exchange (.pfx) file, using 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 certificate from certificate store

If certificate is kept in SQL Server’s local machine certificate store, it can be exported using the following steps:

  1. Open PowerShell console and execute the following command to open 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 certificate and click Export…

  4. Follow the wizard to export certificate and private key to a Personal Information Exchange format

Upload certificate to Azure SQL Database Managed Instance using Azure PowerShell cmdlet

  1. Start with preparation steps in PowerShell:

    # Import the module into the PowerShell session
Import-Module AzureRM
# Connect to Azure with an interactive dialog for sign-in
Connect-AzureRmAccount
# List subscriptions available and copy id of the subscription target Managed Instance belongs to
Get-AzureRmSubscription
# Set subscription for the session (replace Guid_Subscription_Id with actual subscription id)
Select-AzureRmSubscription Guid_Subscription_Id

  2. Once all preparation steps are done, run the following commands to upload base-64 encoded certificate to the target Managed Instance:

    $fileContentBytes = Get-Content 'C:/full_path/TDE_Cert.pfx' -Encoding Byte
$base64EncodedCert = [System.Convert]::ToBase64String($fileContentBytes)
$securePrivateBlob = $base64EncodedCert  | ConvertTo-SecureString -AsPlainText -Force
$password = "SomeStrongPassword"
$securePassword = $password | ConvertTo-SecureString -AsPlainText -Force
Add-AzureRmSqlManagedInstanceTransparentDataEncryptionCertificate -ResourceGroupName "<ResourceGroupName>" -ManagedInstanceName "<ManagedInstanceName>" -PrivateBlob $securePrivateBlob -Password $securePassword

The certificate is now available to the specified Managed Instance and backup of corresponding TDE protected database can be restored successfully.

Next steps

In this article, you learned how to migrate certificate protecting encryption key of database with Transparent Data Encryption, from the on-premises or IaaS SQL Server to Azure SQL Database Managed Instance.

See Restore a database backup to an Azure SQL Database Managed Instance to learn how to restore a database backup to an Azure SQL Database Managed Instance.