Azure SQL How To - SQL Managed Instance

Task 1: Copy encryption certificate on SQL Server to SQL MI

On the source SQL Server machine, in a query window, run script below to export the certificate to a file

select * from sys.certificates
go
-- Find the backup encryption certificate in the query output

BACKUP CERTIFICATE backup_certificate TO FILE = 'C:\SQL_BKP\sql_bkp\YourServerName_backup_certificate_PROTECTED_BY_DMK_WITH_PRI_KEY.cer'
	WITH PRIVATE KEY (FILE = 'C:\SQL_BKP\sql_bkp\YourServerName_backup_certificate_PROTECTED_BY_DMK_WITH_PRI_KEY.pvk',
	ENCRYPTION BY PASSWORD = 'Your_Password');
GO

On the same SQL Server machine, in a PowerShell console, copy certificate information from a pair of newly created files to a Personal Information Exchange (.pfx) file, using Pvk2Pfx

cd "C:\Program Files (x86)\Windows Kits\10\bin\10.0.18362.0\x64"

.\pvk2pfx -pvk C:\SQL_BKP\YourServerName_backup_certificate_PROTECTED_BY_DMK_WITH_PRI_KEY.pvk  -pi "Your_Password" -spc C:\SQL_BKP\YourServerName_backup_certificate_PROTECTED_BY_DMK_WITH_PRI_KEY.cer -pfx C:\SQL_BKP\YourServerName_backup_certificate_PROTECTED_BY_DMK_WITH_PRI_KEY.pfx

If the SQL Server machine doesn't have Internet access, copy the certificate backup files to the server that does have Internet access, then run below

# On machine having Internet access

$ResourceGroupName = "Your_SQLMI_Resource_Group_Name"
$SQLMIName = "Your_SQLMI_Name"
$fileContentBytes = Get-Content 'C:\sql_bkp\YourServerName_backup_certificate_PROTECTED_BY_DMK_WITH_PRI_KEY.pfx' -Encoding Byte

$base64EncodedCert = [System.Convert]::ToBase64String($fileContentBytes)

$securePrivateBlob = $base64EncodedCert  | ConvertTo-SecureString -AsPlainText -Force

$password = "Your_Password"

$securePassword = $password | ConvertTo-SecureString -AsPlainText -Force

Add-AzSqlManagedInstanceTransparentDataEncryptionCertificate -ResourceGroupName $ResourceGroupName -ManagedInstanceName $SQLMIName -PrivateBlob $securePrivateBlob -Password $securePassword

More coming ...