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

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 using PowerShell.


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

Step 0 – Prerequisite: The Azure 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 = “

<# 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 = “

<# Enter your SQL Server Trusted Login Domain/login here #>
$Login = “[DOMAINlogin]”

<# Enter the local Drive:PathSubfolder 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 #>

Step 10: Select the Azure subscription for the Key Vault

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
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.

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//resourceGroups/ContosoDevRG/prov
Vault URI                        :
Tenant ID                        :  
SKU                              : Standard
Enabled For Deployment?          : False
Enabled For Template Deployment? : False
Enabled For Disk ?     : False
Soft Delete Enabled?             :
Access Policies                  :
Tenant 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) : delete, deletesas, get, getsas, list, listsas, regeneratekey, set, setsas, update, recover, backup, restore
Tenant 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) :
Rule Set                       :
Default Action                         : Allow
Bypass                                 : AzureServices
IP Rules                               :
Virtual 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 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 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:


This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.