How to Encrypt and Decrypt a string in SQL Server

To encrypt and decrypt a string we SQL Server 2008 and newer versions have predefined Cryptographic functions.

EncryptByPassPhrase (Transact-SQL)

This function encrypts data or string with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

Syntax:
SELECT EncryptByPassPhrase(@passphrase, @cleartext)
Input:
@passphrase
A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing a passphrase from which to generate a symmetric key.

@cleartext_B
A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing the cleartext. Maximum size is 8,000 bytes.

Returns varbinary with maximum size of 8,000 bytes.

DecryptByPassPhrase (Transact-SQL)

This function decrypts data originally encrypted with a passphrase. If you provide invalid encrypted data, it will return Null.

Syntax:
SELECT DecryptByPassPhrase(@passphrase, @ciphertext)
Input:
@passphrase
A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing a passphrase from which to generate a symmetric key.

@ciphertext
A variable of type varbinary containing data encrypted with the key. The @ciphertext variable has a maximum size of 8,000 bytes.

Returns varbinary with maximum size of 8,000 bytes. This can be converted to varchar.

SQL Query

-- Encryption

DECLARE @passphrase NVARCHAR(50), @cleartext NVARCHAR(max), @ciphertext VARBINARY(max)
SET @passphrase = 'secretKey'
SET @cleartext = 'Cloud Services: Azure, AWS, GCP'

SELECT @ciphertext = EncryptByPassPhrase(@passphrase, @cleartext)
SELECT @ciphertext

-- Output:
-- 0x02000000372BDAF0015FF1B0E2FD5108C37DE677CAA19....

-- Decryption
SELECT CONVERT(VARCHAR(100),DecryptByPassPhrase(@passphrase, @ciphertext))

-- Output:
-- Cloud Services: Azure, AWS, GCP

PreviousNext