Skip to content

Latest commit

 

History

History
291 lines (207 loc) · 14 KB

2.CreateGenericCMK-CEK.md

File metadata and controls

291 lines (207 loc) · 14 KB
Raw

CMK / CEK / Encryption for SQL Always Encrypted generic provider

This scenario use the Key provising with role separation provided by Microsoft.

There is 2 distinguished roles :

  • The security administrator, which has access to the certificate.
  • The database (DBA) administrator, which has no access to the certificate.

While this scenario use a complete role separation, for the sake of simplicity everything was performed on the same computer. I just removed the certificate from the Windows Certificate store for the DBA administrator steps.

So I only setup environment once for both.

Setup environment for Generic Provider usage

This step must de done only once per computer. This download the PowerShell SqlServer module and overwrite the Microsoft dll with the patched one (making a backup of the originating dll).

#####################################################
# BOTH Security administrator and DBA administrator #
#####################################################

#register the default PSGallery repository if not already done
Register-PSRepository -Default -ErrorAction SilentlyContinue

#install SqlServer module if not already done
Find-Module SqlServer | Install-Module

#get the location of the SqlServer PowerShell module and create a backup of the Microsoft dll.
$SqlServerModulePath = (Get-Module -Name "SqlServer" -ListAvailable).ModuleBase

#you can check if the assembly PublicKeyToken match with the one provided in the next step to bypass strong name verification.
$assembly = [System.Reflection.Assembly]::ReflectionOnlyLoadFrom("$SqlServerModulePath\Microsoft.SqlServer.Management.AlwaysEncrypted.Management.dll")
Write-Host $assembly.FullName

#create a backup of the original dll
Rename-Item -Path "$SqlServerModulePath\Microsoft.SqlServer.Management.AlwaysEncrypted.Management.dll" -NewName "Microsoft.SqlServer.Management.AlwaysEncrypted.Management-BACKUP.dll"

#unblock files if downloaded from internet (git or zip)
Unblock-File -Path ".\bin\*.dll" -Verbose

#copy the patched dll to the Sql Server module path to replace the original Microsoft dll.
Copy-Item -Path ".\bin\Microsoft.SqlServer.Management.AlwaysEncrypted.Management.dll" -Destination $SqlServerModulePath

Check the CLR policy and register the dll to bypass strong name verification.

rem Find the sn tool in your .NET Framewrok SDK (path may be different on your host).
cd "C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools"
rem Enable bypass Strong Name verification
sn -Pb y
rem Register the DLL for strong name verification skipping (ensure that 89845DCD8080CC91 match the publickKeyToken)
sn -Vr "Microsoft.SqlServer.Management.AlwaysEncrypted.Management,89845DCD8080CC91" AllUsers

Security administrator

The security administrator (with access to the certificate) now generate CMK settings and encrypted value for the database administrator.

Import PowerShell modules and Types

Import-Module "SqlServer"

#import the extended Always Encrypted cmdlets
Import-Module '.\bin\SqlServerAlwaysEncrypted.dll' -Verbose

#import the extended Always Encrypted types (the Generic provider)
Add-type -Path '.\bin\SqlServerAlwaysEncrypted.dll'

Create the wrapped provider and the generic provider

#create a standard SqlCertificateStoreProvider
$sqlstoreprovider = New-Object -TypeName System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider

#create a generic provider that wrap the SqlCertificateProvider. Provide the real path for the underlying provider !!
#for SqlCertificateStoreProvider, refer to the documentation of each provider to configure properly the path.
$customprovider = New-Object -TypeName SqlServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider($sqlstoreprovider, "CurrentUser/My/$($cert.Thumbprint)")

#confirm the generic provider settings
#the ProviderName property is a static property ... Microsoft implemented all providers with a static property. So I kept this implementation.
Write-Host $customprovider.MasterKeyPath
Write-Host $([SqlServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider]::ProviderName)

Register the generic provider. Be careful with the order of the commands. Once the SqlServer PowerShell module is imported, not all assemblies are loaded until you use one cmdlet of the module. At this step, no cmdlet from the SqlServer is used yet. The first command "fake" a call to any SqlServer cmdlet to load all the assemblies used by the module. This initialize the internal dictionnay of custom providers.

<#internally Always Encrypted Microsoft cmdlets do not load directly the [Microsoft.SqlServer.Management.AlwaysEncrypted.Management] assembly.
Call chain:
  - [Microsoft.SqlServer.Management.AlwaysEncrypted.Types]::CustomProviders 
  - [Microsoft.SqlServer.Management.AlwaysEncrypted.Management]::CustomProviders
  - [System.Data.SqlClient.SqlConnection]::RegisterColumnEncryptionKeyStoreProviders()
All cmdlets related to CMK / CEK use this call chain and use the [Microsoft.SqlServer.Management.AlwaysEncrypted.Management]::CustomProviders dictionnary.
All cmdlets related to columns encryption use directly the [System.Data.SqlClient] assembly and its internal dictionnary of custom providers.
#>
[Microsoft.SqlServer.Management.AlwaysEncrypted.Types.AlwaysEncryptedManager]::CustomProviders

#confirm only one custom provider is registered currently (Azure_Key_Vault)
#PROVIDED CMDLET in the SQLServerAlwaysEncrypted.dll
Get-SqlColumnEncryptionCustomProvider

#Register the generic provider in all custom providers stores (dictionnary)
#PROVIDED CMDLET in the SQLServerAlwaysEncrypted.dll
Register-SqlColumnEncryptionCustomProvider -Provider $customprovider -ProviderName $([SqlServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider]::ProviderName)

#PROVIDED CMDLET in the SQLServerAlwaysEncrypted.dll
#check our custom provider is registered in the different assemblies
#[Microsoft.SqlServer.Management.AlwaysEncrypted.Management]
Get-SqlColumnEncryptionCustomProvider

#[System.Data.SqlClient]
Get-SqlColumnEncryptionCustomProvider -FromSQLConnection

This finally output the current registered generic provider in the different assemblie's provider stores :

PS C:\Users\Administrator\Desktop\GenericProvider> Get-SqlColumnEncryptionCustomProvider

Key             Value                                                                                                        
---             -----                                                                                                        
AZURE_KEY_VAULT Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider
GENERIC         SQLServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider                                                  



PS C:\Users\Administrator\Desktop\GenericProvider> Get-SqlColumnEncryptionCustomProvider -FromSQLConnection

Key             Value                                                                                                        
---             -----                                                                                                        
AZURE_KEY_VAULT Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider
GENERIC         SQLServerAlwaysEncrypted.SqlColumnEncryptionGenericProvider


CMK Key Exchange

This step is done by the security administrator.

# Create a SqlColumnMasterKeySettings object (the CMK metadata) with information about the generic provider. KeyPath must not be empty. Here i use "NONE", but this may be the certificate thumbprint or whatever you want. This is not important because the generic provider is already configured with the real keypath to the wrapped provider.
$cmkSettings = New-SqlColumnMasterKeySettings -KeyStoreProviderName "GENERIC" -KeyPath "NONE"

# Encrypt a value for the CEK
$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings

# Share the location of the column master key and an encrypted value of the column encryption key with a Database administrator, via a CSV file on a share drive
$keyDataFile = "C:\Temp\ExchangeWithDBAdmin.txt"
"KeyStoreProviderName, KeyPath, EncryptedValue" > $keyDataFile
$cmkSettings.KeyStoreProviderName + ", " + $cmkSettings.KeyPath + ", " + $encryptedValue >> $keyDataFile

Running the following commands display a result like this :

PS C:\Users\Administrator\Desktop\GenericProvider>$cmkSettings.KeyStoreProviderName
PS C:\Users\Administrator\Desktop\GenericProvider>$cmkSettings.KeyPath
PS C:\Users\Administrator\Desktop\GenericProvider>$encryptedValue

GENERIC
NONE
0x016E000002630075007200720065006E00740075007300650072002F006D00790...

