1

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!

Improve this question
12
  • 5
    AE will address your requirements if you also protect the key such that developers do not have access (similar to the DBAs).
    – Dan Guzman
    Jul 10, 2019 at 12:11
  • 6
    In your current world, the way you're looking at things, nothing will satisfy your requirements because you're saying that developers must have access to production, which if true would invalidate any option that gives Devs direct access. You could use any of these options if you don't give people direct and unfettered sysadmin to the server and all of the resources like the certificate. However, it doesn't seem as though you're open to that option.
    – Sean Gallardy
    Jul 10, 2019 at 12:22
  • 5
    Now, you give your devs access and they don't have the certificate for AE, nor are they given permissions over AE or sysadmin then they can access the data structures but not the data. If you put in another method such as only supplying temporary accounts in a JIT admin scenario for DBA's and Dev's, then you could do either of those options and still be safe. It's up to you, but when there are competing requirements, one has to give.
    – Sean Gallardy
    Jul 10, 2019 at 12:26
  • 5
    In my opinion, you're trying to solve a process issue with a technical solution. If you manage code development promotions such that developers have full access to non-production environments, but no access to production, you should be able to retain the appropriate separation of duties such that no person should have full, unfettered access to your production data.
    – John Eisbrener
    Jul 10, 2019 at 12:47
  • 2
    the point @JohnEisbrener is making (and I made as well via our Slack conversation) is that you can't solve a process issue with a technical solution. As long as the process allows developers access to the production data via any means, nothing you do will stop them from seeing the data.
    – alroc
    Jul 10, 2019 at 13:09

1 Answer 1

Reset to default
1

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.