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:
- The columns which are using deterministic encryption only support WHERE equality comparisons.
- 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.).
- It does not support CONSTRAINT.
- The collation of the targeted encrypted column needs to be “Latin1_General_BIN2”.
- When encryption is applied, column name and data type become case sensitive.
Certificate Management:
- The certificate and the certificate protective password (if any) must be preserved safely.
- Any encrypted column can be reversed to plain text as long as SQL Server finds the certificate in the certificate store.
- 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.
- One certificate can be used to encrypt all columns or multiple certificates can be used to encrypt multiple columns.
- 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.
- 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.
- 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:
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:
- 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.
- Backup/Restore on same server: Backups and restores will not impact column encryption functionality.
- 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.
- 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.
- 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:
No comments:
Post a Comment