T-SQL Password Encryption / Decryption Using Password Phrase

T-SQL - Password Encryption/Decryption

Here is a sample code which will show how to encrypt/decrypt password using password phrase in T-SQL:

We will hard code password phrase and and original password then we will encrypt the password and store it in a variable.

Declare variables:


@passPhrase NVARCHAR(50),
@encryptedPass VARBINARY(MAX),
@decryptedPass NVARCHAR(50);



Enrcyption code:


SET @origPass='umesh.kumar';
SELECT @passphrase=NEWID();
SET @encryptedPass=ENCRYPTBYPASSPHRASE(@passPhrase,@origPass) -- this method will return varbinary data
PRINT 'OriginalPassword: ' + @origPass ;
PRINT 'PasswordPhrase: ' +  @passPhrase  ;
PRINT 'EncryptedPasswordAsVarBinary: ' 
PRINT   @encryptedPass  ;



Decrypt password and check if it is mathes the original password:


SET @decryptedPass=DECRYPTBYPASSPHRASE(@passPhrase,@encryptedPass);

--SELECT sys.fn_varbintohexstr(@encryptedPass);
PRINT 'DecryptedPassword: ' +   @decryptedPass;



And below is the result:


OriginalPassword: umesh.kumar
PasswordPhrase: CFBC9D5A-B681-40BA-83E7-4512E1EB36D8
DecryptedPassword: umesh.kumar



blog comments powered by Disqus

Posts By Month