Home > SQL Server 2012, SQL Server Encryption > Encryption in SQL Server 2012–Using PassPhrase

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

image

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]

 

image

Hope this helps !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: