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.
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.