%3CLINGO-SUB%20id%3D%22lingo-sub-1425424%22%20slang%3D%22en-US%22%3EPublic%20preview%3A%20TDE%20with%20customer%20managed%20keys%20on%20Azure%20SQL%20Hyperscale%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425424%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20SQL%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Frelational-databases%2Fsecurity%2Fencryption%2Ftransparent-data-encryption%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETransparent%20Data%20Encryption%20(TDE)%3C%2FA%3E%26nbsp%3B%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Ftransparent-data-encryption-byok-azure-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ecustomer-managed%20keys%3C%2FA%3E%20enables%20customers%20to%20control%20the%20key%20lifecycle%20management%2C%20permissions%20and%20operations%20of%20keys%20stored%20in%20their%20own%20Azure%20Key%20Vault.%20We%20are%20announcing%20the%20%3CSTRONG%3Epublic%20preview%3C%2FSTRONG%3E%20of%20TDE%20with%20customer-managed%20keys%20on%20Azure%20SQL%20Database%20Hyperscale.%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1328517585%22%20id%3D%22toc-hId--1328517585%22%3EQuick%20start%20instructions%20with%20Azure%20CLI%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Assign%20Azure%20AD%20identity%20to%20your%20server.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIf%20it%20is%20a%20new%20server%20you%20are%20creating%2C%20then%20you%20can%20assign%20an%20AAD%20identity%20while%20creating%20it%20via%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fcli%2Fazure%2Fsql%2Fserver%3Fview%3Dazure-cli-latest%23az-sql-server-create%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eaz%20sql%20create%3C%2FA%3E%3CBR%20%2F%3E%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3Eaz%20sql%20server%20create%20--name%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%20%20--location%20%3CLOCATION%3E%20--admin-user%20%3CUSER%3E%20--admin-password%20%3CPASSWORD%3E%20--assign-identity%3C%2FPASSWORD%3E%3C%2FUSER%3E%3C%2FLOCATION%3E%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLI%3E%0A%3CLI%3EIf%20it%20is%20an%20existing%20server%20can%20use%20-ERR%3AREF-NOT-FOUND-az%20sql%20server%20update.%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3Eaz%20sql%20server%20update%20--name%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%20%20--location%20%3CLOCATION%3E%20--admin-user%20%3CUSER%3E%20--admin-password%20%3CPASSWORD%3E%20--assign-identity%3C%2FPASSWORD%3E%3C%2FUSER%3E%3C%2FLOCATION%3E%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BYou%20will%20need%20the%20principalId%20from%20the%20commands%20above%20to%20be%20able%20to%20grant%20permissions%20in%20step%204.%20Example%20output%20is%20below%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%26nbsp%3B%3CEM%3E%22identity%22%3A%20%7B%3C%2FEM%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%22principalId%22%3A%20%22bbbbbfsfs-2fde-4890-a7ce-aaaaaaaaaaa%22%2C%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CEM%3E%22tenantId%22%3A%20%22aaaaaf-aaf1-41af-aaab-2d7cd0bbbbbb%22%2C%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E%22type%22%3A%20%22SystemAssigned%22%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3E%7D%2C%3C%2FEM%3E%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%26nbsp%3B%20Create%20a%20test%20Hyperscale%20database%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eaz%20sql%20db%20create%20--name%20%3CDBNAME%3E%20--server%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%20-e%20Hyperscale%20-f%20Gen5%20-c%202%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FDBNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3.%20Create%20a%20Key%20vault%20in%20the%20same%20region%20as%20your%20database%20server%20and%20a%20key%20if%20you%20do%20not%20already%20have%20one%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eaz%20keyvault%20create%20--name%20%3CKEYVAULTNAME%3E%20--resource-group%20%3CRGNAME%3E%20--location%20%3CLOCATION%3E%20--enable-soft-delete%20true%0Aaz%20keyvault%20key%20create%20--name%20%3CKEYNAME%3E%20--vault-name%20%3CKEYVAULTVNAME%3E%20--protection%20software%0A%3C%2FKEYVAULTVNAME%3E%3C%2FKEYNAME%3E%3C%2FLOCATION%3E%3C%2FRGNAME%3E%3C%2FKEYVAULTNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.%20Grant%20Key%20vault%20permissions%20to%20your%20server%20Identity%20using%20the%20principalId%20from%20step1.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eaz%20keyvault%20set-policy%20--name%20%3CKEYVAULTVNAME%3E%20--object-id%20%3CPRINCIPALID%3E%20--resource-group%20%3CRGNAME%3E%20--key-permissions%20wrapKey%20unwrapKey%20get%3C%2FRGNAME%3E%3C%2FPRINCIPALID%3E%3C%2FKEYVAULTVNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E5.%20Obtain%20the%20kid%20from%20the%20key%20that%20was%20created%20in%20step%203%20using%20the%20command%20below.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eaz%20keyvault%20key%20show%20--vault-name%20%3CKEYVAULTNAME%3E%20--name%20%3CKEYNAME%3E%3C%2FKEYNAME%3E%3C%2FKEYVAULTNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESnippet%20of%20output%3A%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%22kid%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fmykeyvault.vault.azure.net%2Fkeys%2Fhstdebyok%2Faaaaaa0384cccc3aef28e04bbbbbb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmykeyvault.vault.azure.net%2Fkeys%2Fhstdebyok%2Faaaaaa0384cccc3aef28e04bbbbbb%3C%2FA%3E%22%2C%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E6.%20Add%20the%20Key%20vault%20key%20to%20the%20server%20and%20set%20the%20TDE%20protector%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eaz%20sql%20server%20key%20create%20--server%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%20--kid%20%3CKEYID%3E%0Aaz%20sql%20server%20tde-key%20set%20--server%20%3CSERVERNAME%3E%20--server-key-type%20AzureKeyVault%20%20--resource-group%20%3CRGNAME%3E%20--kid%20%3CKEYID%3E%3C%2FKEYID%3E%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FKEYID%3E%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E7.%20Enable%20TDE%20at%20the%20database%20level.%20Note%20that%20the%20encryption%20could%20take%20a%20while%20depending%20on%20the%20database%20size%20and%20the%20tier.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eaz%20sql%20db%20tde%20set%20--database%20%3CDBNAME%3E%20--server%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%20--status%20Enabled%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FDBNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E8.%20Check%20Encryption%20state%20and%20activity%20using%20CLI%20or%20powershell%20or%20TSQL%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%3CSTRONG%3ECLI%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3Eaz%20sql%20db%20tde%20list-activity%20--database%20%3CDBNAME%3E%20--server%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%20%20%0A%23%20get%20whether%20encryption%20is%20on%20or%20off%0Aaz%20sql%20db%20tde%20show%20--database%20%3CDBNAME%3E%20--server%20%3CSERVERNAME%3E%20--resource-group%20%3CRGNAME%3E%0A%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FDBNAME%3E%3C%2FRGNAME%3E%3C%2FSERVERNAME%3E%3C%2FDBNAME%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20TSQL%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-database-encryption-keys-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esys.dm_database_encryption_keys%3C%2FA%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--%20encryptor_type%20will%20be%20ASYMMETRIC%20KEY%0ASELECT%20database_id%2Cencryptor_type%2Cencryption_state%2C%20encryption_scan_state%2Cpercent_complete%0Akey_algorithm%2C%20encryptor_thumbprint%0AFROM%20sys.dm_database_encryption_keys%0AWHERE%20database_id%20%3D%20DB_ID()%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20more%20information%20please%20see%20-ERR%3AREF-NOT-FOUND-requirements%20and%20recommendations%20for%20configuration%20customer-managed%20TDE%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1158995248%22%20id%3D%22toc-hId-1158995248%22%3ELimitations%3C%2FH2%3E%0A%3CP%3E%3CSTRIKE%3EAzure%20SQL%20databases%20that%20have%20TDE%20with%20customer-managed%20keys%20on%20other%20tiers%20will%20be%20not%20able%20to%20update%20to%20Hyperscale%20tier%20and%20will%20fail%20with%20the%20error%20below%3A%3C%2FSTRIKE%3E%3C%2FP%3E%0A%3CP%3E%3CSTRIKE%3E%3CEM%3EError%20message%3A%20'The%20logical%20server%20is%20using%20customer%20managed%20key%20(AKV)%2C%20switch%20to%20service%20managed%20key%20before%20moving%20to%20'Hyperscale'%20edition.%20Update%20to%20service%20objective%20'HS_Gen5_2'%20with%20customer%20managed%20key%20is%20not%20supported'%20for%20entity%20'dbname%3C%2FEM%3E'.%3C%2FSTRIKE%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRIKE%3EWe%20will%20be%20addressing%20this%20before%20General%20availability%20of%20the%20feature.%3C%2FSTRIKE%3E%26nbsp%3BUpgrades%20of%20databases%20with%20customer%20managed%20keys%20are%20now%20available.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20us%20know%20your%20experiences%20as%20you%20try%20this%20out%20on%20hyperscale!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1425424%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20are%20announcing%20the%20public%20preview%20of%20TDE%20with%20customer-managed%20keys%20on%20Azure%20SQL%20Database%20Hyperscale.%20Azure%20SQL%26nbsp%3B-ERR%3AREF-NOT-FOUND-Transparent%20Data%20Encryption%20(TDE)%26nbsp%3B%20with%20-ERR%3AREF-NOT-FOUND-customer-managed%20keys%20enables%20customers%20to%20control%20the%20key%20lifecycle%20management%2C%20permissions%20and%20operations%20of%20keys%20stored%20in%20their%20own%20Azure%20Key%20Vault.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1425424%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ETransparent%20Data%20Encryption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Azure SQL Transparent Data Encryption (TDE)  with customer-managed keys enables customers to control the key lifecycle management, permissions and operations of keys stored in their own Azure Key Vault. We are announcing the public preview of TDE with customer-managed keys on Azure SQL Database Hyperscale.

