SQL Server Decryption By Passphrase Returns NULL

Question

In a nutshell, my goal is to transfer encrypted string of messages between one SQL Server database to another. The databases are residing in the same server, however I have this very exact design requirement.

I use few tables in both the database to send and receive (encrypted)messages.

The strange issue i am stumbled upon is that, I am not able to decipher(Decrypt) the message back at the other database. I get a NULL while doing so. It is very strange and i don't know what is going wrong as my knowledge is limited on this topic though i managed to lead this far.

At first I was using the sql server certificate based encryption and decryption. The DBA had to create the symmetric key and certificate in the two different databases individually. It made me think this could be the cause the decryption is not successful at the other end.

But, then i wanted to try something simple so i played with using the SQL Server's passphrase functions. The result is still the same, the decryption returned NULL.

Here is the code

First, I created the functions in both the databases. Please note that the passphrase is the same.

Note: These functions are created in both the databases Database1 and Database2


CREATE FUNCTION [dbo].[Fn_EncryptByPassphrase] (@PlaintextMessage NVARCHAR(Max))
RETURNS VARBINARY(Max)
AS
BEGIN
    DECLARE @Passphrase NVARCHAR(Max) = 'testpassphrase'
        ,@EncryptedMessage VARBINARY(Max)

    SET @EncryptedMessage = EncryptByPassphrase(@Passphrase, @PlaintextMessage)

    RETURN @EncryptedMessage
END

CREATE FUNCTION [dbo].[Fn_DecryptByPassphrase] (@EncryptedMessage VARBINARY(Max))
RETURNS NVARCHAR(Max)
AS
BEGIN
    DECLARE @Passphrase NVARCHAR(Max) = 'testpassphrase'
        ,@PlaintextMessage NVARCHAR(Max)

    SET @PlaintextMessage = CONVERT(NVARCHAR(MAX), DecryptByPassphrase(@Passphrase, @EncryptedMessage))

    RETURN @PlaintextMessage
END

Here are the tables:

Database1

Create Table [dbo].[Outbound]
(Id INT, 
Message NVARCHAR(Max), 
EncryptedMessage VARBINARY(Max))


Database2

Create Table [dbo].[Inbound]
(Id INT, 
InboundMessageId INT, 
EncryptedMessage VARBINARY(Max))

This is the usecase:

USE Database2

INSERT INTO [dbo].[Inbound] (
    InboundMessageId
    ,[EncryptedMessage]
    )
SELECT Id
    ,EncryptedMessage
FROM [Database1].[dbo].[Outbound]
WHERE Id = 208

DECLARE @Message NVARCHAR(Max) = ''
    ,@Encrypted VARBINARY(Max)

SELECT @Encrypted = EncryptedMessage
FROM dbo.Inbound
WHERE InboundMessageId = 208

this returns NULL

SELECT [dbo].[Fn_DecryptByPassphrase](@Encrypted)

this returns NULL either. Despite that i used the same passphrase i used for encryption. The only difference is, I used the passphrase to encrypt the message from Database1 and then I use the same passphrase to decrypt at Database2 which yields a NULL.

DECLARE @Passphrase NVARCHAR(Max) = 'testpassphrase'

SELECT Convert(NVARCHAR(Max), DecryptByPassphrase(@Passphrase, @Encrypted))

This appears strange! I am not sure if this is normal behavior and if so, is there a workaround?.

Update:

Does anybody think the usecase I mentioned does not work between two databases.? because i am able to decrypt the contents if the decryption is done from within the same database. strange!!

Update 2

The DBA has got the certificate based encryption - decryption working. However, the passphrase based code mentioned in this post is still not working. Though i got one issue off my plate, I would still be looking for a solution for the passphrase based decryption.. kindly post your solution.