Microsoft SQL Server - Always Encrypted

There is a requirement to store some sensitive information in an existing SQL table. We currently offload the most sensitive fields to a secure store, but in this instance we're choosing not go that route.

In some services we've used RSA symmetric encryption since SQL Server version may not have supported it.

With SQL Server 2016 we can play around with Always Encrypted columns.

Enable Always Encrypted

Expected that SQL Server 2016+ is already installed or MS SQL is running in docker container .

Let's ensure that the SQL Connection String includes:

Column Encryption Setting=enabled;

At this point we're just connecting through SSMS which would require either pasting the above to the Advanced Option when prompted with the Connect to Server dialog:

Select Options>>> and navigate to the Additional Connection Parameters tab:

or just check the Enable Always Encrypted (column encryption) checkbox under the Always Encrypted Tab


### Create Database
```sql
CREATE DATABASE PaymentMethod;

Create Master Key

Select your Database and expand Security -> Always Encrypted Keys. Right click on Column Master Key and click on New Column Master Key...

Alternatively:

CREATE COLUMN MASTER KEY [your_master_key_name]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/F8E7417035B396C8F505434B6A9057A02A8C6538'
)
GO

Create Column Encryption Key

Select your Database and expand Security -> Always Encrypted Keys. Right click on Column Encryption Key and click on New Column Encryption Key...

Alternatively:

CREATE COLUMN ENCRYPTION KEY [your_column_encryption_key]
WITH VALUES
(
    COLUMN_MASTER_KEY = [your_master_key_name],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0066003800650037003400310037003000330035006200330039003600630038006600350030003500340033003400620036006100390030003500370061003000320061003800630036003500330038002161FED6CFE6223E512FA213878C3A131FAFBE7A6D75AA04AB56C646373BF687EF99ED2E341600785A92F3F32E354567824F69BDF8A5E9FD851B693CCB7DFF1714D91D8940D7A1B940A97BA23C5970C3DE59B57F4005005B9CCBFC130A42FE522C71245D62987F1875A53B1D9AB04ABCAB7DFC06A1C945BE81EC74CA1CFA76940BBD42F4EF5C2D7FE41A2FA5107D1898E7FCB665B68199D2366BEE98175042340EB7C9C8A13C84E3B7CA8B93BC5CC22B94174587A69FA35085F5C1E57668DAD7D3D8C547BF854F662CC4313982005F53229E3FA1F0027AE67829BC9C9E030727142B42E5D92E627F0CFB32D5540ED4FA223F6A59665C23DDF4DFC50F579A5EDFA962DA7FEDB70FC91A82EA3CE048138881180B48FE2CD3F34CBC616D98CC824215C820A430A2A9AF1431B65B0AF131968D6DDA7127F30FC0B9F0EEB2D3603A3CBF12DA51AE65689DCE4E84460F4F82D972800278555F17E8C4EDFD170B6120053DD858F5654145538D9321EB30A3EB06E81822AA4E8E1E4F19E8D0352052EA9B3BB6FF0880DB4DECE713A97C8922D46EBD98449677BF5155F0CF6FC0209C14CA1090B0DF28A43D437B17001E9D19A3063C35FF76C4C9776C46691761892DE481BE8C2585637A66B52D5AAA84DA90089D751DD848E0072A6CBC16B2481CA2909798DEB3319FFCD2F8CD5D800ECD542A1881D4F05ECF62D7E8363CAFE844F2C763
)
GO

### Create Table

```sql
USE [PaymentMethod]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PaymentMethod](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ExternalId] [uniqueidentifier] NOT NULL DEFAULT(newid()),
    [AccountNumber] [varchar](8000) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [ExpirationMonth] [tinyint] NULL,
    [ExpirationYear] [smallint] NULL,
    [FullName] [nvarchar](255) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [PaymentType] [tinyint] NULL,
    [DateCreated] [DateTime] DEFAULT(GETUTCDATE())
) ON [PRIMARY]
GO

Insert Data

DECLARE @accountNumber [varchar](8000) = '4444333322221111'
DECLARE @fullName [nvarchar](255) = 'Testy McTestFace'

Upon running the query, SQL you may be prompted to Enable Parameterization for Always Encrypted. You want to enable this otherwise you'll get the error below.

Msg 33277, Level 16, State 6, Line 12
Encryption scheme mismatch for columns/variables '@accountNumber'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '10' expects it to be RANDOMIZED, or DETERMINISTIC, or PLAINTEXT.

If your version of SSMS didn't prompt, you can enable this way:

Click on Query -> Query Options...

On the left pane, select Advanced and scroll down on the right menu:

Select Enable Parameterization for Always Encrypted

You may also need to do the same for executions by clicking on Tools -> Options.

On the left pane select Query Execution -> Sql Server -> Advanced and select Enable Parameterization for Always Encrypted

