Part 2 (PowerShell) – SQL Server TDE and Extensible Key Management Using Azure Key Vault

This post has been republished via RSS; it originally appeared at: SQL Server articles.

Set up an Azure Active Directory Service Principal and Azure Key Vault using PowerShell

This is Part: PS2 (PowerShell) of a 4-part blog series:

This blog in the series provides the step-by-step instructions to configure Azure Active Directory using PowerShell.


To grant SQL Server access permissions to your Azure Key Vault (AKV), you will need a Service Principal account in Azure Active Directory (AAD).

Step 0 - Prerequisite: The Azure Active Directory PowerShell Module must be installed before these commands will work.


<# Step 0: Install Azure AD PowerShell module if it is not already installed #> Install-Module AzureAD


Step 1 – Set variables: The first step in successfully running PowerShell configuration is to setup variables that can be used throughout the PowerShell script. We want to set these at the top, and they will be used for the various commands in the PowerShell script in this blog.


<# Step 1: Enter script variables #> <# Enter the name of your Azure Subscription Here#> $AzureSubscription = "<YOUR SUBRIPTION NAME HERE>" <# Enter the Unique Name of your AAD Application you will register here#> $appName = "MyAAD-EKM-AKV-Demo" <# Enter the TenantID (GUID) from your subscription here #> $TenantId = "11111111-2222-3333-4444-1234567890ab" <# Enter the name of your Server/Instance here#> $SQLServerInstance = "<SQLServer>\<InstanceName>" <# Enter your SQL Server Trusted Login Domain/login here #> $Login = "[DOMAIN\login]" <# Enter the local Drive:Path\Subfolder\ for scripts: leave trailing backslash "\" #> $SQLScriptFileDir = "C:\AzureSQLDB\" <# Enter the name of a New or existing Resource Group here#> $RGName = 'ContosoDevRG' <# Enter the Location for your Resource Group here (ie: West US, Japan East) #> $Location = 'East Asia' <#######################################################> <# derived variables are setup using the user defined variables #> <# derived variables – do not need modification #> $VaultName = $appName + "KeyVault" <# clean up Vault name to trim to 26 characters #> $VaultName = ("$VaultName").Substring(0,26) <# build the name for the AKV Key#> $VaultKeyName = $appName + "RSAKey" <# Set variables to output the variable names for SQLCMD (to be used in Part 4) #> $SQLCMDFile = $SQLScriptFileDir + "\AAD-AKV-SQL_setvar.txt" <# add the path to the SQLCMD location #> $SQLScriptFile = $SQLScriptFileDir + "\PS_EKM_Setup_TSQL.sql" <# additional variables used throughout PS script #> $appNameApp = $appName + "App" $appURI = "https://$appNameApp.$" $appHomePageUrl = "http://www.$" $appReplyURLs = @($appURI, $appHomePageURL, "https://localhost:1234")


Step 2 – Connect to Azure: this will bring up a login screen dialog box that you can use to connect to Azure.


<# AAD Step 2: Connect to Azure Active Directory #> Connect-AzureAD -TenantId $TenantId


Step 3 – Create a New Azure Active Directory Application: this is your application, which is nothing more than a name that you register that will allow you to tie your SQL Server to Azure Key Vault.


<# AAD Step 3: Create a New-AzureADApplication #> New-AzureADApplication -DisplayName $appName `                        -IdentifierUris $appURI `                        -Homepage $appHomePageUrl `                        -ReplyUrls $appReplyURLs


Step 4 – Create Azure Active Directory Password Credential: Creates a password credential for an application or if it already exists, returns the Secret, ObjectID, AppID and builds the AppSecret (modified AppID + Secret) by removing the dashes from the AppID GUID to be used by SQL Server for Secret in the Credential.


