ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE is not returning correct values

Question

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?

Answer 1

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().