none
symmetric encryption by asymmetric keys RRS feed

  • Question

  • i've getting ready to implement encryption on a rather large database.  I'd read that if performance is of utmost concert, you should use symmetric keys.  I want to encrypt those keys by asymmetric keys.  My code is working, but i'm just not sure if there is a quicker way? do you have to open and close the key each time you select/update/insert in a stored procedure that references an encrypted column, or is there a way to just modify the code by adding the encryptbykey/decryptbykey functions? 

    has anyone implemented encryption on columns in large tables? any suggestions for me?

    Thanks,
    Pete

    here's my code to create the keys:

    create asymmetric key ASK_Auto_Encrypt
    with algorithm = RSA_512;

    create symmetric key SK_AE
    with algorithm = TRIPLE_DES
    encryption by asymmetric key ASK_Auto_Encrypt;

    here's my code to test this:

    create table encryption_test (test varchar(50));

    open symmetric key SK_AE
    decryption by asymmetric key ASK_Auto_Encrypt;

    insert into encryption_test
    select encryptbykey(key_guid('SK_AE'),'test');

    select convert(varchar(max),decryptbykey(test)) from encryption_test;

    close symmetric key SK_AE;
    Friday, November 9, 2007 2:42 PM

Answers

  •    The function you should try is DecryptByKeyAutoAsymKey. My mistake… I typically use certificates instead of asymmetric keys and my brain is starting to replace asymkeys by certs automatically.

     

      Thanks, and please let us know if this information helped

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Friday, November 9, 2007 7:03 PM
    Moderator
  •   The fact that it is returning a non-null value most likely means that the decryption operation itself worked. Most likely the root cause is the convert call. Try the decryption select statement without any conversion (i.e. get the raw decrypted binary data), from there you can figure out if the data is Unicode (nvarchar as you originally tried the conversion i.e. the data seems to have 00s on every other byte) or using a MBCS (varchar).

     

      BTW I strongly recommend against using DES algorithm. Not only because you could be using a better algorithm such as 3DES; but there are also known problems on Windows 2000.

     

      I hope this helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, November 26, 2007 5:21 AM
    Moderator
  •   It is not clear in this example if the SYMMETRIC KEY TOYOTA_SYM_KEY was protected by the ASYMMETRIC KEY named TOYOTA_ASY_KEY in addition to the password protection.

     

    You can try the following statement to see the encryption mechanisms used on the SYMMETRIC KEY:

     

    SELECT sym_keys.name, key_crypt.thumbprint,

       key_crypt.crypt_type_desc

      FROM

       sys.key_encryptions key_crypt,

       sys.symmetric_keys sym_keys,

       sys.asymmetric_keys asym_keys

      WHERE

       sym_keys.symmetric_key_id = key_crypt.key_id

       AND sym_keys.name = 'TOYOTA_SYM_KEY'

    go  

     

    You should see at least two entries, one for the password and one for the ASYMMETRIC KEY. If the entry for the asymmetric key is missing then that is the most likely cause.

     

      BTW. I also noticed that the data type for your encrypted data column is varchar instead of varbinary. I strongly recommend using a varbinary one as the encrypted data will return values that may not be interpreted correctly as characters, and I have seen code that doesn’t handle this properly (i.e. encounter invalid characters, interpreting 0x00 bytes as “null-character termination” for strings, etc.).

     

    I hope this helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Tuesday, November 27, 2007 10:32 PM
    Moderator