<# AAD Step 4: Create a New-AzureADApplicationPasswordCredential #> $Secret = "$((New-AzureADApplicationPasswordCredential -ObjectId `          "$((Get-AzureADApplication -Filter "DisplayName eq '$appName'").ObjectId)" `          -CustomKeyIdentifier $appNameKey ).Value)" $ObjId = "$((Get-AzureADApplication -Filter "DisplayName eq '$appName'").ObjectId)" $AppId = "$((Get-AzureADApplication -Filter "DisplayName eq '$appName'").AppId)" $AppSecret = ("$AppId").Replace("-", "") + $Secret <# remove dashes #>


Step 5 – Create Azure Active Directory Service Principal:  The Service Principal is needed for Azure Key Vault.


<# AAD Step 5: Set the AAD Service Principal which is needed for the AKV#> New-AzADServicePrincipal -ApplicationId $AppId


Step 6: Display output variables to the screen:


<# AAD Step 6: Display information that is needed for the AKV#> Write-Host "AppId    : " $AppId Write-Host "ObjectId : " $ObjId Write-Host "Value    : " $Secret Write-Host "SQLSecret: " $AppSecret


Step 7: Set the output variables: To take advantage of PowerShell to complete Blog Part 4, the variables can be saved to a txt file. Here we generate a txt file that has the TSQL - SQLCMD parameters to setup TDE with EKM using Azure Key Vault.


<# AAD Step 7: Generate a txt file that has the TSQL - SQLCMD parameters #> $Output = ":setvar AzureKeyVault   """ + $VaultName + """ :setvar AppDisplayName  """ +  $appNameApp + """ :setvar AppID           """ +  ("$AppId").Replace("-", "") + """ :setvar Secret          """ +  $Secret + """ :setvar AzKeyVaultName  """ +  $VaultKeyName + """ :setvar Login           ""$Login" + """"


Step 8: Write output variables to a file: After the variables are saved to the $output variable, we can export the formatted setvar variables to a text file (after the path has been changed to the location of the SQLCMD path).


<# AAD Step 8: Generate a SQLCMD variable file that has the TSQL - SQLCMD parameters #> CD $SQLScriptFileDir $Output | Out-File -FilePath $SQLCMDFile


Step 9: Sign in: If you are not already signed in. Sign into your Azure account with the following command:


<# AKV Step 1: Connect to the Azure Account #> Connect-AzAccount  


Step 10: Select the Azure subscription for the Key Vault


[NOTE] If you have multiple subscriptions and want to specify a specific one to use for the vault, then use Get-AzSubscription to see the subscriptions and Select-AzSubscription to choose the correct subscription. Otherwise, PowerShell will select one for you by default.<# AKV Step 2: Select-Azure Subscription #> Select-AzSubscription $AzureSubscription


Step 11: Create a new Resource Group: All Azure resources created via Azure Portal must be contained in resource groups. Create a resource group to house your key vault. This example uses ContosoDevRG. Choose your own unique resource group and key vault name as all key vault names are globally unique.


<# AKV Step 3: Create a New-Azure Resource Group (if not already exist) #> New-AzResourceGroup -Name $RGName -Location $Location[!NOTE] For the -Location parameter, use the command Get-AzureLocation to identify how to specify an alternative location to the one in this example. If you need more information, type: Get-Help Get-AzureLocation


Step 12: Create a Key Vault: The New-AzKeyVault cmdlet requires a resource group name, a key vault name, and a geographic location. For example, for a key vault named AAD-AKV-DemoKeyVault , type:


<# AKV Step 5: Create a new Azure Key Vault #> New-AzKeyVault -VaultName $VaultName -ResourceGroupName $RGName -Location $Location


Step 13: Grant Permission for the Azure Active Directory Service Principal to Access the Key Vault: You can authorize other users and applications to use your key vault.
In this case, let's use the Azure Active Directory service principal created in Step 5 to authorize the SQL Server instance.


[IMPORTANT] The Azure Active Directory service principal must have at least the get, list, wrapKey, and unwrapKey permissions for the key vault.


Use the App (Client) ID from Step 6 for the  ServicePrincipalName  parameter. The Set-AzKeyVaultAccessPolicy runs silently with no output if it runs successfully.


