SQL Server 2016 Always Encrypted – Parameterized Samples

With Always Encrypted in SQL Server 2016, if you want to Insert, Update or Filter by an encrypted column (ie. DML) the statement must be parameterized and/or use sp_executesql.

Jakub shows how to do this on his post at the SQL Server Security Blog, but here’s the condensed version of the article with all the sample scripts/instructions together and them some.

Build your encrypted DB, table, certs and try it out.

Enjoy,

Hiram

-- Discover Always Encrypted keys (none yet)
SELECT * FROM sys.column_master_keys
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values

-- Discover columns protected by Always Encrypted
SELECT * FROM sys.columns WHERE column_encryption_key_id IS NOT NULL

-- Discover columns protected by Always Encrypted
SELECT
       [column] = c.name,
       c.column_encryption_key_id,
       cek = cek.name,
       column_encryption_key_database_name = ISNULL(column_encryption_key_database_name, DB_NAME()),
       encryption_type_desc,
       encryption_algorithm_name
FROM
       sys.columns AS c
       INNER JOIN sys.column_encryption_keys AS cek ON c.column_encryption_key_id = cek.column_encryption_key_id
WHERE
       c.column_encryption_key_id IS NOT NULL

/*I. AEParameterizedSamples.sql*/
/*https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/
********************************/
DECLARE @SSN CHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients] WHERE [SSN] = @SSN

/*SSMS sends the query verbatim as a batch to SQL Server,
including the plaintext value of the @SSN variable.
As a result, the query fails with the below encryption
scheme mismatch error, because the SQL Server expects the value
targeting the SSN column to be encrypted, not in plaintext.
**********************************************************/

/*ERROR:
Msg 33299, Level 16, State 6, Line 2
Encryption scheme mismatch for columns/variables '@SSN'.
The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT')
and the expression near line '2'
expects it to be (encryption_type = 'DETERMINISTIC',
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'CEK_Auto1',
column_encryption_key_database_name = 'Clinic') (or weaker).
********/

/*II. Parameterization for Always Encrypted in SSMS 17.0*/
/*For example, if you run the above query in SSMS over a connection
with [column encryption settings=enabled] and with parameterization turned on,
a SQL Server profiler log will capture the following two RPC calls,
instead of the single batch statement, on the server side:
*********************************************************/
exec sp_describe_parameter_encryption N'DECLARE @SSN AS CHAR (11) = @pdf9f37d6e63c46879555e4ba44741aa6;
SELECT *
FROM [dbo].[Patients]
WHERE [SSN] = @SSN;
',N'@pdf9f37d6e63c46879555e4ba44741aa6 char(11)'
go

exec sp_executesql N'DECLARE @SSN AS CHAR (11) = @pdf9f37d6e63c46879555e4ba44741aa6;
SELECT *
FROM [dbo].[Patients]
WHERE [SSN] = @SSN;
',N'@pdf9f37d6e63c46879555e4ba44741aa6 char(11)',@pdf9f37d6e63c46879555e4ba44741aa6=0x01A01201846E5E924FC73155B7CC71CD05153DD09E95663F8DB34885B048E58C2D2DDDB15A6144A9CD7E6A46310590788F398CA1C216F9215992A0CF77990C9F6B
go

/*III. Getting started w/Parameterization for AE*/
/*
1. Import the CMK (column master key certificate) to the Windows Certificate Store
on your client/workstation to access it.
2. Add [column encryption setting=enabled] in the database connection string on SSMS.
Steps for an existing Query Editor window:
	1. Right-click anywhere in the Query Editor window.
	2. Click Connection, Change Connection.
	3. Click Options, Additional Connection tab.
	4. Enter: Encryption Setting=Enabled, click Connect.

3. Parameterization is disabled by default.
Steps for a query window:
	1. Right-click anywhere in the Query Editor window.
	2. Click Query Options, under Execution click Advanced.
	3. Scroll down, check [Enable Parameterization for Always Encrypted], Click OK.
Steps fpr all new Queries:
	1. Click Tools on the top menu, Options, Query Execution, SQL Server, Advanced.
	2. Check [Enable Parameterization for Always Encrypted], Click OK.
4. Now you can run the parameterized query.
5. Hover on the declare statment to see the process and which variables get Parameterized.
***********************************************/

/*IV. Requirements for Initialization Literals*/
/*
1. The value type must match the data type in the variable declaration.
2. If the data type is a date or time, the value must be in ISO 8601-compliant format.
ie. yyyy-mm-ddThh:mm:ss[.mmm]
***********************************************/
DECLARE @BirthDate date='01/04/1999'--unsupported format.
DECLARE @Number int=1.1--type mismatch. (decimal <> int)
--Error List shows unable to convert message.

/*V. Troubleshooting Server-side Type Conversion Errors*/
/*Here's an example of a successfully parameterized variable and a query.
Yet, the execution of the query fails.
********************************************************/
DECLARE @SSN NCHAR(50)='795-73-9838' --Mismatch datatype
UPDATE dbo.Patients SET SSN=@SSN WHERE PatientID=1

/*In short, the error is due to a datatype mismatch on SSN between CHAR and NCHAR.
The SSN column in the table is CHAR(11), but the variable uses NCHAR(50).
Conversions for encrypted data types are supported, but not between Unicode and ANSI strings.
********/

/*ERROR
Msg 206, Level 16, State 2, Line 3
Operand type clash: nchar(50) encrypted with (encryption_type = 'DETERMINISTIC',
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'CEK_Auto1',
column_encryption_key_database_name = 'Clinic') is incompatible with char(11)
encrypted with (encryption_type = 'DETERMINISTIC',
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'CEK_Auto1',
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2'

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption,
Line 1 [Batch Start Line 0]
Statement(s) could not be prepared.
********/
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s