Prepare and deploy an Azure SQL Server for Always Encrypted



Hi Azure friends,


I used the PowerShell ISE for this configuration. But you are also very welcome to use Visual Studio Code, just as you wish. Please start with the following steps to begin the deployment (the Hashtags are comments):


#The first two lines have nothing to do with the configuration, but make some space below in the blue part of the ISE

Set-Location C:\Temp


#So that you can carry out the configuration, you need the necessary cmdlets, these are contained in the module Az (is the higher-level module from a number of submodules)

Install-Module -Name Az -Force -AllowClobber -Verbose


#For this example I am using passwords in the script. This is usually not a good idea, but it's fine for the demo.


#Prefix for resources
$prefix = "tw"

#Log into Azure

#Select the correct subscription



Get-AzSubscription -SubscriptionName "your subscription name" | Select-AzSubscription

#Let's create a SQL Server that we will use Always Encrypted with
$Location = "westeurope"
$id = Get-Random -Minimum 1000 -Maximum 9999
$ResourceGroupName = "$prefix-sql-ae-$id"
$SQLServerName = "$prefix-sql-$id"
$SQLDatabaseName = "Hospital"
$SQLAdmin = "sqladmin"
$SQLAdminPassword = ConvertTo-SecureString -String 'P@ssw0rd999!' -AsPlainText -Force
$SQLAdminCredentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SQLAdmin,$SQLAdminPassword
#We need our public IP (SQL Firewall configuration)

#Create a resource group for the SQL Server
$sqlAeRG = New-AzResourceGroup -Name $ResourceGroupName -Location $Location

#Now we need to create a Key Vault to use with the SQL Server
$keyVaultParameters = @{
    Name = "$prefix-key-vault-$id"
    ResourceGroupName = $sqlAeRG.ResourceGroupName
    Location = $location
    EnabledForDiskEncryption = $true
    EnabledForDeployment = $true
    Sku = "Standard"
$keyVault = New-AzKeyVault @keyVaultParameters

#Now create an AAD SPN to use with Console App
Import-Module Az.Resources # Imports the PSADPasswordCredential object
$credProps = @{
    StartDate = Get-Date
    EndDate = (Get-Date -Year 2024)
    Password = '9MPG7j2MAH3fEveE58vxxg0ghjo9sEutitv9jBeyjfqTLpb9sGBhXQSY9yn2' #Or generate your own, avoid special characters
$credentials = New-Object Microsoft.Azure.Commands.ActiveDirectory.PSADPasswordCredential -Property $credProps
$sp = New-AzAdServicePrincipal -DisplayName $SQLServerName -PasswordCredential $credentials

#Grant the SPN access to the Key Vault
$accessPolicy = @{
    VaultName = $keyVault.VaultName
    ServicePrincipalName = $sp.ApplicationId
    PermissionsToKeys = @("wrapKey","unwrapKey","get","recover","sign","verify","list")
Set-AzKeyVaultAccessPolicy @accessPolicy

#Grant yourself proper access to the Key Vault
$accessPolicyUPN = @{
    VaultName = $keyVault.VaultName
    UserPrincipalName = ""
    PermissionsToKeys = @("create","wrapKey","unwrapKey","get","recover","sign","verify","list")

Set-AzKeyVaultAccessPolicy @accessPolicyUPN

#Create an Azure SQL Server
$sqlServerParameters = @{
    ResourceGroupName = $sqlAeRG.ResourceGroupName
    Location = $Location
    ServerName = $SQLServerName
    SqlAdministratorCredentials = $SQLAdminCredentials

$sqlServer = New-AzSqlServer @sqlServerParameters

#Create a Firewall rule allowing you to connect with SSMS
$sqlFirewallParameters = @{
    ResourceGroupName = $sqlAeRG.ResourceGroupName
    ServerName = $sqlServer.ServerName
    FirewallRuleName = "MyIPAddress"
    StartIpAddress = $MyIPAddress
    EndIpAddress = $MyIPAddress

$sqlFirewall = New-AzSqlServerFirewallRule @sqlFirewallParameters

#Create a database for the application
$databaseParameters = @{
    ResourceGroupName = $sqlAeRG.ResourceGroupName
    ServerName = $sqlServer.ServerName
    DatabaseName = $SQLDatabaseName
    RequestedServiceObjectiveName = "S0"
    SampleName = "AdventureWorksLT" 

$database = New-AzSqlDatabase @databaseParameters

#Now connect to SQL DB with SQL Server Management Studio (SSMS) and configure Always Encrypted
Now you have used the PowerShell to create Azure SQL Server (Which can now be configured with Always Encrypted)! Congratulations!
#Clean up (when you no longer need the resources)
Remove-AzResourceGroup -Name $ResourceGroupName -Force

I hope this article was useful. Best regards, Tom Wechsler


P.S. All scripts (#PowerShell, Azure CLI, #Terraform, #ARM) that I use can be found on github!

0 Replies