Azure Key vault and encryption in MSSQL

       

Ø  Azure key vault is Microsoft centralized solution enables you to store sensitive information (passwords, keys and certificates etc) All this information is stored in HSM (Hardware security modules) in datacentre which is (HSM) - FIPS 140-2 Level 2 certified 

Ø  It is basically a container where you store all secretes and no one can see this until they have authorized access to it

Example -we have MSSQL Admin password which should be provided while installing MSSQL and if  DBA left the company then one knows the password and to manage it  if use key vault we can store admin password in key vault and use it as admin password for all the installations and even this password is

Ø   A vault owner will be allocated full rights on the vault he can modify delete and audit the vault and see who has accessed key, certificates and passwords and feature versions Microsoft will provide is option to audit this

Ø  Microsoft creates 3 copies of key vault backups in same location and one copy in paired region for disaster scenarios and it will be in same Geographical region

Ø   We can monitor this in Azure monitoring tool

Ø  To create key vault, we can use PowerShell

How to create Key vault using PowerShell command

Prerequisite –

1.Azure account

2.Azure PowerShell modules need to be installed

# The name of the Azure subscription to install the Key Vault into

$subscriptionName = 'MySubscription'

# The resource group that will contain the Key Vault to create to contain the Key Vault

$resourceGroupName = 'MyKeyVaultRG'

# The name of the Key Vault to install

$keyVaultName = 'MyKeyVault'

# The Azure data center to install the Key Vault to

$location = 'southcentralus'

# These are the Azure AD users that will have admin permissions to the Key Vault

$keyVaultAdminUsers = @('xxxx','xxxxx')

# Login to Azure

Login-AzureRMAccount

# Select the appropriate subscription

Select-AzureRmSubscription -SubscriptionName $subscriptionName

# Make the Key Vault provider is available

Register-AzureRmResourceProvider -ProviderNamespace Microsoft.KeyVault

# Create the Resource Group

New-AzureRmResourceGroup -Name $resourceGroupName -Location $location

# Create the Key Vault (enabling it for Disk Encryption, Deployment and Template Deployment)

New-AzureRmKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroupName -Location $location `

    -EnabledForDiskEncryption -EnabledForDeployment -EnabledForTemplateDeployment

# Add the Administrator policies to the Key Vault

foreach ($keyVaultAdminUser in $keyVaultAdminUsers) {

    $UserObjectId = (Get-AzureRmADUser -SearchString $keyVaultAdminUser).Id

    Set-AzureRmKeyVaultAccessPolicy -VaultName $keyVaultName -ResourceGroupName $resourceGroupName -ObjectId $UserObjectId `

        -PermissionsToKeys all -PermissionsToSecrets all -PermissionsToCertificates all

}

MSSQL Always encrypted

Ø  Always encrypted is client-side encryption technology which is used secure sensitive data in MSSQL server or Azure MSSQL databases  

Ø  If we encrypted the columns in tables using this technology, it will encrypt all the data coming to this columns and decrypts when it is requested by authorized user

Ø  We can see below how we can encrypt the data in the table

1.Go to database -àexpand tables-àright click on table  which you want encrypt and we can see option encrypt columns as shown in the below screenshot

Once we select encrypt columns option it will open new wizard as shown below

Ø  Click on next and select the columns which you want to encrypt as shown below and it contains 3 options to select as below

Ø  Once I check the columns which I want to encrypt then we need to select Encryption type

Ø  We have 2 types of encryption types

1. Deterministic encryption

2. Randomized encryption

Ø  The Database Engine never operates on plaintext data stored in encrypted columns, but it still supports some queries on encrypted data, depending on the encryption type for the column. Always Encrypted supports two types of encryption: randomized encryption and deterministic encryption.

1.Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character 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.

Use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records and are not used to join tables

Ø  In next step it will ask to create master key Configuration where we have below options

Ø  If we have azure Key vault then we can select Azure key vault and we need provide some additional information about azure account, subscription and resource group details and click on next

Ø  This certificate will be stored in local windows certificate store if we select windows certificate store

Ø  On next window we have option to generate PowerShell script and run this later or proceed and finish it now option to enable encryption immediately

Ø  We can see summary of the same thing which we have previously created

Ø  Click on finish to create as below

Ø  Once we enabled encryption successfully, we need to check if Master key and Encryption key

is created in security àAlways Encrypted Keys -àColumn Master Keys Here we can see column master key

Ø  security àAlways Encrypted Keys -àColumn Encryption keys we can see Column encrypted keys as below

Once it is enabled if I select the data from the columns I can below encrypted data as below

Here we can see Message Code and Message columns are encrypted

To see the encrypted information using SSMS in same machine where certificate is created in Master key configuration step, We need to enable encryption setting in Additional connection parameters in SSMS as below and command to use is  Column Encryption Setting=enabled

   

This are not supported by Always encryption

Reference - https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

Always Encrypted is not supported for the columns with the below characteristics (for example, the Encrypted WITH clause cannot be used in CREATE TABLE/ALTER TABLE for a column, if any of the following conditions apply to the column):

Ø  Columns using one of the following datatypes: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types.

Ø  FILESTREAM columns

Ø  Columns with the IDENTITY property

Ø  Columns with ROWGUIDCOL property

Ø  String (varchar, char, etc.) columns with non-bin2 collations

Ø  Columns that are keys for nonclustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)

Ø  Columns that are keys for clustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)

Ø  Columns that are keys for fulltext indices containing encrypted columns both randomized and deterministic

Ø  Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted)

Ø  Sparse column set

Ø  Columns that are referenced by statistics

Ø  Columns using alias type

Ø  Partitioning columns

Ø  Columns with default constraints

Ø  Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported)

Ø  Primary key columns when using randomized encryption (deterministic encryption is supported)

Ø  Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms

Ø  Columns referenced by check constraints

Ø  Columns in tables that use change data capture

Ø  Primary key columns on tables that have change tracking

Ø  Columns that are masked (using Dynamic Data Masking)

Ø  Columns in Stretch Database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)

Ø  Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported)

Ø  Table-valued parameters targeting encrypted columns are not supported.

Ø  The following clauses cannot be used for encrypted columns:

Ø  FOR XML

Ø  FOR JSON PATH

The following features do not work on encrypted columns:

Ø    Transactional or merge replication

Ø  Distributed queries (linked servers)

Performance Impact –

Ø  I have tested inserting rows to encrypted and unencrypted columns and I can see almost it is taking double of the time to insert the data to encrypted columns compared to unencrypted columns

Ø  For reading the data unencrypted columns are taking 50% of the time form encrypted columns

Ø  And I can see one most import thing is if it is encrypted it is almost talking 3 times the space compared to unencrypted

Example -If unencrypted table is occupying 5GB and encrypted table is taking 17GB of space to store the same data

Reference –

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017

https://docs.microsoft.com/en-us/azure/security/azure-security-data-encryption-best-practices

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017