Decrypt Symmetrically/Asymmetrically Encrypted Data in MS SQL (T-SQL) Server via Apex Crypto Class

Question

Hope you're doing well. Recently, I have been working on a project and haven't been able to find a solution to this problem.

What I want to achieve is: To decrypt the data in Apex using the crypto class. The data is encrypted using the AES_256 algorithm in MS SQL Server. Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-transact-sql?view=sql-server-ver16

MS SQL server has its own functionality to encrypt/decrypt data using symmetric/asymmetric keys. The problem is, unlike Apex or any other language, we cannot view the generated AES key. The server stores the key such that it is self-encrypted and can only be accessed within the database by its name.

This is how the data is encrypted in MS SQL: Can be tested at MS SQL Online IDE: https://sqliteonline.com/

--Creating a table in db to store data. It has a column to store encrypted password as well.
--
CREATE TABLE encryption_test ( name VARCHAR(20), email VARCHAR(40), password VARCHAR(45), password_encrypted VARBINARY(MAX))


-- Create a DB Master key as pre requisit for symmetric key creation.
--
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncPass@123#';


-- Create a self-signed cert
--
CREATE CERTIFICATE EncryptCert1 WITH SUBJECT = 'EncryptCert1';


-- Create a symmteric key with AES_256 with self-signed cert
--
CREATE SYMMETRIC KEY EncryptKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE EncryptCert1;


-- Create a UDF to easily encrypt/decrypt any data

--Encryption function
--
CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max)  
    SET @Result = EncryptByKey(Key_GUID('EncryptKey1'), @ValueToEncrypt)  
    -- Return the result of the function  
    RETURN @Result  
END


-- Decryption function
--
CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max)  
    SET @Result = DecryptByKey(@ValueToDecrypt)  
    -- Return the result of the function  
    RETURN @Result  
END


-- Create a server stored procedure to easily access encryption/decryption key.
--
CREATE PROCEDURE sp_OpenEncryptionKeys  
AS  
BEGIN  
    SET NOCOUNT ON;  

    BEGIN TRY  
        OPEN SYMMETRIC KEY EncryptKey1  
        DECRYPTION BY CERTIFICATE EncryptCert1 
    END TRY  
    BEGIN CATCH  
        --catch
    END CATCH  
END

-- Insert some data in table
--
EXEC sp_OpenEncryptionKeys  --run procedure to access key.
INSERT INTO encryption_test VALUES ( 'MyName', '[email protected]', 'MyPass123', dbo.Encrypt('MyPass123') );  --dbo.Encrypt encryps the data in BLOB

-- Finally, access the encrypted data.
--
EXEC sp_OpenEncryptionKeys
SELECT name, email, password, password_encrypted, pass_base64
FROM encryption_test
cross apply (select password_encrypted '*' for xml path('')) T (pass_base64);

--OUTPUT DATA
----------------------------------------------------------------
--| name: MyName |
--| email: [email protected]
--| password: MyPass123
--| password_encrypted: -0,114,0,42,94,23,84,68,157,243,45,3,148,238,239,41,2,0,0,0,254,67,113,151,205,120,22,129,189,211,250,94,72,151,11,120,215,250,241,70,193,107,75,191,219,153,101,6,228,84,203,130,84,151,13,71,146,95,234,10,233,6,77,132,176,46,52,240
--| pass_base64: AHIAKl4XVESd8y0DlO7vKQIAAAD+Q3GXzXgWgb3T+l5Ilwt41/rxRsFrS7/bmWUG5FTLglSXDUeSX+oK6QZNhLAuNPA=
----------------------------------------------------------------

Now the data is encrypted in MS SQL server and exported in bas64 format in a CSV. But the data cannot be decrypted in Apex via its crypto class because it requires a key.

        Blob key = Blob.valueOf('???????????????????');  //no such way to export key from T-SQL
        Blob encrypted = EncodingUtil.base64Decode('AHIAKl4XVESd8y0DlO7vKQIAAAD+Q3GXzXgWgb3T+l5Ilwt41/rxRsFrS7/bmWUG5FTLglSXDUeSX+oK6QZNhLAuNPA=');

        Blob decrypted = Crypto.decryptWithManagedIV('AES256', key, encrypted);
        
        String decryptedString = decrypted.toString(); 
        System.debug('decrypted String: '+decryptedString);

I gave it a try by creating a symmetric key encrypted by password to use the password as the key. But, it then returns error: last block incomplete in decryption.

-- Create a symmteric key with AES_256 with password.
--
CREATE SYMMETRIC KEY EncryptKey1   
WITH ALGORITHM = AES_256  
ENCRYPTION BY PASSWORD = '12345qwertyu@!#$%asdEWQAS#$r4cfr';  -- 32 bytes, to be used as the key.

Using the password as KEY in Apex Class.

        Blob key = Blob.valueOf('12345qwertyu@!#$%asdEWQAS#$r4cfr');  //using password as the key.
        Blob encrypted = EncodingUtil.base64Decode('AHIAKl4XVESd8y0DlO7vKQIAAAD+Q3GXzXgWgb3T+l5Ilwt41/rxRsFrS7/bmWUG5FTLglSXDUeSX+oK6QZNhLAuNPA=');

        Blob decrypted = Crypto.decryptWithManagedIV('AES256', key, encrypted);
        
        String decryptedString = decrypted.toString(); 
        System.debug('decrypted String: '+decryptedString);

Therefore, need a solution in some way to either export the key string from MS SQL. Or otherwise an alternate to decrypt the T-SQL encrypted data in Apex Class.

Answer 1

As per the suggestion from @identigral, I posted a topic in DBA SE to get some help about extracting KEY from MS SQL. Got some help. But, no solution. However, I found an alternate solution. Thus, posted as answer to that topic.

---

Original Answer: https://dba.stackexchange.com/questions/318619/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via/318725#318725

---

Hey,

I pay my thanks to everyone who tried to help. Specially @identigral and @Sean Gallardy. Have been looking for a solution or an alternative to this problem for days.

I have come to the conclusion that there is no any available method by which the symmetric/asymmetric key from MS SQL (T-SQL) can be extracted. However, I found an alternative to tackle this problem.

I used SSIS + COZYROC extensions for ETL in VS19. Extracted the data from MS SQL DB. Using Script Component, encrypted the data in C# which generates a random key and IV (or even can define custom key/iv). Loaded the data into Salesforce with the help of COZYROC Salesforce Destination Component. I used the randomly generated key and IV to decrypt the data in Salesforce Apex using its Crypto Class.

This achieves my purpose of encrypting data in MS SQL and decrypting the data into Salesforce Apex. However, through a way around. I hope it will be useful for someone else looking for something similar.