SQL SERVER 2016 – Encrypt Your PII Data – Notes from the Field #132

As a database consultant, I get to experience some pretty scary stuff. Recently I encountered a client that had all of its Personally Identifiable Information (you know the stuff that can be used to steal your identity that many states have added laws about) stored in clear text as well as presented in the webpage clear text. They are just counting down the days until a breach occurs. Literally, blows my mind, exactly like the photo to the right.

This problem frequently occurs in an enterprise company when the business makes all of the decisions based upon client request. The number one client statement, it’ll be too hard if you don’t allow us to see the <put PII column name here>.

Guess what, your customer’s PII data is your responsibility. Protecting it should be your highest priority.

I heard a story just the other day about a woman’s identity being stolen. She was recovering from cancer treatment when it was discovered that someone had purchased two cars and a condo with her identity.

Hmmm… how does that happen. Someone didn’t protect her data.

Encryption of sensitive PII data is a necessary task, especially when access to the data isn’t audited, database access isn’t hardened, and elevated permissions can be compromised. If PII data are combined with other PII data that makes the individual uniquely identifiable then it is sensitive. The most sensitive data is data that by itself it can uniquely identify an individual (and can be used to steal a person’s identity). Encrypt this data. It’s a no brainer.

I’m not going to go into all of the details about what should and should not be encrypted, I’m going to have to write a book on that subject (or you can just read the NIST “Guide to Protecting the Confidentiality of Personally Identifiable Information (PII)”).

This leads me to Encryption

Column Level Encryption

In SQL Server 2005, column level encryption was introduced. This sounds like a great solution, having the database control/manage the encryption of data. Well, if the data never has to be looked at again, and isn’t added to the database through the application, well, maybe.

If the data originates in/from the application, and the data has to be exposed in the application, SQL Server column level encryption is probably not the best solution. That means the data is potentially exposed everywhere before the database (i.e. the application, network, internet, who knows?).

.NET Framework

How about we encrypt using the .NET Framework? The .NET Framework supports encryption using the System. Security. Cryptography namespace to perform symmetric or asymmetric encryption. This allowed the encryption to happen where the data entered the application and remained encrypted all the way into the database.

SQL Server 2016

Ok, here is the fun one. In SQL Server 2016, Always Encrypted was introduced. Always Encrypted is a new feature designed to protect sensitive data, such as social security numbers. It allows clients to encrypt sensitive data inside client applications.

Always Encrypted is configured for specific columns that contain the sensitive data. It is not a solution to encrypt every column.

It is supported in SQL Server 2016 as well as Azure as SQL Database or on a virtual machine running SQL Server 2016.

There are two types of encryption for Always Encrypted:

• Deterministic which allows point lookups, equality joins, grouping and indexing on encrypted columns.
• Randomized encryption that uses a less predictable method, but prevents searching, grouping, indexing and joining on encrypted columns.

There is much, much more about Always Encrypted than can be fit into this little article, but this is a good start.

Read more about it here Always Encrypted (Database Engine).

If you haven’t already done so, download the Developer Edition of SQL Server 2016… It’s free!! Now start playing with it.