Quick start instructions with Azure CLI

 

1. Assign Azure AD identity to your server.

 

  • If it is a new server you are creating, then you can assign an AAD identity while creating it via az sql create
    az sql server create --name <servername> --resource-group <rgname>  --location <location> --admin-user <user> --admin-password <password> --assign-identity
  • If it is an existing server can use az sql server update.
    az sql server update --name <servername> --resource-group <rgname>  --location <location> --admin-user <user> --admin-password <password> --assign-identity

       You will need the principalId from the commands above to be able to grant permissions in step 4. Example output is below

 "identity": {
"principalId": "bbbbbfsfs-2fde-4890-a7ce-aaaaaaaaaaa",
"tenantId": "aaaaaf-aaf1-41af-aaab-2d7cd0bbbbbb",
"type": "SystemAssigned"
},
 

2.  Create a test Hyperscale database

 

 

az sql db create --name <dbname> --server <servername> --resource-group <rgname> -e Hyperscale -f Gen5 -c 2

 

 

3. Create a Key vault in the same region as your database server and a key if you do not already have one

 

 

az keyvault create --name <keyvaultname> --resource-group <rgname> --location <location> --enable-soft-delete true
az keyvault key create --name <keyname> --vault-name <keyvaultvname> --protection software

 

 

4. Grant Key vault permissions to your server Identity using the principalId from step1.

 

 

