How to mimic a wildcard search on Always Encrypted columns with Entity Framework
How to mimic a wildcard search on Always Encrypted columns with Entity Framework
Creating a new project for a Console Application Now Entity Framework has to be installed. For this start the menu option Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution.
The “Manage NuGet Packages for Solution” menu-option .. 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.
Initial steps to install EntityFramework Core v1.1.1 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.
The Output Window .. 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:
Initial steps to add a ADO.NET Entity Data Model 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. /// 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. /// public partial class CreditCard { /// /// 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). /// 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. } /// /// CardSearch1 contains the last 4 digits of the credit card number. /// public string CardSearch1 { get { return (CardNumber != null && CardNumber.Length > 4) ? CardNumber.Substring(CardNumber.Length - 4) : string.Empty; } set { } // Do nothing } /// /// CardSearch2 is in format rMMyyyy, where r=random number, MM is expiration month and yyyy is expiration year. /// 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.
Compile error for CardSearch1 (and CardSearch2) 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: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101 using System;using System.Threading;using System.Data.SqlClient;using System.Data; namespace EFCoreAlwaysEncryptedDemo{ class InitialLoad { /// /// Initial load is not done with entity framework so that SqlBulkInsert can be used, for performance reasons. /// 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): 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182 using System;using System.Data;using System.Collections.Generic;using System.Linq; namespace EFCoreAlwaysEncryptedDemo{ class DemoPlease { /// /// Adds a creditcard. /// /// The card type (issuer of the card) /// The card number /// The expiration month /// The expiration year /// CreditCard instance 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; } /// /// 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. /// /// /// 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; } /// /// Returns a dictionary with creditcards with a credit card number that ends on the value of last4DigitsOfCardNumber. /// /// /// GetCreditCardsByLast4Digits(string last4DigitsOfCardNumber) { Console.WriteLine("GetCreditCardsByLast4Digits started"); if (last4DigitsOfCardNumber.Length > 4) { last4DigitsOfCardNumber = last4DigitsOfCardNumber.Substring(last4DigitsOfCardNumber.Length - 4); } var creditCards = new Dictionary(); 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; } /// /// Deletes a credit card. /// /// 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); } /// /// Updates a credit card. /// /// 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"); } /// /// 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 /// /// The expiration month /// The expiration year public void GetCreditCardsByExpirationMonthAndYear(byte expMonth, short expYear) { Console.WriteLine("GetCreditCardsByExpirationMonthAndYear started"); var creditCards = new Dictionary(); 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): 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556 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 (or the Start Debugging from the Debug Menu)! Watch how all the methods from DemoPlease work.
Initial encryption ..
Add a credit card, retrieve it using the credit card number, update it, retrieve it by the expiration month and year
Delete confirmed ..
And finally retrieving multiple credit cards for the same expiration month and year ..
Author Recent Posts Hans MichielsHans is an Independent Business Intelligence and Data warehouse Consultant, working in the Netherlands.
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 Latest posts by Hans Michiels (see all) 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
SQLShack
SQL Server training EspañolHow to mimic a wildcard search on Always Encrypted columns with Entity Framework
March 22, 2017 by Hans MichielsIntroduction
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: 12345678910111213141516171819202122232425262728293031323334 --\---) 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' )ENDGO 020_create_column_encryption_keys.sql: 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283 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 )ENDGO 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 )ENDGO 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 )ENDGOIF 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 )ENDGOIF 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 )ENDGO 030_create_tables.sql: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112 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 ONGO SET QUOTED_IDENTIFIER ONGO 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 ONGO SET QUOTED_IDENTIFIER ONGO 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]GOCreating 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.Creating a new project for a Console Application Now Entity Framework has to be installed. For this start the menu option Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution.
The “Manage NuGet Packages for Solution” menu-option .. 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.
Initial steps to install EntityFramework Core v1.1.1 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.
The Output Window .. 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:
Initial steps to add a ADO.NET Entity Data Model 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: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 namespace EFCoreAlwaysEncryptedDemo{ using System; ///Compile error for CardSearch1 (and CardSearch2) 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: 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101 using System;using System.Threading;using System.Data.SqlClient;using System.Data; namespace EFCoreAlwaysEncryptedDemo{ class InitialLoad { ///
First add a class DemoPlease to the project, add it to the file DemoPlease.cs Paste this code into the file (replace existing code): 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182 using System;using System.Data;using System.Collections.Generic;using System.Linq; namespace EFCoreAlwaysEncryptedDemo{ class DemoPlease { ///
Initial encryption ..
Add a credit card, retrieve it using the credit card number, update it, retrieve it by the expiration month and year
Delete confirmed ..
And finally retrieving multiple credit cards for the same expiration month and year ..
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.Author Recent Posts Hans MichielsHans is an Independent Business Intelligence and Data warehouse Consultant, working in the Netherlands.
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 Latest posts by Hans Michiels (see all) 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