Simple way of encrypting and decrypting sensitive data using phrase

table variable, ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE

DECLARE	@key varchar(50) = 'fGsltwtHgHobStwbiHsnpbhelj316'
		, @cardNumber varchar(max) = '537153001234567890'
   
DECLARE	@creditCards TABLE (
			OwnerId BIGINT
			, CardNumber VARBINARY(MAX)
		)

INSERT	INTO @creditCards
		(ownerId, cardNumber)
SELECT	1
		, ENCRYPTBYPASSPHRASE (@key, @cardNumber )

SELECT	OwnerId
		, CardNumber as 'EncryptedCardNumber'
		, CONVERT (varchar(max), DECRYPTBYPASSPHRASE (@key, cardNumber ) ) AS 'DecryptedCardNumber'
FROM	@creditCards

And here's our output:

OwnerId	EncryptedCardNumber		DecryptedCardNumber
1		0x01000000A507E0A9A		537153001234567890
		45A5C80E46734197C4D
		BA44929625CC11A754F
		618086BD4DE4A976D84
		A8ACFDDB71D5A3   

Back To Programming & Software Support