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:
- Azure Subscription
- PowerShell 5.1 +
- Azure PowerShell Module
- SQL Server PowerShell Module
- 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
No comments:
Post a Comment