ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE is not returning correct values
The problem is that you're encrypting a varchar value and casting the decrypted value to an nvarchar. Those two datatypes have different byte structures. It's easily fixed, though (literally one character):
declare @decryptedValue nvarchar(4000)
declare @encryptedValue varbinary(8000)
SET @encryptedValue = ENCRYPTBYPASSPHRASE('SQL SERVER 2008',N'SomeValue') --note the "N" before "N'SomeValue'"
Set @decryptedValue = DECRYPTBYPASSPHRASE('SQL SERVER 2008',@encryptedValue)
print @decryptedValue
As to why it worked with your functions, because the arguments to the functions are themselves typed, you're getting an implicit conversion. So, when you provide 'SomeValue'
to the Encrypt
function, it gets converted to an nvarchar version of that and that is passed to encryptbypassphrase()
.
Simsons
Love to write programs . Still learning and trying to explain the code to my self and others.
Updated on July 27, 2022Comments
-
Simsons almost 2 years
I am trying to encrypt and decrypt using following code:
declare @decryptedValue nvarchar(4000) declare @encryptedValue varbinary(8000) SET @encryptedValue = ENCRYPTBYPASSPHRASE('SQL SERVER 2008','SomeValue') Set @decryptedValue = DECRYPTBYPASSPHRASE('SQL SERVER 2008',@encryptedValue) print @decryptedValue
But the decrypted value is not what I passed in ('SomeValue') , but some encrypted charecter 潓敭慖畬e.
Strangely if I create two functions like below it works:
CREATE FUNCTION dbo.Encrypt(@str nvarchar(4000)) RETURNS varbinary(8000) AS BEGIN DECLARE @res varbinary(8000) SET @res = ENCRYPTBYPASSPHRASE('SQL SERVER 2008',@str) RETURN (@res) END GO CREATE FUNCTION dbo.Decrypt(@encrypt varbinary(8000)) RETURNS nvarchar(4000) AS BEGIN DECLARE @res nvarchar(4000) SET @res = DECRYPTBYPASSPHRASE('SQL SERVER 2008',@encrypt) RETURN(@res) END GO declare @x nvarchar(4000) declare @code varbinary(8000) SET @code = dbo.Encrypt('SomeValue') Print @code set @x = dbo.Decrypt(@code) print @x
What am I missing?