SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.
ENCRYPTION by passphrase
ENCRYPTION by symmetric keys
ENCRYPTION by Asymmetric keys
ENCRYPTION by certificates
“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.
select EncryptedData = EncryptByPassPhrase('aad', 'abc' )
select convert(varchar(100),DecryptByPassPhrase('aad'
, 0x01000000563148628C8E8CCAC2114DB23AE4E525AAB518C57F3F51CA))
The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.
SQL Server generates NULL as the result when the password is wrong.
