1.877.833.1202

Database column encryption for sensitive metadata field values – Part 1

Thursday, May 30, 2019

FileHold software uses standard application building blocks from Microsoft such as SQL Server, IIS and dot net in its system design. This makes it possible for customers to leverage off-the-shelf solutions that best fit their IT problems without impacting the FileHold application server. A common IT problem is how to encrypt sensitive data-at-rest.

Data-at-rest encryption is often a part of good IT governance to comply with various privacy laws around the world including GDPR in Europe, PIPEDA in Canada and it is specifically required by HIPAA in the United States. It may also be required by corporate directive to avoid loss of reputation through data breach of sensitive clear text information. The basic premise is that where ever sensitive data is stored such as a logical drive, backup storage, etc. it should be encrypted in a way that is practically difficult or impossible to decode. That way if it was to get into the wrong hands it would be unintelligible.

For over 10 years SQL Server has offered transparent encryption of database columns chosen by the user. A customer’s database administrator would simply choose the columns containing sensitive information and apply the encryption. As an authorized user of the data, FileHold would be able to access the data unencrypted but any person trying to read the data without the correct authentication would see garbage. The only major downside to transparent encryption is that it requires the SQL Server Enterprise edition which excludes many customers due to the relatively high cost to license this edition.

Starting with SQL Server 2016 SP1, Microsoft has offered a feature called Always Encrypted (AE) for all SQL Server editions including the Standard Edition and free Express Edition commonly used with FileHold. AE has similarities to transparent encryption (TE) and some important differences. Unlike TE, AE requires a change to the way the application accesses SQL. On the other hand, AE hides the sensitive data from the database administrators as well from unauthorized actors. This is very useful in environments where the IT governance policies employ separation of concerns (SoC) to compartmentalize information.

Another interesting aspect of AE is that it maintains the encrypted nature of the data throughout the processing cycle. This means that if a hacker finds an OS exploit that allowed them access to your system’s memory, the sensitive data will still be hidden. You can read more about AE at http://aka.ms/AlwaysEncrypted. If you have 12 minutes to spare, there is a nice video on MSDN Channel 9 at https://channel9.msdn.com/events/DataDriven/SQLServer2016/AlwaysEncrypted.

So, you ask, “Where can I get the FileHold version that supports AE.” To that I say, “You already have it.” That’s right, if you are already running SQL Server 2016 SP1 or higher with FileHold you can take advantage of this capability with only one small change to your configuration. That said, fair warning, you will need to be an SQL and Windows server administrator and generally comfortable with technical tidbits like XML and other mean-ugly-nasty stuff. Hand this over to your IT expert now if that is not you.

Now that I am only talking to IT experts, let’s get right down to it. You need to know a couple of things to make AE work with FileHold. First, your metadata field values with sensitive information are stored in the library manager database along with all your other metadata fields. For most customers this will be named ch_librarymanager. If you have changed your database prefix just replace the ch_ with your prefix. Second, you will need to know that the connection string that FileHold uses to access the library manager database is in the library manager application web config file. Generally, you will find this in C:\Program Files\FileHold Systems\ApplicationServer\LibraryManager on your application server.

There will only be one connection string in the web config and it will look something like the following.

Database=ch_librarymanager;Server=MyServer;Integrated Security=True;

All you need to do is enable the Column Encryption Setting option. The updated connection string will look something like this.

Database=ch_librarymanager;Server=MyServer;Integrated Security=True; Column Encryption Setting=enabled;

connection string example from web config

If you fail to set this up correctly you can expect an error similar to the following in your log. System.InvalidCastException: Unable to cast object of type 'System.Byte[]' to type 'System.String'.

That’s it. The normal SQL client handles the rest. At least, that’s it for making FileHold compatible with the AE feature. You still need to define the columns you would like to encrypt in the database and define and store your encryption keys, but we will save that for part 2.

Russ Beinder portrait Russ Beinder is the Chief Technology Officer at FileHold. He is an entrepreneur, a seasoned business analyst, computer technologist and a certified Project Management Professional (PMP). For over 30 years he has used computer technology to help organizations solve business problems.