2

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?

  • I don't see the security threat for a DBA to have access. If you don't want them to have access then don't give them permission to the instance. – Shawn Melton May 28 '15 at 10:59
  • It's a bank application. Restricting instance permission to DBA, wouldn't solve the issue that DBA using the key to decrypt confidential data – user3194125 May 28 '15 at 11:06
  • 3
    If you don't trust the DBA, don't ask the database to do encryption for you. Encrypt client side. (Note that the sysadmin of the client server can always access your data. You can't prevent that.) – Andomar May 28 '15 at 12:12
  • If you have a requirement to encrypt so that the DBA does not have access and you have Enterprise edition, then you need to learn more about Extensible Key Management systems. These are hardware add-ons in the form of expansion boards or interfaced dedicated systems that will allow your organization to separate key management and encryption functions from the data. Then neither the DBA or EKM administrator will have access to the data. – Michael Keleher Jul 29 '16 at 20:48
4

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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