Database administrator

The DBA administrator has no access to the certificate. The DBA administrator must setup its environment as well to use the Generic Key store provider. Refer to Setup environment for Generic Provider usage.

  • Check $serverName
  • Check $databaseName
  • Check if your database use integrated security, else modify the connection string accordingly.
#Import the module
Import-Module "SqlServer"

# Obtain the location of the column master key and the encrypted value of the column encryption key from your Security Administrator, via a CSV file on a share drive.
$keyDataFile = "C:\Temp\ExchangeWithDBAdmin.txt"
$keyData = Import-Csv $keyDataFile

# Connect to your database or use the SQLServer: PSDrive
$serverName = "localhost"
$databaseName = "CLINIC"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]


# Create a SqlColumnMasterKeySettings object for your column master key. 
$cmkSettings = New-SqlColumnMasterKeySettings -KeyStoreProviderName $keyData.KeyStoreProviderName -KeyPath $keyData.KeyPath 

# Create column master key metadata in the database.
$cmkName = "CLINIC_GENERIC_CMK"
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 = "CLINIC_GENERIC_CEK"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName -EncryptedValue $keyData.EncryptedValue

You can now check the keys in SQL Server Management Studio. The result should be like the following screenshot.

  • CMK / CEK available :

CMK/CEK available

  • CMK Details (check the Key Store Provider and Key Path) :

Generic CMK

  • CEK Details :

Generic CMK



Security administrator

Now CMK and CEK keys are created in the database, the security administrator (with access to the certificate) can now encrypt the columns.

  • Data before encryption :

Data PlainText

# set the CEK key name
$cekName = "CLINIC_GENERIC_CEK"

#define columns encryption settings
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey $cekName

#define a log directory
$logdirectory = "C:\Temp\"

# Connect to your database or use the SQLServer: PSDrive
$serverName = "localhost"
$databaseName = "CLINIC"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

#start columns encryption
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -LogFileDirectory $logdirectory -InputObject $database -UseOnlineApproach
  • Data after encryption :

Data PlainText

  • Log file result (for information) :
9/10/2018 4:12:17 PM		INFO		MainThread		Logger initialized.
9/10/2018 4:12:17 PM		INFO		MainThread		Acquiring database model and preparing data migration.
9/10/2018 4:13:20 PM		INFO		[dbo].[Patients]		Data migration for table '[dbo].[Patients]' started.
9/10/2018 4:13:20 PM		INFO		[dbo].[Patients]		Processing Table '[dbo].[Patients]'. 50.00 % done.
9/10/2018 4:13:20 PM		INFO		[dbo].[Patients]		Initial seeding for table '[dbo].[Patients]' completed.
9/10/2018 4:13:20 PM		INFO		[dbo].[Patients]		0 new or updated rows detected in table '[dbo].[Patients]'. Starting catch-up iteration #1.
9/10/2018 4:13:21 PM		INFO		[dbo].[Patients]		Catch-up iteration #1 for table '[dbo].[Patients]' completed. Data migration is 75 % done.
9/10/2018 4:13:22 PM		INFO		[dbo].[Patients]		0 new or updated rows detected in table '[dbo].[Patients]'. Starting catch-up iteration #2.
9/10/2018 4:13:23 PM		INFO		[dbo].[Patients]		Attempting to lock table '[dbo].[Patients]'.
9/10/2018 4:13:24 PM		INFO		[dbo].[Patients]		Table '[dbo].[Patients]' has been locked.
9/10/2018 4:13:27 PM		INFO		[dbo].[Patients]		Catch-up iteration #2 for table '[dbo].[Patients]' completed. Data migration is 99.9 % done.
9/10/2018 4:13:28 PM		INFO		[dbo].[Patients]		Catch-up phase completed.
9/10/2018 4:13:28 PM		INFO		MainThread		Finalizing data migration.
9/10/2018 4:13:29 PM		INFO		MainThread		Deploying the specified encryption settings completed in 0d:0h:1m:12s.

Clients

Now you can use the client sample to access the database and decrypt values :