May 5, 2015 at 8:38 am
Hi there,
I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:
OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION
BY CERTIFICATE [XXXX_CERT];
select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> ))
FROM Database2.dbo.TABLE1
CLOSE SYMMETRIC KEY [XXXXKey];
What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?
Thank you for your help.
-karen
May 5, 2015 at 11:58 am
Since the Key and Certificate reside in the other database you need to change context to that database. I believe you can do it using Exec sp_executesql something like this:
USE tempdb;
GO
CREATE SYMMETRIC KEY test WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = 'This1sTheP@ssW0rd';
GO
CREATE TABLE [dbo].[BoxGroups](
[SysKey] [int] IDENTITY(1,1) NOT NULL,
[BoxMainGroups] [varchar](50) NULL,
[EcryptedBoxMainGroups] [varbinary](100) NULL
)
OPEN SYMMETRIC KEY test DECRYPTION BY PASSWORD = 'This1sTheP@ssW0rd';
INSERT INTO dbo.BoxGroups
(
BoxMainGroups,
EcryptedBoxMainGroups
)
VALUES
(
'Test', -- BoxMainGroups - varchar(50)
ENCRYPTBYKEY(KEY_GUID('test'), 'test') -- EcryptedBoxMainGroups - varbinary(100)
)
SELECT
BG.SysKey,
BG.BoxMainGroups,
CONVERT(VARCHAR(50), DECRYPTBYKEY(BG.EcryptedBoxMainGroups))
FROM
dbo.BoxGroups AS BG;
CLOSE SYMMETRIC KEY test;
USE master;
GO
EXEC sys.sp_executesql N' USE tempdb
OPEN SYMMETRIC KEY test DECRYPTION
BY PASSWORD = ''This1sTheP@ssW0rd'';
SELECT
BG.SysKey,
BG.BoxMainGroups,
CONVERT(VARCHAR(50), DECRYPTBYKEY(BG.EcryptedBoxMainGroups))
FROM
dbo.BoxGroups AS BG;
CLOSE SYMMETRIC KEY test;'
You'll need to manage security so that the user that is executing the stored procedure in database1 has access to the objects (keys, certs, tables) in database2. You can do this with a certificate as well and sign the procedure with a certificate for the most secure way to do it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 5, 2015 at 2:04 pm
Thank you! Solved my problem.
May 5, 2015 at 9:27 pm
Quick question, why not create the symmetric key in the destination database?
😎
May 6, 2015 at 6:00 am
Eirikur Eiriksson (5/5/2015)
Quick question, why not create the symmetric key in the destination database?😎
I haven't done much with encryption so I wasn't sure if this was possible and it was simpler to test using sp_executesql. If you can create the key in the destination database and decrypt the data using it that would be a better solution for sure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2015 at 6:45 am
That is what I originally wanted but the source database is the backend for third party software, so their key.
I asked them for the information (create symmetric key script) to recreate the key in our database so I could decrypt there and was told "I needed to decrypt the data on the way out of their database".
Is this just something that is normally difficult to get ahold of?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply