Introduction
The title of this post should have been “How to implement wildcard search functionality with Always Encrypted, make deterministic encryption safer, and load initial data using SqlBulkInsert”, but as you understand, that’s just too long for a title.
A number of years ago I built a web application with “Always Encrypted” as VARBINARY columns, before Microsoft offered this feature out of the SQL Server Box. So in case the database server would be compromised by hackers, no client details could be revealed.
Just like Microsoft my Data Access Layer, built in .NET did the encryption and decryption, transparent for other code that accessed it. And I had the same challenge how to search on encrypted columns.
So with (maybe more than average) interest I was looking forward to know how the Always Encrypted feature of SQL Server 2016 works.
And I noticed that Microsoft implements the searching differently from what I did at the time. What I did different (using randomized encryption and search columns) was my inspiration for this article.
But first a little introduction on the feature itself.
Always Encrypted is a client-side encryption technology in which data is automatically encrypted not only when it is written but also when it is read by an approved application. It’s an end-to-end encryption, therefore SQL server only sees (and stores) the encrypted version of the data.
This means that your client application needs to use an Always Encrypted enabled driver to communicate with the database.
At this time, the available Always Encrypted enabled-drivers are:
- the .NET Framework Data Provider for SQL Server, which requires installation of .NET Framework version 4.6 on the client computer
- the JDBC 6.0 driver
- the Windows ODBC driver
For more information and to download the drivers see Always Encrypted client development (on MSDN).
To be honest I hate to replicate MSDN or other sites or blog posts when I do not have to. So as an introduction I kindly refer to:
- Always Encrypted (Database Engine) (MSDN)
- New Features in SQL Server 2016 – Always encrypted (SQLShack)
How to mimic a wildcard search on Always Encrypted columns with Entity Framework
The challenges with searching on a column that is Always Encrypted are twofold:
- You can only search on an exact value, you cannot do a wildcard search.
- For the exact search you have to do a sacrifice in the area of security: you have to use deterministic encryption, which always produces the same encrypted value for a given input value. When the number of distinct values in a column is low (for instance true/false or a domain value with a limited number of values), you can imagine this can be dangerous: you could guess values by comparing the same encrypted value of other rows. Also brute force to encrypt all possible values so you can compare the encrypted values with the ones in the database is a possible threat. So you do not really want that.
Coping with these limitations is not ‘easy’. For instance to implement wildcard search functionality you have to to find out how the business users would like to search.
So if you have encrypted credit card numbers in your database, and the business users want to be able to search on the last 4 digits, you have to “do something” for that.
If the business users want to be able to search on expiration month and year of a credit card, but a security requirement is to avoid deterministic encryption because of the limited number of distinct values for those two columns (only 12 month numbers, maybe only 5 to 10 years that are still relevant), the same counts: you have to “do something” for that.
But what is “something”? Okay, to be straight with you, you will need an extra search column to implement each of these search requirements.
Follow the demo and I can show you how this works.
Preparing a database for the demo
First here are my preparation scripts to set up a demo using a CreditCard table.
If you have read the other resources mentioned above you should be able to understand what these scripts do. Also there is some comment in the scripts.
010_create_column_master_key.sql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
--\ ---) Use a separate database [MSSQL_E12_AlwaysEncryptedDemo] for the demo, ---) create this database if it does not exist yet. --/ IF NOT EXISTS ( SELECT 1 FROM sys.databases WHERE name = 'MSSQL_E12_AlwaysEncryptedDemo' ) EXEC('CREATE DATABASE [MSSQL_E12_AlwaysEncryptedDemo]'); GO USE [MSSQL_E12_AlwaysEncryptedDemo] GO --\ ---) Create a column master key, if it does not exist yet. ---) Do not use this script for your production environment! This is unsafe, as these keys are now public. ---) Generate your keys yourself, e.g. with SQL Server Management Studio. --/ IF NOT EXISTS ( SELECT 1 FROM sys.column_master_keys WHERE name = 'CMK_AlwaysEncrypted' ) BEGIN CREATE COLUMN MASTER KEY [CMK_AlwaysEncrypted] WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/ECF3CBAA957FEA693002BE905768C39E65CDE7D0' ) END GO |
020_create_column_encryption_keys.sql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
USE [MSSQL_E12_AlwaysEncryptedDemo] GO --\ ---) Create a column encryption keys, ---) for each column that needs to be encrypted a separate key. ---) Do not use this script for your production environment! This is unsafe, as these keys are now public. ---) Generate your keys yourself, e.g. with SQL Server Management Studio. --/ IF NOT EXISTS ( SELECT 1 FROM sys.column_encryption_keys WHERE name = 'CEK_CreditCard_CardNumber' ) BEGIN CREATE COLUMN ENCRYPTION KEY [CEK_CreditCard_CardNumber] WITH VALUES ( COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006500630066003300630062006100610039003500370066006500610036003900330030003000320062006500390030003500370036003800630033003900650036003500630064006500370064003000BB08A0C879710C99104A22D20A3E6554260C06C0D1449ECB71780B1D98B98CDD1281576E9A274F46765243182EEB343E96AF31D081E64D5687FE99A7C29F606D8C48CAB5CB8974BD4DEB13160F267C18B809888E6951650313208E4FAB2966B281295A6A64E59EEB4C9D00E9EFF842CFC353D0AB63613248F431A9F781F70CCCD6158D1F7D775ECED7F5532ED51B91D9741B0707BF402E13C6092278F85D6DDB35E052EB93C34654E0C67E62176CFEB9DE4C4E9B1B1A781D811DD5FA062327808E3E9E419290B801A5C63AC5BCC6DD45C5DFEEF8696379824362A90321A6063E4074B0F1533A5CEA4C53A730BD1B43C919DBCB4B8767C470E526CD83B0DE2DFF2FA5DD5923D2D177CB86431DE425384F5533425BCF6E1D72562588380E464CE349611187313D426DEACD81C1B24C7844F82DE5CC9A7C70A45CC97871972B2A44EBCD2DC384517CA200395D2DC8E1C4CB2299F29CBA3A22EA418C9853B29174EAECA0FAF2C236A9A9A43EA3B2AB620D61C34B9F98A8207387D5D4D3D460059911FD40174E04DEC571164C69C3E2FC56E5715F62EBF452A8F2961344DEAA2745187DBA710A29D110381CA0988240A00381BD4FB2CC2EA279E0EF2CF7BB833AF3DB577BB73EED528E903D1D3DE48215EAB14E4A8C007555D425DA6F32A603E4600C744488915CA1BA4A4CF85A18B19A88D221E027E6335D97B39C19AE7509845CC9 ) END GO IF NOT EXISTS ( SELECT 1 FROM sys.column_encryption_keys WHERE name = 'CEK_CreditCard_ExpMonth' ) BEGIN CREATE COLUMN ENCRYPTION KEY [CEK_CreditCard_ExpMonth] WITH VALUES ( COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006500630066003300630062006100610039003500370066006500610036003900330030003000320062006500390030003500370036003800630033003900650036003500630064006500370064003000182CFF9D4978426B25AEEC6BBDC352B4F2DC9A969675A6DF55742B8F811D314A04C3C29B3FAFD94AE6A5196AA5AEF03A9B3C05742264D9A2CA87924E5324EC1CFCB5EC7C117D3EFF5E98BBB51CC91F77DC3FAFB6FF4FBC5ABCF963D1AD91531769700941CA3E87B523C275647439A67469B8B2109F1004D8C2C69A1E1C7A8B5CEC101DDA1C3EEEC4781ED27BC706FE97064C776B508B07FB2F725BD52750CC495E002E7BCBB5BE14BEDDF137BD34B45A4BEDDD130584F115D67DFFD63FF3E702DCAAA552B68B942235F8B3F639377EF332BEF6E04468FCB5FC14F41631B7FBDF441980BBE47FFCB42D8E8D10443EB46E64870CF867114BC7268D4A8C65F631529F03F689AE68B4C583D56977E36616598F069D3B74D7BAE03317002905B308C026674691504DA47E28534EC7190EB9E14DDF8805BDE3301BEEBF271558570090F99F2157BB5387DEF887F0A9AF04E470B1711E7E624D00E639CDCD4DCE4EA9D31E5DE721E9BCE61752429545C5121FECB4A2A106F7741C56BFBB6CBE5A87B60041B6E93F84D5D4EA405BFA4E48DB46634F2A58B41B6B5C96D9105FE9FCD593DCCCAE7B8DB24AFBEF3128C023D555A6DE4B0A51FDA3F24E28DC34C52EFC1BFA745CD62D0A7962039566A14A8D4EF942B6ED5EE93FD1C309D799913550207D7BCB12EB649AEE8EC821295BAA03A4A24B56CC05611FA961606A1EAAF17221A6DB7D ) END GO IF NOT EXISTS ( SELECT 1 FROM sys.column_encryption_keys WHERE name = 'CEK_CreditCard_ExpYear' ) BEGIN CREATE COLUMN ENCRYPTION KEY [CEK_CreditCard_ExpYear] WITH VALUES ( COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0065006300660033006300620061006100390035003700660065006100360039003300300030003200620065003900300035003700360038006300330039006500360035006300640065003700640030003EB794029B7984A9E11935A2E236264663E2357988814506ACF7C3D577BE944C54F0A13FEAD63FF262AD16F40639D73EBC508729C871ED7F6E32D5B3D0A38AFA7D9D7533AD7FF128E9111B0E63BCFD29DBA55D1C4B3D9159E7E7DD38D37678A09003EC3875FBBBC321A9386B370BBB01A54BEBAFD77C2699BFC15E7745706174E40AC17BF9F712572B67A9D2B1463D62DC878F516B8867B876478F057E0569911FF19EDCA04941406C1BF0A4EFD063C5D8D200F3CB2AFD56EE99CCF16DF24DA1B3830A60F3A94BD81A1415228BDEA47CABD3EF60E8AF2826D1AF9797A0C2D356F9314FC9FDCF95C005D79B5C20374879029EF69C9E948B146FF383C4AFFE3690BE652CAAE3747E156F39B7DA7A5C407AFE67D32FF0595AEB2217C290AC555ECA2026B7059EA3029F13232DF1F8D3C04BC6A2A47DBF93738CEB1807B5F2C05E000B99D78403588CB6621FE423D3B4AEBC088E62C765C706A58787E348C4B080C598EC3F9FBCEBBEC3EB8281D8C6176CD1F9F11C8F81E5F124E2E76D0502909A1AC9EDA9EF4D94D4DD024EA9BD218C61AEE590C6CE962CF904697E738DE290B30451B1C51EF1B197BE5FAA49844F455650E948076E0B568CAA11674A2376FED2D0CF711697AA303DD1090A3590DCBC343E877E10B4ADB122F2F3DFF92303BA6F99D6E9D1D687FC962312C06717BF27A83FEEDB5F9AEA6E9793D71C7C345EE24C81 ) END GO IF NOT EXISTS ( SELECT 1 FROM sys.column_encryption_keys WHERE name = 'CEK_CreditCard_CardSearch1' ) BEGIN CREATE COLUMN ENCRYPTION KEY [CEK_CreditCard_CardSearch1] WITH VALUES ( COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006500630066003300630062006100610039003500370066006500610036003900330030003000320062006500390030003500370036003800630033003900650036003500630064006500370064003000631C1F382B3A203376A512AE63C7F56FD95BF9048AB3B421C263A520F306DFEC2A1EE33E1B6314AE3A025A793D06C684BFFD24FD521D50831ADC1397F77FEFB4FE63E90E54BF3EF212B963DD25B97D802E85FCE2C4AB9C3FE87C9982B4479D4CF9EE47E9F1413A2018F3D644B0CC6E560DB97FFC00C6EB51994C9852D8C594E54539779B7C4D7268E2F3085C4488F2A4A13B8E47DC3D2518D02FB85A87A574C1E92B910C49BFF4736DFCA2A7B57A581701021255B09F7F904D04DB4B6A0440F30C2832257CF88619B9554BBAB5B63A5714A3CEE74A03A049866094D012B153EECAA01E2C3FCF4C2EB6B388B867F9EEE5C5FC840CB7898FE8187E3244BAB4ED285883FC8FB0D5904D517E286938C2B4330DE5DEE62CEFBFF8EDD3F3E7F2239BCFD761BDAAF586F9C864006BB12F5412D0D1FB961D6DB473BF859C43839689516A97F54AC0A20826858A1FE82FE31D80C979184AB3E59C1C1A6592077E77C126AC395FCF389BB00A2230BD9D954D331D395AD20896EC6B5D8E53318DE532F6EE09FB79CCB9D0B5F21A51AC7603829833D915D4C564ADBFF286A0B1AE7310AFD5D2B38B5C6B76FBF6174E72ED9F27BE713F4CAB5AE05A11CE84875A00B94FBDC7D7BB07768AF05D28132AD991BBB960626627CEF51DFB8B1F36480F753DE20A780D0C36D8AB1C08D444FFE1279465A78434FD095B97A8A11C5205999ACC1C30C001 ) END GO IF NOT EXISTS ( SELECT 1 FROM sys.column_encryption_keys WHERE name = 'CEK_CreditCard_CardSearch2' ) BEGIN CREATE COLUMN ENCRYPTION KEY [CEK_CreditCard_CardSearch2] WITH VALUES ( COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F00650063006600330063006200610061003900350037006600650061003600390033003000300032006200650039003000350037003600380063003300390065003600350063006400650037006400300093136E46AE1774DB0C923EB802FCF4FDA4EA71D9218A6FF4F3159E7ECE0E88055C4C519C386D918B1B9298D68C5F36E998359C6D515014B108C0440DDC2B2E605AA5C38971FD162D671D621621D4A5C7CCA9AF263267EFEAAF3B3746C15EEB10610FB6822529BB4A3AF143823402C9EA557F9745C1033B57DCA596237206CFD6697CF75BE340B21C1EB96F31E81EF449BB59A933A27110BC0ED7A9AF9BB23423FC7AC031F23D696CD7AA98423AA249ADFB5F6BE359BDAB781D62143900D2E1DAAFE402F4ACD5157A3AA1A27F3F208EF37EA36240EC9D52DFB77AA441E753B695810B96174FE4CE756037AAA7F0F995CC09A2403DB50AFB7E05715A142164EF5C3626817CE429887EFBF38CB200F960DEAE313D20AAAFC4DA74C738A2FFE490B2D49C0B5E3B9C78A084D4FC1B021F42330DC231D2B88A00067F875FE19CECB92BD4D5EFB0AAEDD5489D9E11BA501EFE03513004D926988729C7DC55AD2F727A92F93478832166DD539D378BD8BAAD9AABAF40346F963BB3712206D346F3FBEB19ADA2140CDF8219EB36ECA287331BAD281D2B6E19B634D7288494AC523C1A176C90F48E3110E0EBD68A102158D09E650128E6F1A2E6CEA616BAFE7A1E50D2E3BBD1594B83BBE09E6352FF942FC13216C3B43E55ACB7BAA8072F6A74ECA37B904BE930FA0318F883218977377130E4CBADA3667E3783915DC48598836303EF3E40 ) END GO |
030_create_tables.sql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
USE [MSSQL_E12_AlwaysEncryptedDemo] GO --\ ---) First create a temporary table, without columns encrypted with Always Encrypted. ---) This is to simulate a current table in your database, or a staging table ---) that is used to get the initial encryption done fast with SqlBulkInsert. --/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CreditCardTemp]( [CreditCardID] [int] IDENTITY(1,1) NOT NULL, [CardType] [nvarchar](50) NOT NULL, [CardNumber] [nvarchar](25) NOT NULL, [ExpMonth] [tinyint] NOT NULL, [ExpYear] [smallint] NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_CreditCardTemp_CreditCardID] PRIMARY KEY CLUSTERED ( [CreditCardID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CreditCardTemp] ADD CONSTRAINT [DF_CreditCardTemp_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]; GO --\ ---) Now fill the temporary table from the [AdventureWorks].[Sales].[CreditCard] table. --/ INSERT INTO [dbo].[CreditCardTemp] ( [CardType], [CardNumber], [ExpMonth], [ExpYear], [ModifiedDate] ) SELECT [CardType], [CardNumber], [ExpMonth], [ExpYear], [ModifiedDate] FROM [AdventureWorks].[Sales].[CreditCard] GO --\ ---) Now create the [CreditCard] table, that has ---) columns that are encrypted with Always Encrypted. --/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CreditCard]( [CreditCardID] [int] IDENTITY(1,1) NOT NULL, [CardType] [nvarchar](50) NOT NULL, [CardNumber] [nvarchar](25) ENCRYPTED WITH ( ENCRYPTION_TYPE = RANDOMIZED, -- Safe but can’t be searched on ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = [CEK_CreditCard_CardNumber] ) NULL, [ExpMonth] [tinyint] ENCRYPTED WITH ( ENCRYPTION_TYPE = RANDOMIZED, -- Safe but can’t be searched on ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = [CEK_CreditCard_ExpMonth] ) NULL, [ExpYear] [smallint] ENCRYPTED WITH ( ENCRYPTION_TYPE = RANDOMIZED, -- Safe but can’t be searched on ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = [CEK_CreditCard_ExpYear] ) NULL, [CardSearch1] [char](4) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, -- Less safe but can be searched on ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = [CEK_CreditCard_CardSearch1] ) NULL, [CardSearch2] [char](7) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, -- Less safe but can be searched on ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = [CEK_CreditCard_CardSearch2] ) NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_CreditCard_CreditCardID] PRIMARY KEY CLUSTERED ( [CreditCardID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CreditCard] ADD CONSTRAINT [DF_CreditCard_ModifiedDate2] DEFAULT (getdate()) FOR [ModifiedDate] GO |
Creating a .NET Console Application with Entity Framework Core and an Entity Model.
Alright, the encryption keys and database tables are created, now we have to move over to .NET to populate the [CreditCard] table with rows of which some columns are encrypted.
After that, I’ll show you how you can mimic wildcard searches.
For the demo I created a Console Application using Visual Studio 2015 (Visual Studio 2017 seemed unstable during building the solution, so I moved back to VS2015 for now).
In this little demo program the following things will be demonstrated:
- How to populate a table that has Always Encrypted columns from an old or staging table, using SqlBulkCopy (very fast).
- How to use RANDOMIZED encryption for your columns while still be able to a ‘like’ search using a different ‘search’ column with DETERMINISTIC encryption.
- How to make the DETERMINISTIC encryption of the search columns even safer by adding a random part to it.
This is done by the example of a [CreditCard] table.
When not installed yet, download and install the Microsoft .NET Framework 4.6.2.
Then open Visual Studio and create a new Console Application. Make sure to set the .NET Framework to 4.6.1.
Now Entity Framework has to be installed. For this start the menu option Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution.
I have used the Entity Framework Core v1.1.1 which uses the .NET Framework 4.6.
To install Entity Framework Core v1.1.1 in the Browse tab search on entity framework core and select Microsoft.EntityFrameworkCore from the list.
Then select the project name on the right and press the Install button.
After reviewing the changes and accepting the license Entity Framework Core will be installed. Installation takes some time, the output window will show when it is finished.
To use Entity Framework, we have to add a model. Right-click on the project node to add a new item, then follow the steps in the pictures below:
After this, your project should look as follows:
This was not too difficult, right?
Now modify the connection string in App.config, so the connection is prepared for Always Encrypted. To do so, add
;Column Encryption Setting=enabled
at the end of the connection string, as indicated in the pictures below.
Adding code to interact with the database
As you might have noticed the [CreditCard] table contains two search columns CardSearch1 and CardSearch2, of which the values are derived from other columns, namely CardNumber, ExpMonth and ExpYear. So logic needs to be implemented to calculate the value of those columns. So add a “CreditCardSearchColumns.cs” class/file to the project. Replace the initial code in it with the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
namespace EFCoreAlwaysEncryptedDemo { using System; /// <summary> /// This partial class "CreditCard" extends the same class generated by Entity Framework. /// Every time the model regenerates this class, you have to delete public string CardSearch1 and public string CardSearch2 in CreditCard.cs /// To avoid this, edit the "AlwaysEncryptedModel.tt" t4 template that generates this class, so that it excludes those columns. /// </summary> public partial class CreditCard { /// <summary> /// CardSearch2ObfuscationLevel is the number of different encrypted values that you want for the same unencrypted value. /// A higher number is more secure, but will require more complex logic to get data out (more values to search for). /// </summary> public static int CardSearch2ObfuscationLevel { get { return 3; } } int _randomForSearchColumn; public CreditCard() { Random r = new Random(DateTime.Now.Millisecond); _randomForSearchColumn = r.Next(CardSearch2ObfuscationLevel); // Produces 0, 1 or 2 when CardSearch2ObfuscationLevel = 3 // In this case cardSearch2 can contain three possible values for a expiration month and year (e.g. 03/17): // 0032017, 1032017 and 2032017. } /// <summary> /// CardSearch1 contains the last 4 digits of the credit card number. /// </summary> public string CardSearch1 { get { return (CardNumber != null && CardNumber.Length > 4) ? CardNumber.Substring(CardNumber.Length - 4) : string.Empty; } set { } // Do nothing } /// <summary> /// CardSearch2 is in format rMMyyyy, where r=random number, MM is expiration month and yyyy is expiration year. /// </summary> public string CardSearch2 { get { // The column CardSearch2 contains the expiration month and year of the credit card // and is also encrypted using DETERMINISTIC encryption, but it has an extra level // of security: by adding a random number at the beginning of the value, the same unencrypted // values for CardSearch2 can lead to different encrypted values, although DETERMINISTIC encryption // is used! // I have kept it simple by having just 3 different random values. // When you select rows for a certain expiration month and year you have to add three encrypted // values to the WHERE clause in an IN statement. // With more random values added (e.g. 25 or 100) you security becomes even better but your // where clause also longer. return string.Format("{0}{1:00}{2:0000}", _randomForSearchColumn, ExpMonth, ExpYear); } set { } // Do nothing } } } |
This leads to a problem immediately, because both properties CardSearch1 and CardSearch2 are already in a class generated by the installed T4 template AlwaysEncryptedModel.tt.
This can be solved by deleting the two properties from CreditCard.cs:
A more elegant solution would be to adjust the AlwaysEncryptedModel.tt T4 template so that it excludes those properties when generating this file, but doing that is beyond the scope of this article.
We also need code to do the initial load of encrypted values. As you might have noticed, there is also a CreditCardTemp table in the database, that was loaded from the AdventureWorks database. This table does not contain any encrypted columns. We have to copy all rows from this table to the CreditCard table. I do not use Entity Framework for this, but the .NET Framework 4.6 Data Provider for SQL Server directly, so I can use SqlBulkInsert, which is really fast.
What needs to be done is add a class InitialLoad to the EFCoreAlwaysEncryptedDemo project, the InitialLoad.cs file has the following contents:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
using System; using System.Threading; using System.Data.SqlClient; using System.Data; namespace EFCoreAlwaysEncryptedDemo { class InitialLoad { /// <summary> /// Initial load is not done with entity framework so that SqlBulkInsert can be used, for performance reasons. /// </summary> public static void DoInitialEncryption() { // Setting up a connection, make sure this is part of the connection string: Column Encryption Setting=enabled var connectionString = "Data Source=localhost; Initial Catalog=MSSQL_E12_AlwaysEncryptedDemo;" + " Integrated Security=true; Column Encryption Setting=enabled"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Open the new target table, which is still empty, and contains columns that are Always Encrypted. string queryStringTarget = "SELECT [CreditCardID], [CardType], [CardNumber], [ExpMonth], [ExpYear], " + " [CardSearch1], [CardSearch2], [ModifiedDate] " + " FROM [dbo].[CreditCard]"; var daTarget = new SqlDataAdapter(queryStringTarget, connection); var dsTarget = new DataSet(); daTarget.Fill(dsTarget, "CreditCard"); if (dsTarget.Tables[0].Rows.Count > 0) { // Initial encryption already done. return; } // Open the temporary or old table, which does not contain any columns that are Always Encrypted. string queryStringSource = "SELECT [CreditCardID], [CardType], [CardNumber], [ExpMonth], [ExpYear], " + " [ModifiedDate]" + " FROM [dbo].[CreditCardTemp]"; var daSource = new SqlDataAdapter(queryStringSource, connection); var dsSource = new DataSet(); daSource.Fill(dsSource, "CreditCard"); int currentRow = 0; var newCreditCard = new CreditCard(); foreach (DataRow drSource in dsSource.Tables["CreditCard"].Rows) { currentRow++; DataRow drTarget = dsTarget.Tables["CreditCard"].NewRow(); // You could assign columns values to a drTarget column // from a drSource column directly. // The reason to use a CreditCard instance is that this // class will calculate CardSearch1 and CardSearch 2 // for me. Otherwise I would have to copy this logic. newCreditCard.CardType = drSource["CardType"].ToString(); newCreditCard.CardNumber = drSource["CardNumber"].ToString(); newCreditCard.ExpMonth = byte.Parse(drSource["ExpMonth"].ToString()); newCreditCard.ExpYear = short.Parse(drSource["ExpYear"].ToString()); newCreditCard.ModifiedDate = DateTime.Parse(drSource["ModifiedDate"].ToString()); drTarget["CardType"] = newCreditCard.CardType; drTarget["ModifiedDate"] = newCreditCard.ModifiedDate; // For the following columns the .NET Framework Data Provider for // SQL Server (framework 4.6) does the encryption under the hood: drTarget["CardNumber"] = newCreditCard.CardNumber; drTarget["ExpMonth"] = newCreditCard.ExpMonth; drTarget["ExpYear"] = newCreditCard.ExpYear; drTarget["CardSearch1"] = newCreditCard.CardSearch1; drTarget["CardSearch2"] = newCreditCard.CardSearch2; dsTarget.Tables["CreditCard"].Rows.Add(drTarget); if (currentRow % 100 == 0) { // To get different random values, wait a millisecond now and then Thread.Sleep(1); Console.WriteLine("{0} rows encrypted.", currentRow); } } // Now use SqlBulkCopy to get the encrypted data into the new table. Console.WriteLine("Starting SqlBulkCopy"); SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null); bulkCopy.DestinationTableName = dsTarget.Tables[0].TableName; bulkCopy.WriteToServer(dsTarget.Tables[0]); Console.WriteLine("SqlBulkCopy completed"); Console.WriteLine("Press any key to continue .."); Console.ReadKey(); } } } } |
Let’s see, where are we .. we are getting near the end, just one class to add and some code to Program.cs.
First add a class
DemoPlease to the project, add it to the file DemoPlease.cs
Paste this code into the file (replace existing code):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
using System; using System.Data; using System.Collections.Generic; using System.Linq; namespace EFCoreAlwaysEncryptedDemo { class DemoPlease { /// <summary> /// Adds a creditcard. /// </summary> /// <param name="cardType">The card type (issuer of the card)</param> /// <param name="cardNumber">The card number</param> /// <param name="expMonth">The expiration month</param> /// <param name="expYear">The expiration year</param> /// <returns>CreditCard instance</returns> public CreditCard AddCreditCard(string cardType, string cardNumber, byte expMonth, short expYear) { var newCreditCard = new CreditCard() { CardType = cardType, CardNumber = cardNumber, ExpMonth = expMonth, ExpYear = expYear, ModifiedDate = DateTime.Now }; Console.WriteLine("AddCreditCard started"); using (var context = new AlwaysEncryptedDemoEntities()) { context.CreditCards.Add(newCreditCard); context.SaveChanges(); } Console.WriteLine("AddCreditCard completed, CreditCardID = {0}", newCreditCard.CreditCardID); return newCreditCard; } /// <summary> /// Because the credit card number is encrypted in the database with Always Encrypted /// using RANDOMIZED encryption (for security reasons), you cannot directly search on it. /// However, the column CardSearch1 contains the last 4 digits of the credit card number /// and is encrypted with Always Encrypted with DETERMINISTIC encryption. /// So internally we search in the database all credit card numbers with the last 4 digits /// of the cardNumber we are looking for. /// Then in this method, we do the final selection. /// </summary> /// <param name="cardNumber"></param> /// <returns></returns> public CreditCard GetCreditCardByNumber(string cardNumber) { Console.WriteLine("GetCreditCardByNumber started"); var dictCreditCards = GetCreditCardsByLast4Digits(cardNumber); foreach (KeyValuePair<int, CreditCard> entry in dictCreditCards) { if (entry.Value.CardNumber == cardNumber) { Console.WriteLine("GetCreditCardByNumber completed: number found."); Console.WriteLine("GetCreditCardByNumber completed, CreditCardID = {0}", entry.Value.CreditCardID); return entry.Value; } } Console.WriteLine("GetCreditCardByNumber completed: number not found."); return null; } /// <summary> /// Returns a dictionary with creditcards with a credit card number that ends on the value of last4DigitsOfCardNumber. /// </summary> /// <param name="last4DigitsOfCardNumber"></param> /// <returns></returns> private Dictionary<int, CreditCard> GetCreditCardsByLast4Digits(string last4DigitsOfCardNumber) { Console.WriteLine("GetCreditCardsByLast4Digits started"); if (last4DigitsOfCardNumber.Length > 4) { last4DigitsOfCardNumber = last4DigitsOfCardNumber.Substring(last4DigitsOfCardNumber.Length - 4); } var creditCards = new Dictionary<int, CreditCard>(); using (var context = new AlwaysEncryptedDemoEntities()) { var dbCreditCards = context.CreditCards .Where(s => s.CardSearch1 == last4DigitsOfCardNumber); foreach (var creditCard in dbCreditCards) { creditCards.Add(creditCard.CreditCardID, creditCard); } } Console.WriteLine("GetCreditCardByLast4Digits completed"); return creditCards; } /// <summary> /// Deletes a credit card. /// </summary> /// <param name="creditCard"></param> public void DeleteCreditCard(CreditCard creditCard) { Console.WriteLine("DeleteCreditCard started"); using (var context = new AlwaysEncryptedDemoEntities()) { CreditCard deleteMe = new CreditCard() { CreditCardID = creditCard.CreditCardID }; context.CreditCards.Attach(deleteMe); context.CreditCards.Remove(deleteMe); context.SaveChanges(); } Console.WriteLine("DeleteCreditCard completed, CreditCardID = {0}", creditCard.CreditCardID); } /// <summary> /// Updates a credit card. /// </summary> /// <param name="creditCard"></param> public void UpdateCreditCard(CreditCard creditCard) { Console.WriteLine("UpdateCreditCard started"); if (creditCard.CreditCardID <= 0) { throw new InvalidOperationException("You cannot update a new row."); } using (var context = new AlwaysEncryptedDemoEntities()) { creditCard.ModifiedDate = DateTime.Now; context.CreditCards.Attach(creditCard); var entry = context.Entry(creditCard); var excluded = new[] { "CreditCardID" }; foreach (var name in entry.CurrentValues.PropertyNames.Except(excluded)) { entry.Property(name).IsModified = true; } context.SaveChanges(); } Console.WriteLine("UpdateCreditCard completed"); } /// <summary> /// For searching on expiration month and year we use CardSearch2 column, which is encrypted with DETERMINISTIC encryption. /// To obfuscate the encrypted value (make sure it is not always the same for the same month and year) it has a /// random number in it. So for searching we have to search on each (encrypted) value, so with every random value. /// You could make this more secure by using a hardcoded list of random values (longer than one character), /// instead of the integer value 0, 1 or 2 /// </summary> /// <param name="expMonth">The expiration month</param> /// <param name="expYear">The expiration year</param> public void GetCreditCardsByExpirationMonthAndYear(byte expMonth, short expYear) { Console.WriteLine("GetCreditCardsByExpirationMonthAndYear started"); var creditCards = new Dictionary<int, CreditCard>(); using (var context = new AlwaysEncryptedDemoEntities()) { for (int i = 0; i < CreditCard.CardSearch2ObfuscationLevel; i++) { string searchValue = string.Format("{0}{1:00}{2:0000}", i, expMonth, expYear); var dbCreditCards = context.CreditCards .Where(s => s.CardSearch2 == searchValue); foreach (var creditCard in dbCreditCards) { creditCards.Add(creditCard.CreditCardID, creditCard); } } Console.WriteLine("The following creditcards expire in month {0:00}/{1:0000}:", expMonth, expYear); int rowCount = 0; foreach (var creditCard in creditCards) { Console.WriteLine("{0} [{1}]", creditCard.Value.CardNumber, creditCard.Value.CardType); rowCount++; } Console.WriteLine("Total number of rows: {0}", rowCount); } Console.WriteLine("GetCreditCardsByExpirationMonthAndYear completed"); } } } |
Now it is time to glue everything together! To do this, paste this code into Program.cs (replace all existing code):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
using System; namespace EFCoreAlwaysEncryptedDemo { class Program { static void Main(string[] args) { InitialLoad.DoInitialEncryption(); string cardType = "Visa"; string cardNumber = "33332664695311"; byte expMonth = 11; short expYear = 2019; var demo = new DemoPlease(); // First add a Visa card. Console.WriteLine(""); Console.WriteLine("***** Adding credit card {0} *****", cardNumber); var creditCardAdded = demo.AddCreditCard(cardType, cardNumber, expMonth, expYear); // Then retrieve it. Console.WriteLine(""); Console.WriteLine("***** Get a credit card {0} from the database *****", cardNumber); var creditCard = demo.GetCreditCardByNumber(cardNumber); // Update the issuer to Diners. Console.WriteLine(""); Console.WriteLine("***** Update the issuer to Diners Club *****"); creditCard.CardType = "Diners Club"; demo.UpdateCreditCard(creditCard); // Get the card by expiration month and year, notice that the CardType is indeed updated to Diners. Console.WriteLine(""); Console.WriteLine("***** Get the card by expiration month and year, "); Console.WriteLine(" notice that the CardType is indeed updated to Diners Club *****"); demo.GetCreditCardsByExpirationMonthAndYear(expMonth, expYear); Console.WriteLine("Press any key to delete creditCard {0}..", creditCard.CardNumber); Console.ReadKey(); demo.DeleteCreditCard(creditCard); // Get the card by expiration month and year, from a month that returns multiple rows. Console.WriteLine(""); Console.WriteLine("***** Get the card by expiration month and year, from a month that returns multiple rows (01/2006). *****"); Console.WriteLine("Press any key to continue .."); Console.ReadKey(); demo.GetCreditCardsByExpirationMonthAndYear(1, 2006); Console.ReadKey(); } } } |
Now it’s time to press <F5> (or the Start Debugging from the Debug Menu)! Watch how all the methods from DemoPlease work.
Conclusion / Wrap up
In this blog post I shared my insights on how to cope with the limitations of searching on columns that were encrypted using SQL Server’s new feature Always Encrypted, by introducing extra Search columns, so that wildcard searches can be simulated and security is not weakened so much by the less safe deterministic encryption. To demonstrate this I used the Entity Framework Core v1.1.1 that uses the .NET Framework 4.6 Data Provider for SQL Server. This driver can be used to interact with Always Encrypted columns.
About best practices .. please forgive me that I took some shortcuts, which enabled me to focus on the main subject. Things to remember when building production software:
- Follow best practices for Always Encrypted when implementing it. For instance, your application should not run on the same server as the SQL Server Database Engine.
- Do not hardcode a connection string in an executable program.
He works in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on data warehouse and business intelligence projects using Microsoft technology, preferably a Data Vault and Kimball architecture.
He has a special interest in Data warehouse Automation and Metadata driven solutions.
* Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0)
* Certified in MS SQL Server: MCSA (Microsoft Certified Solutions Associate) SQL Server 2012, - MCITP Business Intelligence Developer 2005/2008, MCITP Database Developer 2005/2008, MCITP Database Administrator 2005/2008
His web site and blog is at www.hansmichiels.com, where you can find other contact and social media details.
View all posts by Hans Michiels
- Executing your own .NET console application from SSIS - April 24, 2017
- How to mimic a wildcard search on Always Encrypted columns with Entity Framework - March 22, 2017
- Temporal Table applications in SQL Data Warehouse environments - March 7, 2017