SQL Server Security – Encryption [closed]

Posted on

Question :

I have requirement to store the some sensitive information in SQL Server 2016. Organization policy demands, it should be encrypted.

Business Requirement:

  1. Data cannot be decrypted by DBA.
  2. Data cannot be decrypted by Developers.
  3. Only end users able to access the decrypted data.

I have looked into some of the encryption options:

  • Column Level Encryption
  • Always Encrypted

But both of these options, does not satisfy the requirements. In Column Level Encryption, data can be accessed by DBA and Developers.

Always Encrypted does not allow DBAs to decrypt data but still Developers can be able to view the decrypted data, since data decrypted at the client driver.

I am thinking there must be some other method or combination of techniques can be used to achieve this requirements.

Please suggest on this. Thank you!

Answer :

There are three ways to accomplish this. Since you’ve already mentioned Always Encrypted, I must ask why you would need to give the certificate to developers in the production environment. Without the certificate, the column encryption key can’t be decrypted.

Second, you can set up column level encryption and drop the key on the certificate or asymmetric key so data can only be encrypted and not decrypted, then replicate it to a more secure server that has the private key.

The option that will most likely apply is a hardware security module. Starting in SQL Server 2008 R2 Enterprise Edition, Microsoft included interfaces to integrate Extensible Key Management systems. This is a hardware module that offloads all encryption and decryption functions, including the storage of keys. If the DBA and developers do not have permissions to this system, then they will not be able to decrypt.

Leave a Reply

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