Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to demonstrate the encryption and decryption by passphrase functions.

use AdventureWorks;

go

-- encryption 10 times

declare @i int, @DriverLicenseNo varchar(512), @password varchar(32)

set @i =1

set @DriverLicenseNo = 'NY1234567890'

set @Password = 'SQLUSA'

while (@i<=10)

begin

select CypherText = EncryptByPassPhrase

      (@password, @DriverLicenseNo )

set @i=@i+1

end -- while

GO

-- decryption

select ClearText = convert(varchar(128),DecryptByPassPhrase('SQLUSA'

    , 0x010000002C0EDE29B5E635700DA2999BCD3A074C8A157723C3350823394F33BE7D282B8F))

GO

select  ClearText = convert(varchar(128),DecryptByPassPhrase('SQLUSA1'

    , 0x010000002C0EDE29B5E635700DA2999BCD3A074C8A157723C3350823394F33BE7D282B8F))

GO

Related article in databasejournal.com :

Column-Level Encryption in SQL Server By Deanna Dicken