Select Data

This is what you see when running the following query with Always Encrypted enabled connection:

SELECT * FROM PaymentMethod

If you open a new SSMS instance and don't enable Always Encrypted, you'll see:

If you try to query by the encrypted field:

DECLARE @accountNumber [varchar](36) = '4444333322221111'
SELECT * FROM PaymentMethod WHERE AccountNumber = @accountNumber

You'll get:

Msg 33277, Level 16, State 2, Line 24
Encryption scheme mismatch for columns/variables '@accountNumber', 'AccountNumber'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'PaymentMethod') and the expression near line '5' expects it to be DETERMINISTIC, or RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.
Msg 33277, Level 16, State 2, Line 24
Encryption scheme mismatch for columns/variables '@accountNumber', 'AccountNumber'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'PaymentMethod') and the expression near line '5' expects it to be DETERMINISTIC, or RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 19]
Statement(s) could not be prepared.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 19]
Statement(s) could not be prepared.
An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@pa8c9ac55a3ac4a688458c9d2fea0c6cf' in statement or procedure 'DECLARE @accountNumber AS VARCHAR (36) = @pa8c9ac55a3ac4a688458c9d2fea0c6cf;

SELECT *
FROM   PaymentMethod
WHERE  AccountNumber = @accountNumber;

' is missing in resultset returned by sp_describe_parameter_encryption.

It seems from other users that Enable Parameterization for Always Encrypted should work for this...

Connection through a .NET Application

See here.

Dapper and Always Encrypted don't work together seamlessly.

 Message: 
    Test method SqlAlwaysEncrypted.PaymentMethod.Data.Sql.Tests.PaymentMethodRepositoryTests.AddAsync_ValidPaymentMethod_Inserted threw exception: 
    Microsoft.Data.SqlClient.SqlException: Operand type clash: nvarchar(4000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'PaymentMethod') is incompatible with varchar(36) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'PaymentMethod') collation_name = 'Latin1_General_BIN2'
    Statement(s) could not be prepared.
...

This is the workaround:

[Table("PaymentMethod")]
public class PaymentMethod
{
    [Key]
    public int Id { get; set; }

    [Computed]
    public Guid ExternalId { get; set; }

    public string AccountNumber { get; set; }
    public int ExpirationMonth { get; set; }
    public int ExpirationYear { get; set; }
    public string FullName { get; set; }
    public int PaymentType { get; set; }

    [Computed]
    public DateTime DateCreated { get; set; }

    public IDynamicParameters ToDynamicParameters()
    {
        var dp = new DynamicParameters();

        dp.Add(nameof(ExpirationMonth), dbType: DbType.Int32
            , direction: ParameterDirection.InputOutput);

        dp.Add(nameof(ExpirationYear), dbType: DbType.Int32
            , direction: ParameterDirection.InputOutput);

        dp.Add(nameof(PaymentType), dbType: DbType.Int32
            , direction: ParameterDirection.InputOutput);

        // Encrypted fields
        dp.Add(nameof(AccountNumber), dbType: DbType.AnsiString
            , direction: ParameterDirection.InputOutput, size: 36);

        dp.Add(nameof(FullName), dbType: DbType.String
            , direction: ParameterDirection.InputOutput, size: 255);

        return dp;
    }
}

public async Task<int> AddAsync(PaymentMethod paymentMethod)
{
    using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync();

    // If we didn't have encrypted columns, just use Dapper.Contrib:
    //return await connection.InsertAsync(paymentMethod);

    const string query = @"INSERT INTO PaymentMethod (
        AccountNumber, ExpirationMonth, ExpirationYear, FullName, PaymentType)
        VALUES(@accountNumber, @expirationMonth, @expirationYear, @fullName, @paymentType)";

    return await connection.ExecuteAsync(query, paymentMethod.ToDynamicParameters());
}

Key Takeaways

One of my main concerns was the ability to query the table and see the the unencrypted data. This is alleviated with SSMS's Enable Parameterization for Always Encrypted option. The downside is you can't query by the encrypted field or use it in joins.
The keys can be stored in Windows certificate store or Azure Key Vault. It's worth noting that I am running SQL Server off a docker container in Linux, but the store used for encryption/decryption is on the Windows host...
The nuget package required is Microsoft.Data.SqlClient not System.Data.SqlClient. This could be a problem with older code bases with mixing the two or refactoring.
Dapper and possibly other micro-ORMs may have issues with a table that has an encrypted column. The datatype size must match exactly and there may be some issues with some datatypes.
Key management/rotation is abstracted to the DBA's domain. This is a pro or a con depending on existing secrets/key management processes (consistency) and availability of the DBA when setup/configuration and management of the keys is required.
All this testing was done with sa login. Role access for logins and key management should be taken into consideration when using this feature.

Code Chronicles