<# AKV Step 6: Set-Azure Key Vault Access Policy #> Set-AzKeyVaultAccessPolicy -VaultName $VaultName `           -PermissionsToKeys get, wrapKey, unwrapKey, list ` -ServicePrincipalName $AppId  <# App(Client)ID from AAD #>


Step 14: Validate the Key Vault has been created: Call the Get-AzKeyVault cmdlet to confirm the permissions. In the statement output under 'Access Policies,' you should see your Azure Active Directory (AAD) application name listed as another tenant that has access to this key vault.


<# AKV Step 6: Verify the AKV exists #> Get-AzKeyVault -VaultName $VaultName


The statement returns:


Vault Name                       : MyAAD-EKM-AKV-DemoKeyVault Resource Group Name              : ContosoDevRG Location                         : East Asia Resource ID                      : /subscriptions/<subscription_id>/resourceGroups/ContosoDevRG/prov                                    iders/Microsoft.KeyVault/vaults/MyAAD-EKM-AKV-DemoKeyVault Vault URI                        : Tenant ID                        : <tenant_id>  SKU                              : Standard Enabled For Deployment?          : False Enabled For Template Deployment? : False Enabled For Disk Encryption?     : False Soft Delete Enabled?             : Access Policies                  : Tenant ID                              : <tenant_id> Object ID                              : <object_id> Application ID                         : Display Name                           : FisrtName LastName ( Permissions to Keys                    : get, create, delete, list, update, import, backup, ... Permissions to Secrets                 : get, list, set, delete, backup, restore, recover         Permissions to Certificates            : get, delete, list, create, import, update, deleteissuers, getissuers, listissuers, managecontacts, manageissuers, setissuers, recover, backup, restore Permissions to (Key Vault Managed) Storage : delete, deletesas, get, getsas, list, listsas, regeneratekey, set, setsas, update, recover, backup, restore Tenant ID                              : <tenant_id> Object ID                              : <object_id> Application ID                         : Display Name                           : MyAAD-EKM-AKV-Demo Permissions to Keys                    : get, wrapKey, unwrapKey, list Permissions to Secrets                 : Permissions to Certificates            : Permissions to (Key Vault Managed) Storage : Network Rule Set                       : Default Action                         : Allow Bypass                                 : AzureServices IP Rules                               : Virtual Network Rules                  : Tags                                   : 


Step 15: Generate an Asymmetric Key in the Key Vault

There are two ways to generate a key in Azure Key Vault:

  • Create a new key.
  • Import an existing key.


[NOTE] SQL Server only supports 2048-bit RSA keys.


Create a new key with PowerShell

You can create a new encryption key directly in Azure Key vault and have it be either software-protected or HSM-protected. In this example, let's create a software-protected key using the Add-AzureKeyVaultKey cmdlet:


<# AKV Step 7: Add-Azure Key Vault Key to the Key Vault #> Add-AzKeyVaultKey -VaultName $VaultName -Name $VaultKeyName -Destination 'Software' 



Configuring Azure Active Directory and Azure Key Vault are the second and third step respectively in configuring SQL Server TDE to use Azure Key Vault. Continue the setup process for SQL Server using SSMS or SQLCMD in Blog Part 4 . 


 See you at the next blog (Part: 4) 



Next steps

SQL Server Transparent Data Encryption and Extensible Key Management Using Azure Key Vault – Intro

SQL Server Connector for Microsoft Azure Key Vault (aka: SQL Server Connector) – Part: 1

Azure Portal Method

PowerShell Method

Set up an Azure Active Directory Service Principal – Part: AP2

Setup Azure Active Directory Service Principal and  Azure Key Vault (one script) – Part: PS2 (this document)

This script combines Part: AP2 & Part:AP3

Create an Azure Key Vault – Part: AP3

Configure SQL Server TDE EKM using AKV – Part: 4


Download the scripts for PowerShell and SQLCMD here:

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.