In this entry, we’ll continue with our project and illustrate how we can use SQL Server to re-identify our protected health data.
At this point, you should have installed the database, run the code to encrypt data into the TargetTable of our TestNCrypt database, and have the certificate installed and configured. If not, review the previous post.
For our implementation of encryption, we choose to store the Private Key which is used to decrypt the data apart from the SQL Server. In this way, if we back up our SQL database, we don’t have to worry that our protected data may become compromised. However, it does force us to use some non-SQL ways of re-identifying our data.
Since we using Public Key encryption, we will store the private key in the certificate store of the local server hosting SQL Server. In order to access that store, we need to configure SQL to allow it. Also, since our library is customized and we have just shown how it works in Visual Studio, we will use the CLR integration of SQL Server to call our re-identification routine.
Step by Step
- We need to configure SQL for External Access for the new assembly. However, we first need to enable Trustworthy in the database. For our TestNCrypt database, the command would be:
ALTER DATABASE TestNCrypt SET TRUSTWORTHY ON
- Next, we need to turn on the CLR if not already enabled:
sp_configure ‘clr enabled’, 1;
- Next, we need to put our certificate serial number in the SQL .NET code as we did for the Visual Studio code in Part 2. To do this, open the Decryptor project in Visual Studio and copy the Certificate Serial Number to the property: return GetCertFromSerialNumber(“0e4f958273138e974f81a5a2376f0d44”, true);
- Compile the project and note the location of the compiled library.
- Next we navigate to the Assemblies folder in SQL Management studio: TestNCrypt -> Programmability -> Assemblies and right-click to select New Assembly.
- Select the Decryptor.dll library created in Step #3.
- Set the Permission Set to External Access so that the code can read from the certificate store.
- Create a Scalar Function in SQL to “wrap” the .NET assembly. Open a New Query pointed to the TestNCrypt database and execute the following T-SQL:
execute dbo.sp_executesql @statement = N’CREATE FUNCTION [dbo].[Decrypt](@encryptedBlob [varbinary](8000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
EXTERNAL NAME [Decryptor].[UserDefinedFunctions].[Decrypt]’
- The following SQL Statements will illustrate the several states that we can show the data:
- To verify that PKI is working correctly, remove the dev certificate from the “Trusted Root Certification Authorities” store which would simulate what would happen if the certificate were invalid or not trusted.