Azure Key Vault, Certificates and SQL Server Encryption: A Hands-On Approach

This is a complete example using a hands-on approach of how to use Azure Key Vault to store certificates and using those certificates to create Always Encrypted columns in Azure SQL.

I am assuming that you have an Azure subscription, otherwise you can create a free account to follow along.

Prerequisites:

1. Azure Subscription
2. PowerShell 5.1 +
3. Azure PowerShell Module
4. SQL Server PowerShell Module
5. PowerShell ISE or any other Editor

Step-by-step:

[Step#0] We can use Azure Portal or PowerShell to collect all the required information beforehand and use these values for a non-interactive login to Azure.

# Use Azure Portal/PowerShell to collect required information
Connect-AzAccount 
$azContext      = Get-AzContext
$tenantId       = $azContext.Tenant
$subscriptionId = $azContext.Subscription.SubscriptionId
$objectid       = $azContext.Subscription.Id

[Step#1] First, we need to login to the Azure cloud. Use the following script and change parameters as per your subscription:

<################################################
# Connect to the Azure cloud
################################################>

$azUser = "myemail@hotdogmail.com"
$azPassword = ConvertTo-SecureString -String "myAzurePa$$word" -AsPlainText -Force
$tenantID = "953c73eb-391d-4c0b-b35f-72204d112547"
$subscriptionID = "f361f3cd-39f9-471e-aba7-3f92b8348c0b"

$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $azUser,$azPassword
Connect-AzAccount -Credential $Credential -Tenant $tenantID -Subscription $subscriptionID

 

[Step#2] Now we will need a logical SQL database server to hold our sample database. Therefore, we need to create an Azure Resource group and open up the firewall to connect from outside of Azure Cloud. Change the variables as necessary. Note that the database server name must be unique. Use the following script:

<################################################
# Create a resource group
################################################>
$rgName ="shb-azure-rg-01" # resource group name
$location = "eastus" # $location = "East US"
New-AzResourceGroup -Name $rgName -Location $location

<################################################
# Create a logical Azure SQL Server
################################################>
$serverName         = "shb-az-db-01"
$sqlAdmin           = "sqladmin"
$sqlPassword        = "Winter2020"
$sqlPasswordSecure  = $(ConvertTo-SecureString -String $sqlPassword -AsPlainText -Force) 
$SqlAdminCredential = $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlAdmin, $sqlPasswordSecure )

$newServer = New-AzSqlServer -ResourceGroupName $rgName `
    -Location $location `
    -ServerName $serverName `
    -SqlAdministratorCredentials $SqlAdminCredential

<################################################
# Open Azure server firewall to allow access 
# Replace the public IP addresses bellow 
################################################>
$newStartIP = $((Invoke-RestMethod http://ipinfo.io/json).ip)
$newEndIP   = $((Invoke-RestMethod http://ipinfo.io/json).ip)

$newFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $rgName `
    -ServerName $serverName `
    -FirewallRuleName "Allow_Public_IP" -StartIpAddress $newStartIP -EndIpAddress $newEndIP

<################################################
# Create a new database by specifying a service tier 
# Get-AzSqlServerServiceObjective -Location $location
################################################>
$databaseName = "TestDB"
$newDatabase = New-AzSqlDatabase  -ResourceGroupName $rgName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -RequestedServiceObjectiveName "Free"

[Step#3] Once the Azure SQL server is created, use SSMS to login to the database server and then create the sample database and table. You can insert some records as well.

USE [TestDB]
GO

CREATE TABLE [dbo].[EmpTbl](
	[EmpID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[SSN] [varchar](20) NULL, 
	[Salary] [money] NULL,
	[email] [varchar](50) NULL,
 CONSTRAINT [PK_EmpTbl] PRIMARY KEY CLUSTERED (	[EmpID] ASC ) 
) 
GO

SET IDENTITY_INSERT TestDB.dbo.EmpTbl ON
GO
INSERT TestDB.dbo.EmpTbl(EmpID, Name, SSN, Salary, email) VALUES (1, 'Greg', '456-65-3369', 500.00, 'greg@domain.com')
INSERT TestDB.dbo.EmpTbl(EmpID, Name, SSN, Salary, email) VALUES (2, 'David', '415-70-6978', 600.00, 'david@hisdomain.org')
INSERT TestDB.dbo.EmpTbl(EmpID, Name, SSN, Salary, email) VALUES (3, 'Bart', '545-98-7854', 550.00, 'bart@taste.net')
GO
SET IDENTITY_INSERT TestDB.dbo.EmpTbl OFF
GO

SELECT * FROM [dbo].EmpTbl

[Step#4] We need an Azure Key vault to store certificates which will be used to encrypt columns in the SQL Server table. The estimated time for the Key Vault creation process is approximately 2 minutes. Note that since we are using a Personal Account/Microsoft account (e.g. Outlook, Hotmail account) in our Azure AD tenant, there will be two warning messages. We can safely ignore these messages. However, to suppress these two warnings, I have added -WarningAction Ignore. Following are the two warning messages you may encounter:

WARNING: The provided information does not map to an AD object id.

WARNING: Access policy is not set. No user or application have access permission to use this vault. This can happen if the vault was created by a service principal. Please use Set-AzKeyVaultAccessPolicy to set access policies. Use the following script to create the Key Vault and assign permissions:

<############################################################
# Create Key Vault and grant permission to the vault owner
############################################################>

$vaultName = 'vault480'  # key vault name

# get the object id of who is creating the vault
$objectid =$(Get-AzADUser)[0].Id

# Create a new vault
New-AzKeyVault `
    -VaultName $vaultName `
    -ResourceGroupName $rgName `
    -Location $location `
    -WarningAction Ignore

# let's wait to complete the deployment operation
sleep 120

# grant all permissions to the owner of the vault

# Key permissions
Set-AzKeyVaultAccessPolicy `
    -VaultName $vaultName `
    -ResourceGroupName $rgName `
    -ObjectId $objectid `
    -PermissionsToKeys wrapKey,unwrapKey,verify,sign,get,list,update,create,import,delete,backup,restore,recover,encrypt,decrypt,purge 

# Secrect permissions
Set-AzKeyVaultAccessPolicy `
    -VaultName $vaultName `
    -ResourceGroupName $rgName `
    -ObjectId $objectid `
    -PermissionsToSecrets get,list,set,delete,backup,restore,recover,purge

# Certificate permissions
Set-AzKeyVaultAccessPolicy `
     -VaultName $vaultName `
     -ResourceGroupName $rgName `
     -ObjectId $objectid `
     -PermissionsToCertificates get,list,delete,create,import,update,managecontacts,getissuers,listissuers,setissuers,deleteissuers,manageissuers,recover,purge,backup,restore

[Step#5] In this step, we can create a certificate or if we already have one, we can import the certificate to the Azure Key Vault. Use the following script to create a self-signed certificate or import an existing one:

<############################################################
# Create or import a certificate
############################################################>

# Create self-signed certificate
$vaultName = "vault480"  # Azure key vault name
$certName  = "SQLCert01" # Certificate name

# Create a self-signed certificate
$Policy = New-AzKeyVaultCertificatePolicy `
            -SecretContentType "application/x-pkcs12" `
            -SubjectName "CN=SQLAlwaysEnctypted" `
            -IssuerName "Self" `
            -ValidityInMonths 72 `
            -ReuseKeyOnRenewal

Add-AzKeyVaultCertificate `
            -VaultName $vaultName `
            -Name $certName `
            -CertificatePolicy $Policy

# Import Certificate to the Azure Keyvault from a local drive
$filePath       = "D:\AzureKeyVault\AlwaysAzureCert.pfx"
$certPassword   = ConvertTo-SecureString -String "YourPassword2020"-Force -AsPlainText

Import-AzKeyVaultCertificate `
        -VaultName $vaultName `
        -Name $certName `
        -FilePath $filePath `
        -Password $certPassword

# Verify the certificate
Get-AzKeyVaultCertificate -VaultName $vaultName -Name $certName

[Step#6] Now we need to create a CMK and a CEK from the certificate. Use the following script to create both keys:

# Make sure volt and certificate name is correct
$vaultName = "vault480"  # Azure key vault name
$certName  = "SQLCert01" # Certificate name

# Connect to your database.
$AzSQLServer    = "tcp:shb-az-db-01.database.windows.net,1433"
$sqlAdmin       = "sqladmin"
$sqlAdminPass   = "Winter2020"
$databaseName   = "TestDB"
$masterKey      = "CMK1"
$encryptionKey  = "CEK1"

# connect to the database and open the connection
$connStr = "Server = " + $AzSQLServer + "; Database = " + $databaseName + "; ` 
            Integrated Security = false;User ID=$sqlAdmin;Password=$sqlAdminPass"

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $connStr
$sqlConn.Open()

# If your encryption changes involve keys in Azure Key Vault, 

# uncomment one of the lines below in order to authenticate:
# Prompt for a username and password:
Add-SqlAzureAuthenticationContext -Interactive

# For Service principal, enter a Client ID, Secret, and Tenant ID:
#Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'

# get database smo object for the InputObject parameter 
$database = Get-SqlDatabase -ConnectionString $connStr

# Get certificate information from Azure key vault
$cert        = Get-AzKeyVaultCertificate -VaultName $vaultName -Name $certName
$CmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl $($cert.KeyId)

# Create Column Master Key
New-SqlColumnMasterKey `
            -Name $masterKey `
            -InputObject $database `
            -ColumnMasterKeySettings $CmkSettings

Sleep 10

# Create Column Encryption Key
New-SqlColumnEncryptionKey `
    -Name $encryptionKey `
    -InputObject $database `
    -ColumnMasterKeyName $masterKey

# close Connection
$sqlConn.close()

[Step#7] Use the following script to encrypt the columns:

# Connect to your database.
$AzSQLServer    = "tcp:shb-az-db-01.database.windows.net,1433"
$sqlAdmin       = "sqladmin"
$sqlAdminPass   = "Winter2020"
$databaseName   = "TestDB"

# connect to the database
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server = " + $AzSQLServer + "; Database = " + $databaseName + "; `
        Integrated Security = false;User ID=$sqlAdmin;Password=$sqlAdminPass"

# Open the connection
$sqlConn.Open()

# Name of the column encryption key 
$cekName = "CEK1"

# Encrypt the columns with CEK1 key
$columns = @()
$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.Name" -EncryptionType Deterministic -EncryptionKey $cekName
$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.SSN" -EncryptionType Deterministic -EncryptionKey $cekName
$database | Set-SqlColumnEncryption -ColumnEncryptionSettings $columns

# Close connection
$sqlConn.Close()

[Step#8] Use following script to decrypt one or more columns when you need to:

# Connect to your database.
$AzSQLServer    = "tcp:shb-az-db-01.database.windows.net,1433"
$sqlAdmin       = "sqladmin"
$sqlAdminPass   = "Winter2020"
$databaseName   = "TestDB"

# connect to the database
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server = " + $AzSQLServer + "; Database = " + $databaseName + "; `
        Integrated Security = false;User ID=$sqlAdmin;Password=$sqlAdminPass"

# Open the connection
$sqlConn.Open()

# Name of the column encryption key 
$cekName = "CEK1"

# Remove encryption from a column
$columns = @()
$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.Name" -EncryptionType PlainText 
$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.SSN" -EncryptionType PlainText 
$database | Set-SqlColumnEncryption -ColumnEncryptionSettings $columns

# Close connection
$sqlConn.Close()

[Step#9] Let’s insert some sample data to the encrypted columns. Notice, we need to use “Column Encryption Setting=Enabled” as a connection string option:

# Connect to your database.
$AzSQLServer    = "tcp:shb-az-db-01.database.windows.net,1433"
$sqlAdmin       = "sqladmin"
$sqlAdminPass   = "Winter2020"
$databaseName   = "TestDB"

# connect to the database
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server = " + $AzSQLServer + "; Database = " + $databaseName + "; `
        Integrated Security = false;User ID=$sqlAdmin;Password=$sqlAdminPass;Column Encryption Setting=enabled"

# Open the connection
$sqlConn.Open()

# Create a SqlCommand object, and add the query and parameters
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn

$sqlcmd.CommandText = "INSERT INTO dbo.EmpTbl (Name, SSN,Salary, email) VALUES ( @name, @ssn, @salary, @email )"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@name",[Data.SQLDBType]::VARCHAR, 50)))
$sqlcmd.Parameters["@name"].Value = "Fong"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ssn",[Data.SQLDBType]::VARCHAR, 20)))
$sqlcmd.Parameters["@ssn"].Value = "6756-15-0342"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@salary",[Data.SQLDBType]::Money)))
$sqlcmd.Parameters["@salary"].Value = "540"

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@email",[Data.SQLDBType]::VARCHAR, 50 )))
$sqlcmd.Parameters["@email"].Value = "fong@yahoo.com"

#Insert the data
$sqlcmd.ExecuteNonQuery();

#Close the connection
$sqlConn.Close()

[Step#10] Use the following script to search encrypted columns. Notice that we need to use “Column Encryption Setting=Enabled” as a connection string option:

# Connect to your database.
$AzSQLServer    = "tcp:shb-az-db-01.database.windows.net,1433"
$sqlAdmin       = "sqladmin"
$sqlAdminPass   = "Winter2020"
$databaseName   = "TestDB"
$SqlQuery       = "SELECT * FROM Emptbl WHERE name = @name;"  

# connect to the database
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server = " + $AzSQLServer + "; Database = " + $databaseName + "; `
        Integrated Security = false;User ID=$sqlAdmin;Password=$sqlAdminPass;Column Encryption Setting=Enabled"

# Open the connection
$sqlConn.Open()
  
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
$SqlCmd.CommandText = $SqlQuery  

$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@name",[Data.SQLDBType]::VARCHAR, 20)))
$sqlcmd.Parameters["@name"].Value = "Bart"

$SqlCmd.Connection = $SqlConn
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
$SqlAdapter.SelectCommand = $SqlCmd   

#Creating Dataset  
$DataSet = New-Object System.Data.DataSet  
$SqlAdapter.Fill($DataSet)  
$DataSet.Tables[0] | Format-Table

# Close the connection
$sqlConn.Close()

Querying Encrypted table:

 

Further reading:

SQL Server Always Encrypted at a glance - using PowerShell

Azure SQL logical Server and creating/migrating database – non-interactively using PowerShell

This tutorial is about creating an Azure logical database server along with its associated resources, and then creating/migrating database non-interactively. The provided script can be run manually or as a scheduled task. You need to replace your subscription and a few other values found in the PowerShell script to reflect your situation.

The Project Goal:
We have a need to provision a logical database server in Microsoft Azure and then creating one or more brand new Azure SQL Databases. We also need to migrate one or more on-premises databases to this logical server.

Pre-requisites:

• Any valid Azure subscription.
• PowerShell 5.1+
• Latest Azure PowerShell Module (Install-Module -Name Az)
• Latest SSMS
• SQLPackages.exe

PowerShell Script explanation:
Note the value of SubscriptionID and TenantID from the Azure Portal; this information is required to login non-interactively.

Alternatively, you can login to the Azure Cloud interactively and then collect the SubscriptionID and TenantID in-advance with the following code:

# To get Subscription and Tenant ID
Clear-Host

Connect-AzAccount

$tenantID       = (Get-AzContext).Tenant.id
$subscriptionID = (Get-AzContext).Subscription.id

"TenantID = $tenantID"
"SubscriptionID = $subscriptionID"

Steps to follow:

• Login to the Azure cloud.
• Create a new resource group.
• Create a logical SQL Server.
• Open the Firewall to allow specific IP to the logical database server.
• Create one or more new databases.
• Migrate one or more on-premises databases using SQLPackage.exe to the newly created logical server.

PowerShell Script:

################################################
# Step 1: Connect to the Azure cloud
################################################
$azUser = "myemail@hotdog.com"
$azPassword = ConvertTo-SecureString -String "OpenPass404" -AsPlainText -Force
$tenantID = "953c78eb-391d-6c0b-a35f-7224d135267"
$subscriptionID = "f961f3ad-79f9-471e-aba7-3f92b8458c0b"

$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $azUser,$azPassword
Connect-AzAccount -Credential $Credential -Tenant $tenantID -Subscription $subscriptionID


################################################
# Step 2: Create a resource group
################################################
$rgName ="shb-azure-rg-01"
$location = "eastus" # $location = "East US"
New-AzResourceGroup -Name $rgName -Location $location


################################################
# Step 3: Create a logical Azure SQL Server
################################################
$serverName         ="shb-az-db-01"
$sqlAdmin           = "sqladmin"
$sqlPassword        = "Winter2020"
$sqlPasswordSecure  =  $(ConvertTo-SecureString -String $sqlPassword -AsPlainText -Force) 
$SqlAdminCredential = $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sqlAdmin, $sqlPasswordSecure )

$newServer = New-AzSqlServer -ResourceGroupName $rgName `
    -Location $location `
    -ServerName $serverName `
    -SqlAdministratorCredentials $SqlAdminCredential


################################################
# Step 4: Open Azure server firewall to allow access 
# Replace the public IP addresses bellow 
################################################
$newStartIP = $((Invoke-RestMethod http://ipinfo.io/json).ip)
$newEndIP   = $((Invoke-RestMethod http://ipinfo.io/json).ip)

$newFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $rgName `
    -ServerName $serverName `
    -FirewallRuleName "Allow_Public_IP" -StartIpAddress $newStartIP -EndIpAddress $newEndIP


################################################
# Step 5: Create a new database by specifying a service tier 
# Get-AzSqlServerServiceObjective -Location $location
################################################
$databaseName = "ProductDB"
$newDatabase = New-AzSqlDatabase  -ResourceGroupName $rgName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -RequestedServiceObjectiveName "Basic"


################################################
# Step 6: Migrate on-premises dataabse to Azure SQL
################################################

# Environment variable for SQLPackage
$EnvPath = $Env:Path
IF (-not $EnvPath.Contains( "C:\Program Files\Microsoft SQL Server\150\DAC\bin")) {
    Write-Host "SQLPackage.exe is not in the environment variables path"
    $Env:Path = $Env:Path + ";C:\Program Files\Microsoft SQL Server\150\DAC\bin;" 
}

# database to backup 
$databaseToAzure = 'WorkForceDB'

# bacpac file name for the backup
$fileName = $databaseToAzure + "_full"

# backup to a local folder
# or to a local temporay folder
$folderName = $env:temp

#target filename with location 
$targetFilePath = "$folderName\$fileName-$(get-date -f yyyyMMdd-hhmmtt).bacpac"

# export the database to the local folder
SqlPackage.exe /Action:Export /scs:"Server=SHBMAIN\SQL2019;Database=$databaseToAzure;Trusted_Connection=True;" /tf:$targetFilePath

# import the database in Azure SQL Database
sqlpackage.exe /Action:Import /tsn:tcp:$serverName.database.windows.net,1433 /tdn:$databaseToAzure /tu:$sqlAdmin /tp:$sqlPassword /sf:$targetFilePath /p:Storage=File 

# Change the pricing tier
Set-AzSqlDatabase -ServerName $serverName -ResourceGroupName $rgName `
        -DatabaseName $databaseToAzure -RequestedServiceObjectiveName "Basic"

################################################
# Step 6: Use SSMS to connect to the Azure SQL
################################################

PowerShell Script Execution:

New Resource has been created:

 

Opening Public IP in Firewall for the Logical Server:
Connecting to the Logical Server using SSMS:

References:

Use PowerShell to create a single database and configure a server-level firewall rule
https://docs.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-powershell?toc=/powershell/module/toc.json
 

Public Endpoint for Azure Managed Instance and SSMS connectivity

VPN throughput or intermittent connection disruption could be an issue while connecting to an Azure Managed Instance. To avoid VPN connection-related disruptions to an Azure Managed Instance,  Microsoft has introduced Public Endpoint Link to connect an Azure managed instance directly from an on-premises SSMS. Public Endpoint is also helpful while managing both on-premises SQL Servers as well as Cloud based Azure SQL offerings through SSMS.

Steps to follow: (using PowerShell or using Azure Portal)

• Enable Public Endpoint for the managed instance.
• Configure the managed instance Network Security Group (NSG).
• Obtain the managed instance Public Endpoint host name.

Managed Instance Note:

• The value for the priority of the “Inbound Traffic Rule” must be higher than the “deny_all_inbound” rule.
• The default port of the managed instance is 3342, it is fixed and can’t be changed.
• To connect from an on-premises SSMS, use the following host naming method:

<mi_name>.public.<dns_zone>.database.windows.net,3342    

In our example, the managed instance host name is the following:

shb-mi-01.public.aaf67be1d0fe.database.windows.net,3342

Using PowerShell:

# Connect to the Azure Cloud
Connect-AzAccount

# Initiate Resource groups where managed instance belongs to
$rgname ='shb-db-rg-01'
# Managed instance name
$miname = 'shb-mi-01' 

# Enable the public endpoint
$misql = Get-AzSqlInstance -ResourceGroupName $rgname -Name $miname
$misql = $misql | Set-AzSqlInstance -PublicDataEndpointEnabled $true -force

# Modify NSG (Network security Group) by adding Inbound security rule
Get-AzNetworkSecurityGroup -Name nsg-shb-mi-01 -ResourceGroupName shb-db-rg-01 | `
    Add-AzNetworkSecurityRuleConfig -Name public_endpoint_inbound_sql -Description "Allow SQL TCP" -Access Allow `
    -Protocol Tcp -Direction Inbound -Priority 1210 -SourceAddressPrefix * -SourcePortRange * `
    -DestinationAddressPrefix * -DestinationPortRange 3342 | Set-AzNetworkSecurityGroup

 Inbound Traffic:

Connection to an Managed Instance:

References:

• Configure public endpoint in Azure SQL Managed Instance: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure
• T-SQL differences between SQL Server & Azure SQL Managed Instance: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server