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

SQL Server Always Encrypted at a glance - using PowerShell

Following are some criteria we need to consider while implementing the Always Encrypted feature in SQL Server 2016 or above. We’ll create a test database and a sample certificate, and then encrypt one or more columns using the certificate for this tutorial.

Always Encrypted at a glance: 

• SQL Server Always Encrypted uses a certificate. A self-signed certificate can be generated by SQL Server itself or PowerShell. External third-party tools or certificate providers can recommend the appropriate certificate based on Microsoft specifications. 
• The certificate expiration date is not adhered to by SQL Server, and will be ignored. SQL Server will not validate the certificate expiration date to encrypt/decrypt data. 
• The certificate can be stored in the Windows Certificate Store, Azure Key Vault or a Key Storage Provider (CNG). 
• When a particular certificate is used with the Always Encrypted feature to secure one or more columns, the certificate needs to be imported/distributed to all downstream applications’ certificate store or a centralized certificate store. 
• Microsoft .NET Framework 4.6.x or above requires connecting to the SQL Server database. 
• The option Column Encryption Setting=Enabled needs to be added to the connection string. 

Few Encrypted Column Restrictions: 

1. The columns which are using deterministic encryption only support WHERE equality comparisons. 
2. An encrypted column also supports DISTINCT, JOIN, and GROUP BY. We cannot perform inequality, range, or LIKE queries, or any other operations against encrypted columns (arithmetic, date/time operations, etc.). 
3. It does not support CONSTRAINT. 
4. The collation of the targeted encrypted column needs to be “Latin1_General_BIN2”. 
5. When encryption is applied, column name and data type become case sensitive. 

Certificate Management:

1. The certificate and the certificate protective password (if any) must be preserved safely. 
2. Any encrypted column can be reversed to plain text as long as SQL Server finds the certificate in the certificate store. 
3. If the certificate is deleted/damaged/tampered, the encrypted column data can not be retrieved. However, all other non-encrypted columns remain accessible and retrievable. 
4. One certificate can be used to encrypt all columns or multiple certificates can be used to encrypt multiple columns. 
5. The certificate can be replaced and rotated. 

Steps to implement Always Encrypted feature:

To encrypt columns, we require a certificate. We can use either SSSMS or PowerShell to generate one or more self-signed certificates. To implement Always Encrypted features in SQL Server, we perform the following steps sequentially.

• Generate a certificate and import it to the Windows Certificate store. 
• Take a note of “Thumbprint” and storage the path of the certificate. 
• Generate a CMK (Column Master Key) from the certificate. 
• Generate CEK (Column Encryption Key). 
• Encrypt one or more columns with the CEK. 
• Export the certificate with a password. 
• Import the certificate in a different server or desktop. 
• Connect to the database server using SSMS/.Net Framework Driver and use the Column Encryption Setting=Enabled option. 

Pre-requisites: 

• Latest version of SSMS. 
• PowerShell 5.1 or above. 
• SQL Server 2016 or above. 
• Import SQL PowerShell Module. 

Encryption Type:
Always Encrypted supports two types of encryption: deterministic encryption and randomized encryption.

1. Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows for point lookup, equality joins, grouping and indexing on encrypted columns. 
2. Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns. 

SQL Server metadata and Certificate:
SQL Server metadata or the encrypted database do not hold the certificate. SQL Server only preserves the generated CMK (Column Master Key) and the CEK (Column Encryption Key) which is used to encrypt a column. Therefore, the certificate must be backed up form the certificate store and preserved safely.

Connecting an Encrypted Database:
To connect an Always Encrypted database, the recommended driver is .Net Framework 4.6 or above. Note that we also need to include the “Column Encryption Setting = Enabled” with the connection string.

Querying Encrypted Columns:

If a Certificated is deleted or damaged:
When a certificate is deleted or damaged, all DML statements will fail when an encrypted column is included with the T-SQL statement. However, a statement will succeed if the encrypted column is not used in the T-SQL statement. To fix the problem, the certificate must be imported to the certificate store.

Error when certificate is missing:

