[X]

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:

 

DECLARE @origPass NVARCHAR(50),
@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
EncryptedPasswordAsVarBinary: 
0x01000000F15621B4F571B500EB29B1F81FFAAE1871CFA2965DFB5B589AC771B0D42DBF5205CB2BF678B5F0EE
DecryptedPassword: umesh.kumar

 

 

blog comments powered by Disqus

Posts By Month