Encrypting Data with a PassPhrase

The simplest form of encryption is encrypting data by a PassPhrase using the EncryptByPhrase function.

EncryptByPhrase function lets you encrypt your data using a passphrase. In order to decrypt the same data, you use the DecryptByPassPhrase function. This lets you decrypt the data using the same passphrase you used in encrypting the data. THe example below demonstrate how to use a Passphrase in encrypting and decrypting data.


Declare @text varchar(100)
Declare @key varchar(100)
Declare @decrypted varchar(100)
Declare @encrypted varbinary(102) --requires two additional bytes

Set @text ='Hello World'
Set @key = 'Pa55word'

-- Encrypt the text using the @key Pasphrase(Pa55word)
Set @encrypted = EncryptByPassPhrase(@key, @text)
Select @text as 'Text to Encrypt', @encrypted as 'Ecnrypted text'

-- Deccrypt the text using the @key Pasphrase(Pa55word)
Set @decrypted = DecryptByPassPhrase(@key,@encrypted)
Select @encrypted as 'Encrypted text', @decrypted as 'Decrypted text'

Output:

-- The text has been encrypted using the PassPhrase set at @key variable
Text to Encrypt Encrypted Text
----------------------------------------------------------------------------------------
Hello World 0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF

-- The text has been decrypted using the PassPhrase set at @key variableEncrypted
Encrypted text Decrypted Text
0x010000007E380B8599411C0F7636F3D03145C84A34D260F0796182D9A9813959B6CF3BDF Hello World

Note that the PassPhrase are case sensitive. Secondly, you can only decrypt the text using the same passphrasethat were used in ecrypting the data.