How to safeguard a symmetric key in SQL Server

Question

I've got a database in which I need to store the encrypted values for a column. I use the SQL Server encryption format for encryption. The format is as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'dsagfdsagv418515adsf'

CREATE CERTIFICATE 'CERTIFICATE_NAME'

CREATE SYMMETRIC KEY 'KEY_NAME'

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE 'CERTIFICATE_NAME';

Now I am able to encrypt and decrypt the column, using EncryptByKey and DecryptByKey functions respectively.

Now I see a security threat here in a way, that the SYMMETRIC KEY is visible to DBA and other database Users. They can always decrypt that column using the key any time they want. This is not feasible in my application. Can someone please suggest what can be done to safeguard this key?

Answer 1

I have dealt a lot with PCI and HIPAA recently since we've developed the ApexSQL Audit compliance tool (some details about the tool can be found here), and during a few years of working on the tool and demoing the tool, I was engaged in numerous discussions about the status of DBA in relation to compliance rules. PCI and HIPAA are not developed to deal with "trust" but to eliminate "trust someone" as a category, so PCI DSS is quite strict in that. DBA usually is not the one who is allowed to have access to data related to compliance, and lot of customers are using our tool to place the control of the DBA as well... not because they "don't have a trust" in DBA, but because the specific compliance say/recommend that he shouldn't be trusted. Most of the security breaches are inside jobs, so PCI DSS is there to prevent inside jobs as well

Quite often question was how to prevent DBA to access the data. There are active and passive methods to do that, like segregation of duties between the DBA or attempt to limit the DBA access to some specific tables/views... but so far it seems that passive methods are more efficient. Auditing all user activities and especially activities on objects that stores the sensitive data are the crucial things (including the SELECT)... of course all security related operations (including anything related to keys) must be audited as well as most DDL activities, especially the ones related to triggers and stored procedures must be tracked as they are based on procedural programming language where someone can hide the malicious code that would otherwise be invisible

When somebody want to do something bad, he want to stay unnoticed, and if you establish the auditing of all activities which can be used for some malicious job, and ensure that nothing can go unnoticed, than your passive method in fact became the active method, or better say the pro-active method. If you can be alerted immediately on the desired events, than you can react immediately as well to deal with any security treat and prevent a damage

Just want to state that I'm working for ApexSQL and this is the reason that I mentioned our tool, but there is a bunch of other tools that dealing with compliance and auditing as well

Answer 2

I supported PCI and HIPPA compliant data as a DBA contractor for the Air Force and as a consultant for hospitals. It is part of a DBA's job to have access to data, whether it is encrypted or not.

The PCI compliance data was encrypted by the application, but I could still see it decrypted because I had access to the DLL that handled it...quick PowerShell script let me see all of it. Which I ended up using that PowerShell script to switch the data over to let SQL Server maintain the encryption.

If you do not trust your DBAs to perform their job and hold up the integrity of the system, and security of that system, then you need to bring that up with management. Your security officer (if you are at a bank you do have one of these) should be made aware of this situation. DBAs are the gate keepers of all data for an organization, it is part of the job.

Plain and simply, if you do not want them to have access remove them from the server and database instance. By remove I mean delete their account from it all.