Converting to Plain Text from Encrypted Text:
If we want to revert back to plain text format of an encrypted column, it can be done easily as long as the SQL Server finds the certificate which is used to encrypt the column.

Database Server upgrade or backup/restore:

1. In-Place Upgrade: If the SQL Server gets upgraded (compatibility change) to a newer version, column encryption functionality will have no impact. No action is required. 
2. Backup/Restore on same server: Backups and restores will not impact column encryption functionality. 
3. Backup/Restore to a different server: Restoring an Always Encrypted database backup to a different server can happen and columns will not lose encryption functionality. The original certificate needs to be restored in order to decrypt the encrypted column. 

Windows Certificate Store:
In a Windows machine, all certificates will be stored in the Windows Certificate Store. There are two major stores, that are further divided into sub-stores.

If you are the administrator on a computer, you can view both major stores by using the MMC snap-in tool. Non-administrators can view only the Current User store.

1. Certlm.exe  - the Local Machine store: This contains the certificates accessed by machine processes. This location is used to store certificates that authenticate the server to clients. Use Certlm.exe to open the local machine certificate store. 
2. Certmgr.exe  - the Current User store: Interactive applications typically place certificates here for the computer's current user. Certificates for a client application typically reside here and authenticate a user to a service. Use Certmgr.exe to open Current User certificate store.

Local Machine Store vs Current User Store:

In Windows, the certificate can be stored in two places in the Windows Certificate Store. The certificate store can be Current User or Local Machine. If a certificate gets stored in the Current User store, it can not be read by any other user. If the certificate is stored in the Windows Local Machine store, all users can read the certificate with appropriate permissions.

Querying metadata:
The following DMVs are used to find certificates and key-related information:

-- Encryption key and certificate information
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_key_values;
SELECT * FROM sys.column_encryption_keys;

-- Encryption key and certificate information
SELECT cmk.name AS CMK, 
       cek.name AS CEK, 
       cmk.key_store_provider_name, 
       cmk.key_path, 
       cekv.encryption_algorithm_name, 
       cekv.encrypted_value
FROM sys.column_encryption_keys cek
     LEFT JOIN sys.column_encryption_key_values cekv ON cek.column_encryption_key_id = cekv.column_encryption_key_id
     LEFT JOIN sys.column_master_keys cmk ON cmk.column_master_key_id = cekv.column_master_key_id;

 Create sample database and table:

-- Sample database 
CREATE DATABASE [TestDB]
GO

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, 'Jhon', '456-65-3369', 500.00, 'jhon@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

 Generate Self-signed Certificate, create CMK and CEK:

# Install SqlServer PowerShell module
# Install-Module "SqlServer" -AllowClobber -Force

# Import the SqlServer module.
# Import-Module "SqlServer"

# Initiate certificate expire date
$today = Get-Date
$years = $today.AddYears(5)

# Create a column master key in Windows Certificate Store.
$params=@{
    Subject = "Always Encrypted Certificate" 
    CertStoreLocation ="Cert:LocalMachine\my"
    KeyExportPolicy ="Exportable"
    Type ="DocumentEncryptionCert"
    KeyUsage ="DataEncipherment"
    KeySpec = "KeyExchange"
    NotAfter = $years }
$cert = New-SelfSignedCertificate @params

# get the certificate thumbprint
$Thumbprint= $cert.Thumbprint

# Connect to your database.
$serverName = "SHBMAIN\SQL2016"
$databaseName = "TestDB"

# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Create a SqlColumnMasterKeySettings object for your column master key. 
$params = @{

            CertificateStoreLocation ="LocalMachine" 
            Thumbprint = $Thumbprint }

$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings @params

# Create column master key metadata in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey `
    -Name $cmkName `
    -InputObject $database `
    -ColumnMasterKeySettings $cmkSettings

