Encryption in SQL Server 2012–Using PassPhrase

SQL Server 2012 supports PassPhrase based encryption. PassPhrase method uses TRIPLE DES algorithm with a 128 key bit length.

The following are the 2 functions

1. EncryptByPassPhrase

2. DecryptByPassPhrase

Its very simple and easy to use as well

DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with a passphrase
DECLARE @encryptedtext varbinary(300);
SET @encryptedtext = EncryptByPassPhrase(N'p@ssw0rd', @plaintext);

-- Decrypt the data with the same passphrase
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(DecryptByPassPhrase(N'p@ssw0rd', @encryptedtext) AS nvarchar(100)) [DecryptedText]

The output will look as follows

Also you can use an Authenticator as well along with your PassPhrase

DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with a passphrase with Authenticator
DECLARE @encryptedtext varbinary(300);

SET @encryptedtext = EncryptByPassPhrase
            (
                N'p@ssw0rd', 
                @plaintext, 
                1, -- To Enable Authenticator Specify 1
                CONVERT(Varbinary, 'AuthenticatorText') -- Authenticator Text should be converted to VARBINARY
            );

-- Decrypt the data with the same passphrase with Authenticator
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(
        DecryptByPassPhrase(
                N'p@ssw0rd', 
                @encryptedtext, 
                1, 
                CONVERT(Varbinary, 'AuthenticatorText')
        ) AS nvarchar(100)) As [DecryptedText]

Hope this helps !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group. View all posts by Arunraj