All replies

  •    If I understand your question, your concern is that it takes additional time to open/encrypt or decrypt/close the key for every DML operation, correct?

     

      One thing you can try, if your environment allows it, is opening the key once and keep it in the key ring for the whole session (the key ring is session bound), you will only need to open it again if you close the session for any reason (i.e. reconnect to SQL Server).

     

      For read-only operations (i.e. you need to decrypt, but not encrypt) you can also try using DecryptByKeyAutoCert, it may be a better option, specially in cases where you select multiple rows at a time.

     

      I hope this helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Friday, November 9, 2007 6:59 PM
    Moderator
  •    The function you should try is DecryptByKeyAutoAsymKey. My mistake… I typically use certificates instead of asymmetric keys and my brain is starting to replace asymkeys by certs automatically.

     

      Thanks, and please let us know if this information helped

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Friday, November 9, 2007 7:03 PM
    Moderator
  • i think that's exactly what i was looking for.  as far as encrypting goes, we'll have the app pass a quick open key statement after db session is started.

    thank you!!
    Friday, November 9, 2007 7:29 PM
  • ok i am getting closer, however i cannot get this to work properly. i'm wondering if it's a problem with my datatypes??? it encrypts the data great, but decrypting is another story.

     anyway here's my code, i gotta be doing something wrong:

    CREATE MASTER KEY DECRYPTION BY PASSWORD 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdMlk979438teag$$ds987yghn)(*&4fdg^';

    CREATE ASYMMETRIC KEY AGENT_ASY_KEY
        WITH ALGORITHM = RSA_512 ;

    CREATE SYMMETRIC KEY AGENT_SYM_KEY WITH ALGORITHM = DES
    ENCRYPTION BY ASYMMETRIC KEY AGENT_ASY_KEY;

    ALTER TABLE AGENT
        ADD EncryptedCompany varbinary(128);
    OPEN SYMMETRIC KEY AGENT_SYM_KEY
       DECRYPTION BY ASYMMETRIC KEY AGENT_ASY_KEY;
    UPDATE AGENT
    SET EncryptedCompany
        = EncryptByKey(Key_GUID('AGENT_SYM_KEY'), Company);
    GO
    --Close the key used to encrypt the data.
    CLOSE SYMMETRIC KEY AGENT_SYM_KEY;

    SELECT Company, EncryptedCompany
        AS 'Encrypted Company',
        CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('AGENT_ASY_KEY') , NULL ,EncryptedCompany))
        AS 'Decrypted Company'
        FROM agent;


    OPEN SYMMETRIC KEY AGENT_SYM_KEY
       DECRYPTION BY ASYMMETRIC KEY AGENT_ASY_KEY;
    SELECT Company, EncryptedCompany 
        AS 'Encrypted Company',
        CONVERT(nvarchar, DecryptByKey(EncryptedCompany))
        AS 'Decrypted Company'
        FROM agent;
    CLOSE SYMMETRIC KEY agent_sym_key;


    Friday, November 23, 2007 7:51 PM
  • i forgot to mention, the last 2 scripts return garbage data in the "decrypted company' column.  i'm not sure why it's not decrypting correctly.
    Friday, November 23, 2007 9:04 PM
  •   The fact that it is returning a non-null value most likely means that the decryption operation itself worked. Most likely the root cause is the convert call. Try the decryption select statement without any conversion (i.e. get the raw decrypted binary data), from there you can figure out if the data is Unicode (nvarchar as you originally tried the conversion i.e. the data seems to have 00s on every other byte) or using a MBCS (varchar).

     

      BTW I strongly recommend against using DES algorithm. Not only because you could be using a better algorithm such as 3DES; but there are also known problems on Windows 2000.

     

      I hope this helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, November 26, 2007 5:21 AM
    Moderator
  • Raul,

    This helped, thank you very much sir.  I hope MS is paying you well!!!

    - Pete
    Monday, November 26, 2007 2:13 PM
  • sorry, this is getting quite frustrating.. when i move to my prod table i still get nulls no matter what i use for the conversion.  am i creating my table incorrectly?

    here's my code again:

    create table borrower3 (firstname varchar(270), firstname2 varchar(270));

    insert into borrower3 (firstname)
    select 'GUSTAVO';

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MasterKey';

    OPEN SYMMETRIC KEY TOYOTA_SYM_KEY
       DECRYPTION BY ASYMMETRIC KEY TOYOTA_ASY_KEY
        WITH PASSWORD = 'toyotapassword';

    UPDATE borrower3
    SET firstname2
        = EncryptByKey(Key_GUID('TOYOTA_SYM_KEY'), FirstName);

    CLOSE SYMMETRIC KEY TOYOTA_SYM_KEY;

    SELECT firstname
        AS 'non decrypted fname',
        CONVERT(varchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('TOYOTA_ASY_KEY') , null ,FirstName2))
        AS 'Decrypted fname'
        FROM borrower3;

    Tuesday, November 27, 2007 2:33 PM
  •   It is not clear in this example if the SYMMETRIC KEY TOYOTA_SYM_KEY was protected by the ASYMMETRIC KEY named TOYOTA_ASY_KEY in addition to the password protection.

     

    You can try the following statement to see the encryption mechanisms used on the SYMMETRIC KEY:

     

    SELECT sym_keys.name, key_crypt.thumbprint,

       key_crypt.crypt_type_desc

      FROM

       sys.key_encryptions key_crypt,

       sys.symmetric_keys sym_keys,

       sys.asymmetric_keys asym_keys

      WHERE

       sym_keys.symmetric_key_id = key_crypt.key_id

       AND sym_keys.name = 'TOYOTA_SYM_KEY'

    go  

     

    You should see at least two entries, one for the password and one for the ASYMMETRIC KEY. If the entry for the asymmetric key is missing then that is the most likely cause.

     

      BTW. I also noticed that the data type for your encrypted data column is varchar instead of varbinary. I strongly recommend using a varbinary one as the encrypted data will return values that may not be interpreted correctly as characters, and I have seen code that doesn’t handle this properly (i.e. encounter invalid characters, interpreting 0x00 bytes as “null-character termination” for strings, etc.).

     

    I hope this helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Tuesday, November 27, 2007 10:32 PM
    Moderator
  • the entry for the asymmetric key was missing.  i removed the password restriction and everything works fine.

    i would love to change those data types to varbinary, but i have some customers that want their data encrypted, and some who do not.  do you have any suggestions on what to do in this case?
    Wednesday, November 28, 2007 1:59 PM
  •   One trick would be to create the real table with varbinary column only and always cast the result (either plaintext directly or the decrypted data) to varchar on a view. Hopefully more people in the audience may be able to give other ideas.

     

     I hope this helps,

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Wednesday, November 28, 2007 6:46 PM
    Moderator