locked
Decrypt a SYMMETRIC KEY by MANY ASSYMETRIC KEY RRS feed

  • Question

  • Hello guys,

    I have many user that access my database and i need to encrypt the data. I am trying to do one thing but nothing so far.

    Please tell me if this is possible:

    I need that each user have it own ASYMMETRIC KEY.

    The column have to be encrypted by one SYMETRIC KEY.

    Every ASSYMETRIC key must be able to open the encrypted column.

    I am trying to do that:

    --User 1
    CREATE ASYMMETRIC KEY Key_JLASA01_Admin AUTHORIZATION [JLASA01_admin]
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'jlasa01_admin'
    
    --User 2
    CREATE ASYMMETRIC KEY Key_JLASA01_MDD_ICD_user AUTHORIZATION [JLASA01_MDD_ICD_user]
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'icd'
    
    --Encrypt key
    CREATE SYMMETRIC KEY SymetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY Key_JLASA01_Admin, Key_JLASA01_MDD_ICD_user
    --->>>> See here, ENCRYPTION BY MANY ASYMMETRIC KEYS, IS THAT POSSIBLE???


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com


    Monday, May 26, 2014 3:15 PM

Answers

  • Hello,

    A single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time. In your case, please refer to the following statements:

    CREATE SYMMETRIC KEY SymetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY Key_JLASA01_Admin, 
              ASYMMETRIC KEY   Key_JLASA01_MDD_ICD_user
    

    When you want to encrypting or decrypting data with the symmetric key, you can using the OPEN SYMMETRIC KEY command followed by the symmetric key's name and indicate either asymmetric key with password to decrypt the symmetric key.Please refer to the following statements:

    OPEN SYMMETRIC KEY SymmetricKey
      DECRYPTION BY ASYMMETRIC KEY Key_JLASA01_Admin 
      WITH PASSWORD = N'jlasa01_admin'
      GO
    --OR 
    OPEN SYMMETRIC KEY SymmetricKey
      DECRYPTION BY ASYMMETRIC KEY Key_JLASA01_MDD_ICD_user 
      WITH PASSWORD = N'icd'
      GO

    Reference:http://msdn.microsoft.com/en-us/library/ms188357.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support

    • Marked as answer by Fanny Liu Wednesday, June 4, 2014 12:25 PM
    Tuesday, May 27, 2014 3:10 AM
  • Hello,

    DecryptByAsymKey is used to decrypts data with an asymmetric key. In your case, you can use the DecryptByKeyAutoAsymKey or OPEN SYMMETRIC KEY  with DecryptByKey() statement to decrypts a symmetric key and uses that key to decrypt ciphertext. Please refer to the following statements:

    --OPTION ONE, using DecryptByKey()
    --1. Open the symmetric key.
    --2. Decrypt the data.
    --3. Close the symmetric key.
    OPEN SYMMETRIC KEY SymmetricKey
      DECRYPTION BY ASYMMETRIC KEY ChaveAssimetrica001 
      WITH PASSWORD = N'JnJ123@456'
      GO
    SELECT CONVERT(nvarchar, DecryptByKey(STCD1_Encrypted)) 
        AS 'Decrypted STCD1'
        FROM table_name;
    CLOSE SYMMETRIC KEY SymmetricKey;
    
    --OPTION TWO, using DecryptByKeyAutoAsymKey()
    SELECT 
        CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('ChaveAssimetrica001') , N'JnJ123@456' ,STCD1_Encrypted)) 
        AS 'Decrypted STCD1'
        FROM table_name;
    GO
    

    Reference:
    DECRYPTBYASYMKEY (Transact-SQL)
    DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Marked as answer by Fanny Liu Wednesday, June 4, 2014 12:25 PM
    Wednesday, May 28, 2014 1:27 AM

All replies

  • Hello,

    A single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys, and asymmetric keys at the same time. In your case, please refer to the following statements:

    CREATE SYMMETRIC KEY SymetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY Key_JLASA01_Admin, 
              ASYMMETRIC KEY   Key_JLASA01_MDD_ICD_user
    

    When you want to encrypting or decrypting data with the symmetric key, you can using the OPEN SYMMETRIC KEY command followed by the symmetric key's name and indicate either asymmetric key with password to decrypt the symmetric key.Please refer to the following statements:

    OPEN SYMMETRIC KEY SymmetricKey
      DECRYPTION BY ASYMMETRIC KEY Key_JLASA01_Admin 
      WITH PASSWORD = N'jlasa01_admin'
      GO
    --OR 
    OPEN SYMMETRIC KEY SymmetricKey
      DECRYPTION BY ASYMMETRIC KEY Key_JLASA01_MDD_ICD_user 
      WITH PASSWORD = N'icd'
      GO

    Reference:http://msdn.microsoft.com/en-us/library/ms188357.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here. 


    Fanny Liu
    TechNet Community Support

    • Marked as answer by Fanny Liu Wednesday, June 4, 2014 12:25 PM
    Tuesday, May 27, 2014 3:10 AM
  • Fanny liu,

    It is working perfectly, just one more thing. Before i was using something liek this to descrypt my data:

    CONVERT(VARCHAR, DecryptByAsymKey(AsymKey_ID('ChaveAssimetrica001'), STCD1_Encrypted, N'JnJ123@456'))

    But now this string is not working, how do i decrypt my data now using ASYMMETRIC KEY + SYMMETRIC KEY?


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    Tuesday, May 27, 2014 11:55 AM
  • Hello,

    DecryptByAsymKey is used to decrypts data with an asymmetric key. In your case, you can use the DecryptByKeyAutoAsymKey or OPEN SYMMETRIC KEY  with DecryptByKey() statement to decrypts a symmetric key and uses that key to decrypt ciphertext. Please refer to the following statements:

    --OPTION ONE, using DecryptByKey()
    --1. Open the symmetric key.
    --2. Decrypt the data.
    --3. Close the symmetric key.
    OPEN SYMMETRIC KEY SymmetricKey
      DECRYPTION BY ASYMMETRIC KEY ChaveAssimetrica001 
      WITH PASSWORD = N'JnJ123@456'
      GO
    SELECT CONVERT(nvarchar, DecryptByKey(STCD1_Encrypted)) 
        AS 'Decrypted STCD1'
        FROM table_name;
    CLOSE SYMMETRIC KEY SymmetricKey;
    
    --OPTION TWO, using DecryptByKeyAutoAsymKey()
    SELECT 
        CONVERT(nvarchar, DecryptByKeyAutoAsymKey ( AsymKey_ID('ChaveAssimetrica001') , N'JnJ123@456' ,STCD1_Encrypted)) 
        AS 'Decrypted STCD1'
        FROM table_name;
    GO
    

    Reference:
    DECRYPTBYASYMKEY (Transact-SQL)
    DECRYPTBYKEYAUTOASYMKEY (Transact-SQL)

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Marked as answer by Fanny Liu Wednesday, June 4, 2014 12:25 PM
    Wednesday, May 28, 2014 1:27 AM