CODEWITHSUNDEEP
sqlsql-serverencryption
LordCommanDev
LordCommanDev

Reputation: 922

DecryptByPassPhrase returns null

I'm trying to encrypt the pass of the user in my table User, column password varchar(20)

So, Im using this to encrypt:

CREATE PROCEDURE [dbo].[SP_ENCRYPTARPASSB](
@CLAVE VARCHAR(20),
@PASS VARCHAR(20) OUT 
)
AS
BEGIN
SELECT
@PASS = CONVERT(varchar(20), ENCRYPTBYPASSPHRASE('enelhogar',@CLAVE),2)
END

This works fine and returns something like this '01000000B4B51D0B8356'.

But my problem is when I try to decrypt the pass. Im using this but doesnt works:

CREATE PROCEDURE [dbo].[_DESENCRIPTARPASSB](
@CLAVE VARCHAR(20),
@PASS VARCHAR(20) OUT 
)
AS
BEGIN
SELECT
@PASS = convert(varchar(20),DecryptByPassPhrase('enelhogar',CONVERT(varchar(20),@clave,2)))
END

But always return null. I was reading about the topic but I cant find the solution.

Any idea?? THanks in advance!

Upvotes: 1

Views: 2492

Answers (1)

mjyazdani
mjyazdani

Reputation: 2035

DecryptByPassPhrase gets a varbinary variable and returns nvarchar. So you should do as below:

DECLARE @BinaryCLAVE  varbinary(1000);
SET @BinaryCLAVE =  CONVERT( varbinary(1000) , @CLAVE,1)
SELECT @PASS = DecryptByPassPhrase('enelhogar', @BinaryCLAVE ))

Upvotes: 1

Related Questions