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 instructions to configure Azure using PowerShell.

Adrian_Rupp_0-1590519409809.png

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


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.




$appName = “MyAAD-EKM-AKV-Demo”


$TenantId = “11111111-2222-3333-4444-1234567890ab”



$Login = “[DOMAINlogin]”


$SQLScriptFileDir = “C:AzureSQLDB”


$RGName = ‘ContosoDevRG'


$Location = ‘East Asia'




$VaultName = $appName + “KeyVault”


$VaultName = (“$VaultName”).Substring(0,26)


$VaultKeyName = $appName + “RSAKey”


$SQLCMDFile = $SQLScriptFileDir + “AAD-AKV-SQL_setvar.txt”


$SQLScriptFile = $SQLScriptFileDir + “PS_EKM_Setup_TSQL.sql”


$appNameApp = $appName + “App”
$appURI = “https://$appNameApp.$appName.onmicrosoft.com”
$appHomePageUrl = “http://www.$appName.com/”
$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.


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 to Azure Key Vault.


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 for Secret in the Credential.


$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)”

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


New-AzADServicePrincipal -ApplicationId $AppId

Step 6: Display output variables to the screen:


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.


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


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:


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.

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.


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:


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.


Set-AzKeyVaultAccessPolicy -VaultName $VaultName `
          -PermissionsToKeys get, wrapKey, unwrapKey, list `

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.


Get-AzKeyVault -VaultName $VaultName

The statement returns:

Vault Name                       : MyAAD-EKM-AKV-DemoKeyVault
Resource Group Name              : ContosoDevRG
Location                         : East Asia

                                   iders/Microsoft.KeyVault/vaults/MyAAD-EKM-AKV-DemoKeyVault
Vault URI                        : https://myaad-ekm-akv-demokeyvault.vault.azure.net/

SKU                              : Standard
Enabled For Deployment?          : False
Enabled For Template Deployment? : False
Enabled For Disk ?     : False
Soft Delete Enabled?             :
Access Policies                  :


Application ID                         :
Display Name                           : FisrtName LastName (user@domain.com)
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


Application ID                         :
Display Name                           : MyAAD-EKM-AKV-Demo https://MyAAD-EKM-AKV-DemoApp.MyAAD-EKM-AKV-Demo.onmicrosoft.com)
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 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:


Add-AzKeyVaultKey -VaultName $VaultName -Name $VaultKeyName
-Destination ‘Software' 

Conclusion

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

Adrian

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.