Those DBAs eh? You just can't trust 'em....
Well, we know that statement is ridiculous, but there are so many cases where data in a SQL Server DB contains confidential data that only a very select few are allowed to see it. Take payroll data for example, you may only want the HR department to have access, or medical records for Doctors, bank details etc.
Whatever the reason, this data is most definitely not for the eyes of us grubby little DBAs....and rightly so. As DBAs, do we care about the actual contents of these DBs? No, but that's not the point, they have to be secured tightly.
This is why Microsoft introduced SQL Always Encrypted back in SQL 2016.
This is the first of several posts where overall i'll talk about:
Setting up box SQL Server always Encrypted with Azure Key Vault, and reading/writing that data using SSMS. - This is what i'm covering today.
Reading Always Encrypted data using Power BI
Writing Always Encrypted data using SSIS.
Rotating the Always Encrypted Keys.
For the purposes of this post, i'm going to show how to set up Always Encrypted for column data held in an OnPrem SQL Server (which could be MI, or Azure SQL DB ) using keys stored in Azure Key Vault. And showing you some of the many potential issues that you need to be aware of along the way.
I'm not going to talk about the Secure Enclaves flavour here, just the 'common or garden' Always Encrypted.
Well, what is it?
I'm not going to go into detail, so take a look at the excellent MS documentation on it, but essentially it allows the encryption keys to be separated and controlled application side so that the SQL Engine has no access to those keys.
The Basic basics.
For the purposes of this post, i'll be making use of Azure Key Vault
You have:
Keys - used for the encryption, usually Window Certificates, or Azure Key Vault
Used by:
Column Master Key (or multiple)
which is referenced by:
Column Encryption Key
referenced by:
Encrypted Columns in your table.
Separation of Duties
Due to the nature of this requirement, different teams may have different roles to perform in this set up and maintenance. This stops one person from being able to perform the config and ultimately see the confidential data.
Security - Create/maintain/rotate Keys
DBA - Assign keys against SQL Server objects.
Users/Service principals - Access to Open Keys to allow decryption.
PreRequisites
Azure Key Vault and Keys.
For separation of duties, the DBA Login(s) only need the Key Vault Reader role within Azure Key Vault to assign keys. This allows the pre-created Keys to be listed within SQL and assigned when creating the Column Master Key below.
The Key Administrators (ie... Security Team) will require access to manage keys, something like the Key Vault Crypto Officer role allows this.
Column Master Key
Under the Security section of your DB, you will find Always Encrypted Keys.
To start with, we need a Column Master Key, standard... Right Click, New .
You have a few options to choose from, i'm going to use Azure Key Vault
Sign in to Azure if prompted with an Account that has access to Key Vault if you aren't already. This will then show you the KeyVaults and Keys that are applicable for use.
For a DBA with separated duties, you may need a friendly Security /Azure Admin person to create a Key for you to use within the Key Vault. Your DBA account requires Key Vault Reader against the Key Vault to see and select the Keys, but crucially, this role does not grant permission to decrypt using the key.
You may see an warning in SSMS that you do not have the required permissions to list keys in the KeyVault...just after you've listed keys in the KeyVault... so ignore this.
Scripting this out after creation is like below... listing the keyvault name and Key name along with its thumbprint. Which is actually annoying when it comes to the purpose of key rotation, but more on that in a follow up post.
CREATE COLUMN MASTER KEY [AECMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
KEY_PATH = N'https://mykeyvaultname.vault.azure.net/keys/MyKey/mykeythumbprintvalue'
)
Column Encryption Key
Next, we need a column encryption key (CEK), which is secured using the Column Master Key (CMK) above.
You may have to sign into Azure again, and slightly annoyingly this requires more access than Key Vault Reader, and Key Vault Crypto User grants what is required. However, this is not a permission required long term for the role separated DBA, so either temporary role assigned (and removed post CEK creation), or someone with elevated Keyvault permissions must create the CEK. Reference your Column Master Key when creating the Column Encryption Key.
Table containing Encrypted Columns.
Now the table that actually contains our sensitive data can be created, basically, we just add the collation, CEK, type of encryption and strength to use.
CREATE TABLE dbo.EncryptedTable
(Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
LastName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AECEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
FirstName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AECEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL );Encryption Types
Deterministic - The same data is always encrypted to the same values
Random - Non Predicable encrypting of values. Note, Random Encryption cannot be used in predicates , joins, grouping or indexes but is more secure.
Note: there are more restrictions on what operations can be performed on each type of encryption, refer to Always Encrypted - SQL Server | Microsoft Learn for details of these limitations.
Why the collation?
This is a requirement as it essentially enforces case sensitivity on the encrypted column, as two values 'R' and 'r' both encrypting to the same value would not be as secure, makes sense.
Omitting this gives you the lovely message when using Deterministic encryption ....
Msg 33289, Level 16, State 38, Line 8
Cannot create or alter encrypted column 'LastName'. Character strings that do not use a *_BIN2 collation cannot be encrypted using deterministic encryption.
Right, that is our Always Encrypted objects set up. Now the fun begins of using it, all of the below focuses on just using SSMS.
Selecting Data
Lets have a look what we have (I've already added some data)
SELECT * FROM EncryptedTableAha, that's right royally encrypted that is....
But I'm logged on with an account that has access to read keys?
Ok, reconnect to your SQL Server again, and in the options when connecting, tick the Enable Always Encrypted (column encryption) option, then connect as normal.
Now running the same Select gives... (you will be prompted to sign into Azure if you haven't already. You will need permissions to Unwrap Keys (Key Vault User etc) in the Key Vault for this to work.
Predicate me.
Cool, so lets run a simple query with a WHERE clause.
SELECT * FROM EncryptedTable Where LastName='Smith'No sir, I didn't like it...
Msg 206, Level 16, State 2, Line 3
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AECEK', column_encryption_key_database_name = 'AETesting') collation_name = 'Latin1_General_CI_AS'
Here's a big kicker...
Queries that involve predicates on Always Encrypted columns must be parameterized.
And this may cause you headaches down the road, particularly with other tooling...
OK....
DECLARE @LastName nvarchar(50) = 'Smith';
SELECT * FROM dbo.EncryptedTable WHERE LastName=@LastNameNearly there....
In my SSMS, the location it tells you to enable is slightly wrong... I prefer to enable it myself, as i've experienced the query window hanging when letting it try and do it.
DECLARE @LastName nvarchar(50) = 'Smith';
SELECT * FROM dbo.EncryptedTable WHERE LastName=@LastName
Hurrah, I see you!
Inserting Data
Let us perform a single insert....
INSERT INTO EncryptedTable (LastName, FirstName) VALUES ('Edwards','Rod')Nope.
Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AECEK', column_encryption_key_database_name = 'AETesting') collation_name = 'Latin1_General_CI_AS'
Parametize me baby....
DECLARE @LastName nvarchar(50) = 'Edwards';
DECLARE @FirstName nvarchar(50) = 'Rod';
INSERT INTO dbo.EncryptedTable (LastName, FirstName)
VALUES (@LastName, @FirstName);Sorted.
Updating Data
Its the same behaviour with updates, with both with SET variable and the predicate variable having to be parameterized.
DECLARE @LastName nvarchar(50) = 'Edwards';
DECLARE @NewLastName nvarchar(50) = 'TrunkyWill';
UPDATE dbo.EncryptedTable SET LastName=@NewLastName
WHERE LastName=@LastName...will work fine, whereas:
DECLARE @LastName nvarchar(50) = 'Edwards';
UPDATE dbo.EncryptedTable SET LastName=N'TrunkyWill'
WHERE LastName=@LastNameWill fail with the same datatype incompatible errors.
Datatypes
Yes, there's more, its quite a minefield if you aren't expecting it. Lets be lazy and not match our datatypes up. The target column is nvarchar(50), lets change our param to be a varchar.
DECLARE @LastName varchar(50) = 'Edwards';
SELECT * FROM dbo.EncryptedTable WHERE LastName=@LastNameOh look, more errors.
Msg 402, Level 16, State 2, Line 18
The data types nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AECEK', column_encryption_key_database_name = 'AETesting') and varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AECEK', column_encryption_key_database_name = 'AETesting') collation_name = 'Latin1_General_CI_AS' are incompatible in the equal to operator.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 13]
Statement(s) could not be prepared.
An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@pf49d7d03c2c94a809737cbb43321019f' in statement or procedure 'DECLARE @LastName AS VARCHAR (50) = @pf49d7d03c2c94a809737cbb43321019f;
SELECT *
FROM dbo.EncryptedTable
WHERE LastName = @LastName;
' is missing in resultset returned by sp_describe_parameter_encryption.
And those are the basics for Always Encrypted, nothing that can't be coded around, but can be a bit of a shock if you're not expected it. In the next post, i'll talk about how to access this data using PowerBI Desktop, and the PowerBI service itself.
Thanks for reading
Rodecrypto.
Comentários