SQL Azure, Entity Framework and HIPAA

I was helping out a friend who is working on healthcare software.  They would like to run the app on Azure.  I am not sure how many of you have heard of HIPAA – Healthcare Insurance Portability and Accountability Act.  It sets some rules for anyone who writes healthcare related software.  One of the requirements is to make reasonable efforts to ensure that data at rest (read “in database”) cannot be read by unauthorized people.  The easiest approach to that would be to encrypt data at rest.  Unfortunately, SQL Azure does not support TDE – transparent data encryption of SQL Server.  Only SQL Server Enterprise edition supports this feature.  Another downside of SQL Azure is that it does not support .NET assembly integration, so that idea is out the widow.  So, the next idea I have is to put this functionality into Entity Framework. 

I already blogged here about encryption utility.  I am going to use it.  I am also working with EF 6 that was just released.  So, I only need to handle two methods when it comes to saving the data – SaveChanges and SaveChangesAsync.  Through this approach I am going to encrypt the data before sending it to the database.  I am going to write one method to encrypt an entity, based on DbEntityEntry.

        public override int SaveChanges()
        {
            Encrypt<Person>();
            return base.SaveChanges();
        }

        public override Task<int> SaveChangesAsync(System.Threading.CancellationToken cancellationToken)
        {
            Encrypt<Person>();
            return base.SaveChangesAsync(cancellationToken);
        }

 

Not let’s take a look at my Encrypt method.

        public void Encrypt<T>()
            where T : class
        {
            var changedPeople = this.ChangeTracker.Entries<T>()
                .Where(one => one.State != EntityState.Detached && one.State != EntityState.Unchanged).ToList();
            foreach (var item in changedPeople)
            {
                foreach (var propertyName in item.CurrentValues.PropertyNames)
                {
                    var value = item.CurrentValues[propertyName];
                    var stringValue = value as string;
                    if (stringValue != null)
                    {
                        if (!string.IsNullOrEmpty(stringValue))
                        {
                            item.CurrentValues[propertyName] = DecryptOneString(stringValue);
                        }
                    }
                }
            }

        }

As you can see, I get the list of changed objects of type T, then run through them, encrypting all the string properties that are filled in.  This is it, now any time I call SaveChanges, I will encrypt all the string values in Person instance.  Of course, I would have to make all identifiable information to be strings, including Date of birth, etc…  Same is the case with employment information, etc…  You can possibly make the method smarted by providing a dictionary of fields that need to be encrypted.

 

Now, the next question is querying.  Not much we can do here, besides calling Decrypt method for all the entities after they are retrieved from the database.

            using (var context = new Context())
            {
                var people = context.People.ToList();
                context.Decrypt(people);
                foreach (var item in people)
                {
                    Console.WriteLine(item.LastName);
                }
            }

This Decrypt method is very similar to Encrypt.  It actually calls a couple of other methods, each doing a specific part of the job.

        public void Decrypt<T>(IEnumerable<T> collection)
            where T: class
        {
            foreach (var item in collection)
            {
                var entry = Entry(item);
                DecryptEntity(entry);
            }
        }


        private void DecryptEntity(DbEntityEntry item)
        {
            foreach (var propertyName in item.CurrentValues.PropertyNames)
            {
                var value = item.CurrentValues[propertyName];
                var stringValue = value as string;
                if (stringValue != null)
                {
                    if (!string.IsNullOrEmpty(stringValue))
                    {
                        item.CurrentValues[propertyName] = DecryptOneString(stringValue);
                    }
                }
            }
        }

        public string DecryptOneString(string input)
        {
            return EncryptionUtility.Decrypt(input, "MyRandomPassword");
        }

 

The last part is to deal with projections.  Since they are not part of the schema, we cannot use DbEntityEntry class.  So, this code is a bit uglier.

using (var context = new Context())
            {
                var people = (from one in context.People
                              select new
                              {
                                  LastNameOfPerson = one.LastName
                              }).ToList().Select(one =>
                              {
                                  return new
                                  {
                                      LastNameOfPerson =
                                      string.IsNullOrEmpty(one.LastNameOfPerson)
                                            ? one.LastNameOfPerson
                                            : context.DecryptOneString(one.LastNameOfPerson)
                                  };
                              });

                foreach (var item in people)
                {
                    Console.WriteLine(item.LastNameOfPerson);
                }
            }

Done.  You can download sample project here.  You will need to restore EF 6 Nuget package before running the sample.  I also timed it.  It takes about 90 ms to encrypt Person object with 3 fields.  Not great, but what is the alternative?

Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *