Calling duplicated user defined functions across mssql server databases (archive scenario)

Question

This may be a fairly straight forward process and I'm simply missing something.

I have a duplicated database in an archive scenario. some of the fields in certain tables are encrypted using wrapper functions around SQL Server's Encypt/DecryptByKey.

During the archive process we need to decrypt certain data from the database where data is being offloaded, because it was encrypted using that databases certs/keys, and re-encrypt it w/ the archive db's infrastructure.

not being a huge sql guru myself (and not seeing much online about this that pops up at least on whatever i'm searching on which may be part of the problem), I'm not seeing how I can decrypt, then re-encrypt the data in my insert/select statements.

the decrypt needs to happen using the exporting databases user defined function (at least this is how the logic seems to work in my brain), then re-encrypted using the UDF on the archiving database, though I don't see a direct way to invoke the function by server->schema->user defined function maybe this isn't how its invoked and is really why i'm posting this here... so if anybody has input on how I could do this (if its even possible)

side note, one limitation of this encryption/decryption method is that the keys can't be opened from the function itself so we have to call this OpenKeys sproc to make the keys available to the function. Those keys have a limited session life and each server will need to access their own.

here's some sample code if that helps clarify things:

sort of what we're trying to do in an archive

EXEC OpenKeys
EXEC [ArchiveDB].OpenKeys           
INSERT INTO [ArchiveDB].dbo.AuditTrail
    SELECT
        Action, 
        Location, 
        TableName, 
        TableID, 
        -- ***
        -- obv. incorrect but need to call archive db's encrypt using
        -- archive keys etc., and active db's decrypt, with active db keys
        -- ***
        [ArchiveDB].dbo.EncryptColumn(dbo.DecryptColumn(UserName)), 
        EventDate 
    FROM 
        AuditTrail 
    WHERE 
        AuditTrail < @ArchiveDate

kind of what we're doing with the key session sproc

CREATE PROCEDURE [dbo].[OpenKeys]
AS
BEGIN
    BEGIN TRY
        OPEN SYMMETRIC KEY MySymmetricKey
        DECRYPTION BY CERTIFICATE MyCertificate
    END TRY
    BEGIN CATCH
        -- ...
    END CATCH
END

sample of what the encrypt columns function does

CREATE FUNCTION [dbo].[EncryptColumn] -- must call OpenKeys before this (Decrypt similar)
(
    @ValueToEncrypt varchar(max)
)
    RETURNS varbinary(8000)
    AS
    BEGIN
        -- Declare the return variable here
        DECLARE @Result varbinary(8000)
        SET @Result = EncryptByKey(Key_GUID('MySymmetricKey'), @ValueToEncrypt)
        -- Return the result of the function
        RETURN @Result
    END

hopefully this makes sense

userFWV · Answer

as it turns out, i had it partially right from the beginning. it was just missing explicit references to both databases

EXEC OrigDB.dbo.OpenKeys
EXEC ArchiveDB.dbo.OpenKeys

INSERT INTO ArchiveDB.dbo.ArchiveTable
( Col1, Col2, Col3, Col4 )
    SELECT
        Col1,
        Col1,
        Col1,
        ArchiveDB.dbo.EncryptColumn(OrigDB.dbo.DecryptColumn(Col4)) 
    FROM
        ArchiveTable
--    WHERE
--        SOME CONDITION EXISTS

Calling duplicated user defined functions across mssql server databases (archive scenario)

1 Answers