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.