In my previous entry, I summarized the rationale for our design decisions. In this entry, I’ll dive into the technical implementations and share some code.
Create a Test Certificate
The first step is to install a new server certificate on the SQL Server. In order to support things such as certificate revocation, this certificate should be issued by an Enterprise Certificate Authority. However, it is possible to use a certificate generate by a tool such as MakeCert.
- Open a Visual Studio Command Prompt as administrator.
- Edit and execute the following command:
makecert “c:tempmycert.cer” -a sha1 -n “cn= dev” -sr LocalMachine -ss My -pe -len 2048 -r -sky exchange
Note 1: You should convert the “cn=dev” to the fully qualified name of the server that hosts your SQL Server. For this example, we assume a single/same server installation.
Note 2: If you use a multi-server configuration; that is, if you encrypt on a machine that is remote from the SQL Server, install the public/private key pair on the SQL Server and then export or copy the public key file (the .cer file) to the development machine. If you run the command above, you can get the public key file from “c:tempmycert.cer”
Note 3: Of course you can also use a qualified Certificate Authority from your organization or a Public “CA” if available.
Configure the Test Certificate
- Double-click the mycert.cer file created by the Makecert command above. You will note that the certificate is not trusted. The code has a switch that allows you to run either trusted or not trusted certificates. However, in production you should only use trust and leave the switch to TRUE.
- Open the Local Machine Certificate Store. Run -> MMC.exe
- From the dialog: File -> Add/Remove Snapins -> Certificates -> Add -> Computer Account etc
- Open Certificates (Local Computer) -> Personal -> Certificates and you should see a certificate in the right panel with the name for your server. In my example, that is called “dev”. Select the certificate and right-click and select “Copy”
- In the left panel, expand the leaf “Trusted Root Certification Authorities” and then “Certificates”. With Certificates open, paste the “dev” certificate into the store.
- Navigate back up to the Personal /Certificates store and double click the “dev” certificate to open it again and note that the warning that the certificate is not trusted has now disappeared.
Configure the Code to Use the Certificate Serial Number
To encrypt, you can either configure the code to use the public key file or the serial number of the new certificate. For our example, we’ll use the serial number.
- Double click the certificate in either the certificate store or on the file system to open the certificate applet
- Click the “Details” tab.
- Click the Serial Number value and copy the value such as (0e 4f 95 82 73 13 8e 97 4f 81 a5 a2 37 6f 0d 44)
- Open the NCryptLibs project in Visual Studio and then the Utils.cs file and navigate to the X509Certificate2 property section.
- Comment out the File System section of code and uncomment the serial number section and paste your serial number into the code. Remove leading and internal spaces so that your property code section should look like:
Open the Visual Studio Project
Copy and open the NCrypt solution (download from here). It is comprised of three projects: NCryptLibs which is the base encryption library functions; Decryptor which is the sample SQL Database project; and NCrypt which is the launching sample program.
Configure the SQL Server Database & Execute the Program
Open the TestDB.SQL file. Review the settings and make changes such as destination paths that you deem necessary. This file takes some data from the Adventure Works sample database so we can test our functions
For example, to create a one-way, cryptographic hash of the Last Name so that we can join it across other tables, we would call the API as:
targetRow.LastName = Utils.GetHash(row.LastName);
And to save the reversible encrypted or de-identified value, we call the API with:
targetRow.LastNameX = Utils.GetEncryptedBuffer(row.LastName);
Run the program and review the values in the Target table. The following SQL code should help illustrate both the de-identified (encrypted) data fields as well as the re-identified (decrypted) data:
Select * from SourceTable where id = 1
Select * from TargetTable where id = 1
SELECT [id],[FirstName],[MiddleName],[LastName],’Encrypted’ as EmailAddress,[MRN],[JobTitle],[City],[State],[Zip]
where id = 1
dbo.Decrypt([LastNameX]) as LastName,
dbo.Decrypt(EmailAddressX) as EmailAddressEmailAddress,
dbo.Decrypt(MRNX) as MRN,
where id = 1
A sample output from above would look something like:
Some things to note:
- You would never choose to show the encrypted values such as LastNameX. Instead, you may want to retrieve the values such as: Select FirstName, “Encrypted” as LastName, from MyTable. Unless of course you want to show the re-identified values – more in a bit.
- The one way hash length has a somewhat adjustable length depending on the library you use to create the hash. For example, SHA512 creates a longer hash than SHA256.
- To store the hash in a more human readable form, we convert to Base64 string.
Now that our data has been de-identified, let’s see how easy it is to re-identify when we need it.
We will use Visual Studio to re-identify our fictitious CEO’s email address. His record is in Row 1 and so the following code will retrieve the name:
TargetTable dbRow = (from r in ctx.TargetTables where r.id == 1 select r).First();
string myCeoAddress = Utils.GetDecryptedString(dbRow.EmailAddressX.ToArray());
All fields that hold PHI are now fully encrypted. As illustrated in our fictitious MRN values, we have two types of encrypted data. The first is a reversible encrypted binary “blob” that uses Public Key Cryptography to “hide” our data. If we use a certificate issued by our Enterprise CA and enable Valid Certificate checking, the data can be be re-identified.
In order to join values from one row to another, we can use the one-way hash value that is cryptographically secure but that is unique to that value (in other words, all instances of the same data value will have the same hash value).
If we fully trust certain users, we can give them access to the private key so that they can decrypt the data locally.
In our next post, we’ll show how to decrypt data in SQL.
Get the project code here.