locked
SQL SERVER SYMMETRIC KEY SECURITY ISSUE RRS feed

  • Question

  • I've got a DB 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 resp.

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

    Thursday, May 28, 2015 8:38 AM

Answers

All replies

  • USE encrypt_test;
    GO
    OPEN SYMMETRIC KEY SymmetricKey1
    DECRYPTION BY CERTIFICATE Certificate1;
    GO
    -- Now list the original ID, the encrypted ID 
    SELECT statement

     -- Close the symmetric key
    CLOSE SYMMETRIC KEY SymmetricKey1;
    GO

    Accessing the Encrypted Data

    All the read access users will see the encrypted values while they do a select on table. A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values. However they do not receive any errors. In the below sample code I am running select in context of a user 'test' which has only read access on DB.

    Execute as user='DBA'
    GO
    SELECT statement

     Grant Permissions to the Encrypted Data

    Permissions can be granted to a set of users to decrypt and read data using the commands below.

    GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO DBA; 
    GO
    GRANT VIEW DEFINITION ON Certificate::Certificate1 TO DBA;
    GO    

    Refer

     
    Friday, May 29, 2015 12:47 AM
  • Hi conqueredplanet,

    Instead of making the symmetric key be encrypted by a certificate which is in turn encrypted by the database master key, you can encrypt the symmetric key by specifying a password, one known to the application.

    In this way, you can shield the password from the standard DBA toolset and keep the DBAs' eyes off the data.

    For more details, please review the following blog.
    SQL Server Encryption To Block DBAs Data Access
    http://www.mssqltips.com/sqlservertip/2840/sql-server-encryption-to-block-dbas-data-access/


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support




    Monday, June 1, 2015 2:44 AM