0

I received the task to provide a possibility to import highly sensitive data to a SQL Server 2012. There are pretty high restrictions and requirements concerning the data:

  • Neither the Dev Team (incl. me) nor the DBAs are allowed to see the (productive) data

  • The Data has to be imported from csv files - which again will not be accessible to us

  • Only two dedicated users may have access to the mentioned data

  • For the time being the data will be processed via SSRS only, but it is possible that it has to be loaded to a cube

Now, stumbling through the web and verbosing many nice approaches such as "always encrypted" due to lag of compatibility (as mentioned: SQL Server 2012 available only) I tripped over "TDE" and wondered, if this would fulfill the requirements? If I'm not mistaken, it should cover most of the mentioned topics:

  • encryption via certificates

  • certificates can be assigned per user

  • data encryption within the DB - so users whithout the certificate can not read the data

  • certificates will be recognized by all apps such as SSRS, SSAS etc - so no special coding required here in order to perform decryption

So, if the import of the csv is correctly configured (secured path with source files etc.) and the data is stored into an encrypted database, the data should be protected pretty good...

Does anyone here already have experience concerning this topic? Has anyone got any better suggestions / other ideas how to implement the storing of highly sensitive data? Any help would be highly appreciated.

Tyron78
  • 4,117
  • 2
  • 17
  • 32
  • 3
    TDE protects data at rest, but it cannot protect against DBAs being able to see the data when not at rest. That's why Always Encrypted was invented. Without it, you essentially have to duplicate what it does, which means storing only encrypted data in the database and doing all the encryption and decryption in the clients. It's actually much easier to simply lock down the server so nobody but the two dedicated users can access it in any way. Of course, if those users are then not also developers you may have a problem... Not being able to trust DBAs makes securing data exponentially harder. – Jeroen Mostert Jul 10 '18 at 11:50

1 Answers1

2

TDE alone will not meet your needs since it only encrypts data at rest. Anyone with permissions to select from the table can view data in the clear.

To prevent DBAs from viewing the data, you must use a form of client-side column level encryption and secure the keys (e.g. certificates) such that only authorized users have access. Always Encrypted columns in SQL Server 2016 provides that capability. In earlier SQL Server versions, column level encryption must be done in the client app code in order to secure data from DBAs.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Thank you very much. I missed the part about the "data at rest" - thought it meant something like all stored data except current transaction or something like this... – Tyron78 Jul 10 '18 at 12:17