Mike, thanks again for your insightful article, and thanks all for this discussion. I have learned a lot.
Lately, I have been able to research the subject of database encryption extensively and have come up with a plan for implementing encryption, suited to our environment. The plan is outlined in http://www.sqlservercentral.com/Forums/Topic694420-359-1.aspx
, along with the links that helped me.
My plan incorporates many of the points made in Mike's article, especially anything to do with performance optimization. The main differences lie in the security aspect of the en(de)cryption procedure, primarily, the digital signing
of the en(de)cryption code.
Digital signing of T-SQL code achieves 2 important objectives (http://msdn.microsoft.com/en-us/library/cc837966.aspx
(1) Enables the code to run at an elevated security context, that of the digital-signing principal, relative to that of the user actually executing the code;
(2) Prohibits modifications to the signed code without first breaking the digital signature; if the latter is protected with a password, as recommended, only those with knowledge of that password can make any code changes.
In this plan:
- A code-signing certificate is created (with a complex password) and assigned to a user.
- A signature is added to each of the encryption procedures specifying the code-signing certificate.
- The code-signing user is granted permissions to control the data-access certificate, open the data-access symmetric key, and read/modify data in the tables that hold the encrypted data.
EXECUTE permissions on the encryption code are granted, as usual, to a database role containing the users permitted to execute the code through an application. These are the only permissions granted to the application role. However, because the code is digitally signed, it is executed in the security context of the code-signing certificate user, who has the power to access the encryption objects and database tables.
In that way, at least in our environment, encryption/decryption of the data can only occur through the signed code and not through ad-hoc queries issued in Management Studio. I should mention that in our organization we have created an in-house solution that enables users to gain authorization to an application through their Windows credentials, while at the same time disallowing these same users from executing the same SQL code through ad-hoc queries. The above scheme ensures that these users will ONLY be able to encrypt/decrypt data through sanctioned applications and not through SSMS (or other client tools).
The DBA team holds the keys to this process.
I must admit, I haven't yet fully tested all this, and would welcome any comments/suggestions.
The main inspiration for the above came from: http://www.thelastpickle.com/2008/01/15/how-i-think-sql-encryption-is-meant-to-be-used/
__________________________________________________________________________________SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based TablesPersisting SQL Server Index-Usage Statistics with MERGETurbocharge Your Database Maintenance With Service Broker: Part 2