# Generate a column encryption key, encrypt it with the column master key 
# and create column encryption key metadata in the database. 
$cekName = "CEK1"
New-SqlColumnEncryptionKey `
    -Name $cekName `
    -InputObject $database `
    -ColumnMasterKey $cmkName

 Encrypt Columns:

##################################################################
# Encrypt the columns with CEK1 key
##################################################################
$columns = @()

$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.SSN" -EncryptionType Deterministic -EncryptionKey $cekName
$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.Salary" -EncryptionType Randomized -EncryptionKey $cekName
$database | Set-SqlColumnEncryption -ColumnEncryptionSettings $columns

##################################################################
# decrypt encrypted column to plain text
##################################################################
$columns = @()

$columns += New-SqlColumnEncryptionSettings -ColumnName "dbo.EmpTbl.Salary" -EncryptionType PlainText 
$database | Set-SqlColumnEncryption -ColumnEncryptionSettings $columns

 Insert sample data:

######################################################################################
# [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Install-Module -Name SqlServer 
# Import-Module SqlServer
######################################################################################

#Create a SqlConnection object, specifying Column Encryption Setting = enabled
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server=SHBMAIN\SQL2016;Integrated Security=true; Initial Catalog=TestDB; 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 = "Doug"

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

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

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

#Insert the data
$sqlcmd.ExecuteNonQuery()

#Close the connection
$sqlConn.Close()

 Search Encrypted Column:

#################################################
# Searching encrypted column 
#################################################
Clear-Host

$SqlQuery = "SELECT * FROM Emptbl WHERE name = @name;"  

$SqlConn = New-Object System.Data.SqlClient.SqlConnection  
$sqlConn.ConnectionString = "Server=SHBMAIN\SQL2016;Integrated Security=true; Initial Catalog=TestDB; Column Encryption Setting=enabled;"
$SqlConn.Open()
  
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
$SqlCmd.CommandText = $SqlQuery  

#$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@ssn",[Data.SQLDBType]::VARCHAR, 20)))
#$sqlcmd.Parameters["@ssn"].Value = "456-65-3369"

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

$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

 Import/Export Certificates on Windows Server and Windows 10:

# Export and Import Certificates on Windows Server
$CertPassword = ConvertTo-SecureString -String "YourPassword2020" -Force -AsPlainText

$cert = dir cert:LocalMachine\My -Recurse | Where-Object { $_.Subject -like "*Always*" } | select -First 1
$cert.Thumbprint

Export-PfxCertificate `
    -Cert cert:\LocalMachine\My\$($cert.Thumbprint) `
    -FilePath D:\SQLDir\AlwaysEncrypted\AlwaysCert.pfx `
    -Password $CertPassword 	

Import-PfxCertificate `
    -FilePath D:\SQLDir\AlwaysEncrypted\AEcert.pfx `
    -CertStoreLocation Cert:\LocalMachine\My `
    -Password $CertPassword `
    -Exportable

 Import Certificate on a Windows 7 Desktop:

# Import Certification on Windows 7.0
function Import-PfxCertificate
{	
	param (
		[Parameter(Mandatory = $true)]
        [String]$certPath,
		[Parameter(Mandatory = $true)]		
        [String]$certRootStore = "LocalMachine",
		[Parameter(Mandatory = $true)]		
        [String]$certStore = "My",
		[Parameter(Mandatory = $false)]
		$CertPassword = $null)
	
    $pfx = new-object System.Security.Cryptography.X509Certificates.X509Certificate2
	
	if ($CertPassword -eq $null) {$CertPassword = read-host "Enter Password for the Certificate: " -AsSecureString} 
	
	$pfx.import($certPath, $CertPassword, "Exportable,PersistKeySet")
	
	$store = new-object System.Security.Cryptography.X509Certificates.X509Store($certStore, $certRootStore)
	$store.open("MaxAllowed")
	$store.add($pfx)
	$store.close()
}

# Password for the certificate
$CertPassword = ConvertTo-SecureString -String "YourPassword2020" -Force -AsPlainText

# Import the certificate
Import-PfxCertificate -CertPath "D:\CertLocation\AlwaysCert.pfx" `
        -certRootStore "CurrentUser" `
        -CertStore "My" `
        -CertPassword $CertPassword

 References:

• Always Encrypted: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
• Working with Certificates: https://docs.microsoft.com/en-us/dotnet/framework/wcf/feature-details/working-with-certificates

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