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!

  • 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 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 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 at 12:26
  • 4
    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 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 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.