August 30, 2017 at 6:18 pm
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?.
August 31, 2017 at 7:39 am
Just a thought, but have you tried to directly decrypt in the SELECT of the message data, as opposed to putting the data in a variable first?
Also, are you sure you have something other than a NULL value being inserted?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 31, 2017 at 9:02 am
you getting null value from the table
SELECT @Encrypted = EncryptedMessage
FROM dbo.Inbound
WHERE InboundMessageId = 208
this returns NULL
so basically your input to function ( Fn_DecryptByPassphrase) is NULL, it will return null.
Did you verified the data in your table in DB1 and data in DB2?
August 31, 2017 at 9:05 am
Just to verify. I ran the script to encrypt in one server and then ran the decryption script to different server, it works fine
DECLARE @Passphrase NVARCHAR(Max) = 'testpassphrase'
,@EncryptedMessage VARBINARY(Max)
,@PlaintextMessage NVARCHAR(Max) ='Hello'
SET @EncryptedMessage = EncryptByPassphrase(@Passphrase, @PlaintextMessage)
print @EncryptedMessage
go
DECLARE @Passphrase NVARCHAR(Max) = 'testpassphrase'
,@PlaintextMessage NVARCHAR(Max)
,@EncryptedMessage VARBINARY(Max)=0x01000000AD6F5DF23B74788DC4F449334EBD8CD0B0FF73BD876065AF02981FF25CB09943
SET @PlaintextMessage = CONVERT(NVARCHAR(MAX), DecryptByPassphrase(@Passphrase, @EncryptedMessage))
print @PlaintextMessage
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply