0

How do I store the sensitive information on SQL server?. We have policy guidelines, DBA and Development engineers are not allowed to view the data as plain text.

I am aware of the encryption technologies and data masking technologies but the problems is, except the person who is directly responsible for the data, no one else can access the data as per the policy.

When developers need to perform some calculation on sensitive information, how is it possible to do that, without decrypting? while still allowing SysAdmin users to view the encryption key details? I am sure, I am not the first one to implement this system. How do I design this solution? Thank you!

Edit 1:

Is it possible to create a reference column based on the original column and give it to developer for calculations? In that case, how to avoid direct logics or how to avoid people reverse engineer the logic and how is it possible to keep the encryption / hashing logic away from DBA.

I am open to any other suggestion or workarounds. Thank you!

Improve this question
3
  • Are the developers working directly on production data or do they have a staging environment?
    – Craig Efrein
    Nov 22, 2018 at 8:57
  • As of now, developers are working with sample data on staging environment.
    – Rathish Kumar B
    Nov 22, 2018 at 12:34
  • In that case, have you considered anonmyizing the data before its restored on your staging instance? Restore the DB on an instance you control, anonmyize the columns you need to protect, backup and restore that anonymized database to your staging environment/server.
    – Craig Efrein
    Nov 22, 2018 at 14:26

1 Answer 1

Reset to default
0

One alternative is to encrypt and decrypt in the app, and SQL server will of course have no knowledge that this data was born as anything else. Or, in SQL Server 2016, use Always Encrypted which means that the database API (ODBC, ADO.NET, JDBC...) does the job for you. Regardless of which solution, make sure that the DBA don't have access to the key. And, since the whole point is that the SQL Server never sees the clear-text data, you cannot manipulate using pure T-SQL. (In SQL Server 2019 will will have secure enclaves allowing for the database engine to decrypt the data to allow for things such as rotation of keys, LIKE, BETWEEN etc, but that isn't released yet.)

Improve this answer
6
  • The problem with decrypting data in app is, the developer will get access to the key. Our requirement is like, except the person directly responsible for the data, no one else should get access. For example, only Payroll head will have access to salary data and he is the one creating encryption keys. I am not sure, how this can be done, without DBA and how developers will perform calculations on salary data without decrypting.
    – Rathish Kumar B
    Nov 22, 2018 at 12:41
  • @rathishDBA if the developers have no access to the production systems, and they're developing in a staging environment, why would they ever see the real data?
    – George.Palacios
    Nov 22, 2018 at 12:58
  • @rathishDBA in AlwaysEncrypted the person who creates the encryption key does not need to be a developer or DBA. It can be, say, a Domain Administrator, who creates the Encryption Key and installs it on the Payroll head's PC, who can then use it to encrypt and decrypt the data. But you can't have the head of Payroll be the only person with a Key, since that person could leave the company.
    – David Browne - Microsoft
    Nov 22, 2018 at 13:24
  • @George, there are possibilities, where the developers can get encryption details from the application, even if they are not directly having access to production data. Our requirement does not allow this option.
    – Rathish Kumar B
    Nov 22, 2018 at 13:41
  • @DavidBrowne-Microsoft, Our requirement, the development team should be able to work on payroll data, but we cannot expose the salary details to anyone, except payroll team. The example, I stated is, only payroll head will know the key, since he already knows the data. The current options are DBA can secure the data, but the problem is 1) DBA can see the data. 2) developers cannot work on salary data without decrypting. Let me know, if there is better and secure way to handle this. Even, Domain Admin create key, app can see the plain data. We need to restrict plain data to any other user.
    – Rathish Kumar B
    Nov 22, 2018 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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