locked
how encryption and decryption done in the PassbyPhrase Encryption? RRS feed

  • Question

  • Hi ,

             I tried this with the following script :

    create

    table Encrypt_Decrypt(username varchar(300),Password varchar(100),Encrypted_PWD varbinary(256),Decrypted_PWD varchar(100

    ))

    insert

    into Encrypt_Decrypt(username,Password) select 'chaithu',

    'aimhigh'

    union

    all

    select

    'kranthi',

    'something'

     

    DECLARE

    @PassphraseEnteredByUser nvarchar(128

    );

    SET

    @PassphraseEnteredByUser

     

    = 'Encrypt it';

     

    UPDATE

    Encrypt_Decrypt

    SET

    Encrypted_PWD = EncryptByPassPhrase(@PassphraseEnteredByUser

     

    , Password);

     

     

    /*DECLARE @PassphraseEnteredByUser nvarchar(128);

    SET @PassphraseEnteredByUser

    = 'Encrypt it'; */

    update

    Encrypt_Decrypt set Decrypted_PWD=DECRYPTBYPASSPHRASE(@PassphraseEnteredByUser,Encrypted_PWD)

     

     

     

     

     

    But, I am unable to figure out how internally it is performing encryption and decryption.Please help me

     

    Tuesday, May 17, 2011 12:37 PM

Answers

  • Hi,

    The example script above is to Encrypt and Decrypt a text. You can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE function. The EncryptByPassPhrase function uses the Triple DES algorithm to encrypt the text passed in. The passphrase is used as  Symmetric key.
    Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE',‘text’)
     In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key, and 'text' data type should be VarBinary.
    DECRYPTBYPASSPHRASE function takes two arguments one is 'PASSPHRASE'and text or column_name.
    Please refer to the online documents for more details:
    • ENCRYPTBYPASSPHRASE (Transact-SQL)
    • DECRYPTBYPASSPHRASE (Transact-SQL)
    • Choosing an Encryption Algorithm

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, May 19, 2011 7:31 AM
    Answerer

All replies

  • I do not think that MS has been publishing such info for end users.... Probably someone from the MS can answer you...
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, May 18, 2011 5:19 AM
  • Hi,

    The example script above is to Encrypt and Decrypt a text. You can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE function. The EncryptByPassPhrase function uses the Triple DES algorithm to encrypt the text passed in. The passphrase is used as  Symmetric key.
    Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE',‘text’)
     In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key, and 'text' data type should be VarBinary.
    DECRYPTBYPASSPHRASE function takes two arguments one is 'PASSPHRASE'and text or column_name.
    Please refer to the online documents for more details:
    • ENCRYPTBYPASSPHRASE (Transact-SQL)
    • DECRYPTBYPASSPHRASE (Transact-SQL)
    • Choosing an Encryption Algorithm

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, May 19, 2011 7:31 AM
    Answerer