I’ve recently been researching the Microsoft SQL Server Always Encrypted feature (https://msdn.microsoft.com/en-us/library/mt163865.aspx) as part of a presentation that I am due to make to the Adelaide Dot net user group later this month (http://www.meetup.com/Adelaide-dotNET/ ).

This post is driven by my reflections on researching and preparing this talk.

The introduction of this new functionality is to be welcomed. It provides companies with more effective and useable encryption techniques for securing enterprise data, without imposing (overly) difficult to implement application programming techniques on the developers, through the updated .Net 4.6 driver capabilities.

This shift in access to encryption does, however, come with some data management health warnings. The problem space has shifted from the technical sphere towards ensuring that other management tasks are not overlooked.

And there’s nothing more annoying than locking the front door than forgetting where you put the keys (or how the lock works!).

Although it is generally good to take administrators out of the equation by encrypting data thought must still be given to ensuring the Confidentiality, Availability and Integrity (Correctness) of the encrypted business information.

That is, applying the C-I-A of Information Security to the newly encrypted system.

I think that widespread acceptance of data encryption technology like Always Encrypted will move the focus towards the following areas.

Business process owners will need to take into account the implications of using the encrypted data:

  • Whether it is necessary to hold the sensitive data at all.
  • If it is necessary to hold the data, whether it is necessary to encrypt this data
  • Reviewing and updating the disaster recovery and business continuity policy
  • Whether encryption is desirable and whether it will contribute to security and compliance with the regulatory environment (For example, In Australia some private, and all public organisations are subject to the Privacy Act 1988 which governs the handling of sensitive information relating to individuals (https://www.oaic.gov.au/privacy-law/privacy-act/)).

Architects and analysts should ensure that the following analysis is undertaken before implementation:

  • The usage of all fields that are to be encrypted needs to be evaluated before the encryption takes place, otherwise it will risk breaking the artefacts that depend upon it. As well as checking direct database dependencies, it is necessary to also look at the following
  1. Existing applications that query the database tables
  2. Web services or interfaces that utilise the encrypted data
  3. Business intelligence systems that draw the data periodically into a data warehouse
  4. Reports that are created using the database, and whether the encrypted data needs to be decrypted before display
  5. Analysis of Excel spreadsheets that import the data for analysis and business modelling
  6. Any ‘compound documents’ that are created from multiple record sources
  • Systems impacted should be analysed both from a bottom up perspective (by tracing and analysing dependencies on the database tables and fields), and by taking a top down approach (by analysing business processes to ensure that any dependencies on the encrypted fields will break). This is especially important to identify documents and systems that may have a transient or inferred dependency on the encrypted fields (e.g. MI reports may rely upon inferred (SQL) joins between tables that are now encrypted).
  • What the key rotation policy should be and how changes to the encryption keys are to be implemented and maintained.
  • What the implications for trusted suppliers are and whether any other internal or external actors (e.g. system auditors) would be affected by the decision to encrypt.

DBAs and designers are affected by:

  • The need to take into account the presence of encrypted fields in their table structures and use best practice to ensure that the encrypted fields have keys in 3rd Normal form (https://en.wikipedia.org/wiki/Third_normal_form).  It has been historically tempting (but bad design practice) to use keys like social security numbers as primary keys. Although deterministic encryption of keys can allow fields to be indexed, it will be quicker to use key values where records need to be related.
  • Whether fields are to be encrypted using Deterministic (in which case fields can be joined and grouped) or Randomised encryption (which will not allow grouping).
  • Applications developers need to ensure that constraints are added to the fields before they are inserted into the database. Using the new .Net 4.6 client the application must use the SQL Parameter object so this should be taken care of during coding.
  • Coded error handlers should ensure that the application does not output or log to file in plaintext any data fields that should be encrypted.
  • The replication and movement of data in encrypted fields can be handled using SSIS (https://blogs.msdn.microsoft.com/ssis/2015/12/17/ssis-with-always-encrypted/). This will highlight other data storage that may need access to the encryption keys.
  • Once implemented a key management and rotation policy should be put in place. The DBA should ensure that he/she is aware of how to implement SQL Server Always Encrypted key rotations.
  • Implementing different keys on development, test and production systems.

Encryption is an all or nothing option. The final decisions around whether to encrypt, and which fields to encrypt should lie with and be agreed with the business owners beforehand, and be bound to the data governance processes and policies of the organisation.

Encryption strengthens the protection afforded to sensitive data for organisations by protecting the data from unauthorised disclosure. SQL Server Always Encrypted contributes to this goal by providing a simpler route to encryption and decryption that protects the content from the administrators of the system

Introducing encryption as a business as usual operation should be the trigger that prompts the organisation to undertake a full review of the collection, retention, operations on and handling of sensitive data.