How to Create Microsoft SQL 2016 Always Encrypted Table

Issue:

I need to store PII (Personally identifiable information) data like SSN into SQL server table, but I don’t want to store as plain text as it is sensitive information. Where and how to  start encrypting my table column(s)?

Environment:

Microsoft SQL Server 2016 and Windows 10 with SQL Server Management Studio (SMS) v17

Resolution:

To encrypt SQL Server database table column(s) with Always On Encryption, please follow following sample steps.

Step-by-step instructions:-

  1. Create a Master Key by right click on “Column Master Keys” folder as shown below picture and choose “New Column Master Key…”:
    --// Or use this script to create a master key instead of GUI
    --// Step 1 Create a Master Key
    CREATE COLUMN MASTER KEY [MasterKey1]
    WITH
    (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/B6601D804D204FFF47F5155D827ECD639AAC9DAD'
    )
    GO
  2. Create an Encryption Key by right click on “Column Encryption Keys” folder and choose “New Column Encryption Key..”
    --// Step 2 Create an Encryption Key
    CREATE COLUMN ENCRYPTION KEY [EncryptKey1]
    WITH VALUES
    (
    COLUMN_MASTER_KEY = [MasterKey1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006200360036003000310064003800300034006400320030003400660066006600340037006600350031003500350064003800320037006500630064003600330039006100610063003900640061006400B327F3D2ECC9AA6611222FF39D4134D4732FB35B83109428821D4BECB39DA098CD5863B8EB3F20BF0A3D36F6514A409BBE5FC12198B1959165A89DC7B51D5FF07847085EF4ADA36CD7FE237DA95CE603FC04D2CAC692D52CB2FB4020DBCCB6F7938341394168DFA0924B7938A2A60E4C56E450AE89126BEFBE7E63FB550F8689078892968DF5EC06321B2FF70B8F2BEEBE775F9452623CF257412FB25C3BCC665ED87C078AC5E286465A46D25E0AFD053D7045CE5393AC4ED8D8AC18C6A9241D219107F2543423116F27493E68C56008CABF79EA8A537F53A136C9D0C2521BC779CBC5EEE7C8AB8F4D6E709E66EC3B0F75B83B437E51ED339AADACAC631E120C8D0A10ED84BA760A12A83E7C8A4DEE96142B45C659A879AB33616B63EEEDDCFF7EA61422B8E11A3C3B4C220FCA8C3FBB1E2861ED12C5BFBA9DD429CA7AD934B35481FF6D8D66AF541FB9846CA87C157991AC37AA8474087756C97A33F51A69729D881D60170CE53CA52D42F24D1F6BE57F1FE50FA3B2B5FC03ECA188983206AA9FC9D581C3D31BEC19C6A0C89A590A5DD2D815E8B3BB8262FD865DD6339E70489FB3568C7D0A8615F228C384C8AA5A6A5F477E8A1870A084821DF75DFDED474F5E140AE3E68BC5B467B0C0073A596EBA9D27277488519284DFD8F7B443F4141F23571DE65817CD948321A76E25AD4853F887DCEB7926468ECA89504BF2FE455D
    )
    GO
  3. Create your own table or sample table here. Make sure “EncryptKey1” match to what you have in step 2:
    --// Step 3 Sample Table 
    --// Sample Deterministic and Randomized encryption types
    CREATE TABLE [dbo].[_Test](
     Id uniqueidentifier default (newid()) PRIMARY KEY,
     LastName VARCHAR(32) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH
    (
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
    COLUMN_ENCRYPTION_KEY = EncryptKey1
    ) NOT NULL,
     FirstName NVARCHAR(32) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH
    (
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
    COLUMN_ENCRYPTION_KEY = EncryptKey1
    ) NULL,
     Ssn INT
    ENCRYPTED WITH
    (
    ENCRYPTION_TYPE = RANDOMIZED,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
    COLUMN_ENCRYPTION_KEY = EncryptKey1
    ) NOT NULL,
     SsnText CHAR(11)
    ENCRYPTED WITH
    (
    ENCRYPTION_TYPE = RANDOMIZED,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
    COLUMN_ENCRYPTION_KEY = EncryptKey1 
    ) NOT NULL
    )
    GO
  4.  To enable parameterization in SMS 17,  go to menu Query/Query Options/Execution/Advanced/, then click on checkbox to enable “Enable Parameterization for Always Encrypted” item.
  5. To enable column encryption in SMS 17, go to menu Query/Connection/Change Connection/Additional Connection Parameters/, then enter “Column Encryption Setting = enabled;” (without double quotes) in the cleartext box.
  6.  Now we can try to insert data into step 3 table. Below is sample insert with plaintext in SMS 17:
    --// Step 4 Sample Insert 
    declare @lastName VARCHAR(32) = 'test1'; 
    declare @firstName NVARCHAR(32) = 'tester'; 
    declare @ssn INT = 123456789; 
    declare @ssnText CHAR(11) = '123-45-6789'; 
    INSERT INTO [dbo].[_Test]([LastName],[FirstName],[Ssn],[SsnText]) values (@lastName,@firstName,@ssn,@ssnText); 
    GO
    
    declare @lastName VARCHAR(32) = 'test2'; 
    declare @firstName NVARCHAR(32) = 'tester'; 
    declare @ssn INT = 987654321; 
    declare @ssnText CHAR(11) = '987-65-4321'; 
    INSERT INTO [dbo].[_Test]([LastName],[FirstName],[Ssn],[SsnText]) values (@lastName,@firstName,@ssn,@ssnText); 
    GO
  7. To select data back in SMS 17,  we just write query as usual without encryption key or cert:
    --// Step 5 Sample Select
    SELECT * FROM [dbo].[_Test]
    GO

    Result:

    Id LastName FirstName Ssn SsnText
    5A648DA1-5724-4EC3-A942-270CC6F96CB8 test2 tester 987654321 987-65-4321
    7E1558D7-2BAD-45D6-8CAD-FFB5FCBEBCE6 test1 tester 123456789 123-45-6789

    Result encrypted without Step 4 and Step 5:

    Id LastName FirstName Ssn SsnText
    5A648DA1-5724-4EC3-A942-270CC6F96CB8 0x0111D7F120F402E7F808D742B78F4225AB2EE22142F40FE3BCFEC6C52FAD1C97AF75736393E338609B6644C96295DF3CAD65738F99D88EB0C572694A1F1672D306 0x0188A51BFA8BD9E9123C248EE4C1A42493B790E8B79EDA0FB6782E338C33A07FBC211D019355842F34D7AD3048411BB6CC9C6B92850B4406DCA3F961B12BA34B20 0x01B177E8D07F3EC31299E6B7FC101ADA8D5A6AB6C4BC864103FCBBCF87AA3B2AAF6B4043B7CE2FED8C8EC3995724532DD494ABBDA42C17A627DF75F67EDD952BD3 0x01FF54F6BE6A3373C62FE33C15AAE3A9B3C543C256E50DF585E82209E99CC447C728C22206D5F2D939BBA321BD64FBB5ECA4EC5ECE64137298B1BEACB75F2F643E
    7E1558D7-2BAD-45D6-8CAD-FFB5FCBEBCE6 0x01E4E50453393D9B5E7331890173F4FEE36E69F3E0C25A0243159E3456FA9A3FCB742F632B75EBC07CC6916E64CE51E8B117E6180ECE6C51DD174872B814E0DEC5 0x0188A51BFA8BD9E9123C248EE4C1A42493B790E8B79EDA0FB6782E338C33A07FBC211D019355842F34D7AD3048411BB6CC9C6B92850B4406DCA3F961B12BA34B20 0x011A8FC92D732191CC28A9D8D75396FCFF8E1AE44885E5077543D2808257F5C114CF8D75C36CAB2A9D790B934015A9AD82CCF67B684A5B1126C844EA80B32CDD8D
  8. Download Sample Always On Encryption script

Troubleshoot:

  1. If you get this error message, try “Enable Parameterization for Always Encrypted” on Step 4. If problem still exists, make sure you have correct datatype because it does conversion before execute actual select.
    Msg 33299, Level 16, State 6, Line 15
    
    Encryption scheme mismatch for columns/variables '@lastName'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '15' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'EncryptKey1', column_encryption_key_database_name = 'Your DB Name') (or weaker).
  2. If you encounter below error, you may have mismatch datatype on the column.
    Msg 33299, Level 16, State 6, Line 50
    
    Encryption scheme mismatch for columns/variables '@pe1948c8ee4a946aabd71214b49b11d84', '@lastName'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '9' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'EncryptKey1', column_encryption_key_database_name = 'Your DB Name') (or weaker).
    
    Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 41]
    
    Statement(s) could not be prepared.
    
    An error occurred while executing batch. Error message is: Internal error. The format of the resultset returned by sp_describe_parameter_encryption is invalid. One of the resultsets is missing.
  3. If you see column LastName encrypted when you do a select, try enable “Column Encryption Setting = enabled;” on Step 5
    LastName
    
    0x01CDED5BBA97F20A56D01E7F55DC667B83E388C948837B71F944C16563D40D53613E082580C87360B18B9B2247DCC077E91450AC1A2A9363E6D4AA172A031A6E05

Reference:

Parameterization for Always Encrypted – Using SSMS to Insert into, Update and Filter by Encrypted Columns