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.$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.
<# 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 : https://myaad-ekm-akv-demokeyvault.vault.azure.net/
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 (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
Tenant ID : <tenant_id>
Object ID : <object_id>
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 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'
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 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: