locked
encryptbypassphrase and encrypting by using symmetric key RRS feed

  • Question

  • I wanted to know some details about sql server encryption

    a) What is the exact difference between encryptbypassphrase  and encrypting using symmetric key which is protected by a password(while creating symmetric key it is encypted by using password not master key or certificate)

    b)Which one is more secure in those two?

    c) If i use the second method mentioned in  question a)  i.e encrypting using symmetric key which is protected by a password,then in any case after about a year if i want to change password then how it can be easily done?

    Thursday, March 14, 2013 1:45 PM

Answers

  • Hi Winman,

    First, ENCRYPTBYPASSPHRASE encrypts data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length, when a symmetric key is encrypted with a password instead of the public key of the database master key, the TRIPLE DES encryption algorithm is used, so I don’t think there is any difference between these two methods. But I suggest encrypting the symmetric key with master key or certificate, in this way, we can use other encryption methods. To change the symmetric key encryption password, please run the following commands:

    -- First, add the new password:
    alter SYMMETRIC KEY [SymmetricKeyName]
    add ENCRYPTION BY password='PasswordNEW'
    -- Then drop the old password
    alter SYMMETRIC KEY [SymmetricKeyName]
    DROP ENCRYPTION BY password='Passw0rdOLd'
    

    For more detail information, please refer to the following link:

    CREATE SYMMETRIC KEY (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms188357.aspx

    ALTER SYMMETRIC KEY (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189440(v=sql.90).aspx



    Allen Li
    TechNet Community Support

    Monday, March 18, 2013 2:31 AM