az keyvault set-policy --name <keyvaultvname> --object-id <principalID> --resource-group <rgname> --key-permissions wrapKey unwrapKey get

 

 

5. Obtain the kid from the key that was created in step 3 using the command below.

 

 

az keyvault key show --vault-name <keyvaultname> --name <keyname>

 

 

Snippet of output:

"kid": "https://mykeyvault.vault.azure.net/keys/hstdebyok/aaaaaa0384cccc3aef28e04bbbbbb",

 

6. Add the Key vault key to the server and set the TDE protector

 

 

az sql server key create --server <servername> --resource-group <rgname> --kid <keyID>
az sql server tde-key set --server <servername> --server-key-type AzureKeyVault  --resource-group <rgname> --kid <keyID>

 

 

7. Enable TDE at the database level. Note that the encryption could take a while depending on the database size and the tier.

 

 

az sql db tde set --database <dbname> --server <servername> --resource-group <rgname> --status Enabled

 

 

8. Check Encryption state and activity using CLI or powershell or TSQL

    CLI:

 

 

az sql db tde list-activity --database <dbname> --server <servername> --resource-group <rgname>  
# get whether encryption is on or off
az sql db tde show --database <dbname> --server <servername> --resource-group <rgname>

 

 

TSQL : sys.dm_database_encryption_keys

 

 

-- encryptor_type will be ASYMMETRIC KEY
SELECT database_id,encryptor_type,encryption_state, encryption_scan_state,percent_complete
key_algorithm, encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID()
GO

 

 

For more information please see requirements and recommendations for configuration customer-managed TDE

Limitations

Azure SQL databases that have TDE with customer-managed keys on other tiers will be not able to update to Hyperscale tier and will fail with the error below:

Error message: 'The logical server is using customer managed key (AKV), switch to service managed key before moving to 'Hyperscale' edition. Update to service objective 'HS_Gen5_2' with customer managed key is not supported' for entity 'dbname'.

 

We will be addressing this before General availability of the feature. Upgrades of databases with customer managed keys are now available.

 

Let us know your experiences as you try this out on hyperscale!