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

Question

Hope you're having a good day. This is in continuation to my previously posted question in Salesforce StackExchange. I was suggested to seek help at DBA SE in order to find a way to extract Symmetric key from MS SQL in order to use it for decrypting the data in Salesforce Apex using its Crypto class. Or otherwise, an alternative.

---

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

--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. Following suggestion from identigral in my previous question, I manipulate the output data by cutting out all the junk to arrive at IV + encrypted data.

GUID: 0072002a5e1754449df32d0394eeef29

VER: 02000000

IV: fe437197cd781681bdd3fa5e48970b78
IV_b64: /kNxl814FoG90/peSJcLeA==

Header: d7faf146c16b4bbf

Data: db996506e454cb8254970d47925fea0ae9064d84b02e34f0
Data_b64: 25llBuRUy4JUlw1Hkl/qCukGTYSwLjTw

However, still missing out the Symmetric key to be used for decryption inside Salesforce Apex.

        Blob key = Blob.valueOf('???????????????????');  //no such way to export key from T-SQL
        Blob encrypted = EncodingUtil.base64Decode('25llBuRUy4JUlw1Hkl/qCukGTYSwLjTw');

        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 symmetric key with AES_256 with a password.
--
CREATE SYMMETRIC KEY EncryptKey1   
WITH ALGORITHM = AES_256  
ENCRYPTION BY PASSWORD = '12345qwertyu@!#$%asdEWQAS#$r4cfr';  -- 32 bytes, to be used as the key.

After encoding and manipulating the same password, this is the result:

PASSWORDED

GUID: 005d15df67da1d4199bb777f779acb26

VER: 02000000

IV: b6175270ad8b7a259531206018591c4f
IV_b64: thdScK2LeiWVMSBgGFkcTw==

Header: a0a1bb88411dea02


Data: 19b397e8335e6f5934b31814ce62645395c2e76408667160
Data_b64: GbOX6DNeb1k0sxgUzmJkU5XC52QIZnFg

Using the password as KEY in Apex Class. Returns above-mentioned error.

        Blob key = Blob.valueOf('12345qwertyu@!#$%asdEWQAS#$r4cfr');  //using password as the key.
        Blob iv = EncodingUtil.base64Decode('thdScK2LeiWVMSBgGFkcTw==');
        Blob encrypted = EncodingUtil.base64Decode('GbOX6DNeb1k0sxgUzmJkU5XC52QIZnFg');

        Blob decrypted = Crypto.decrypt('AES256', key, iv, 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

Therefore, need a solution in some way to either export the key string from MS SQL.

SQL Server attempts to keep secrets safe. None of the values are kept in plain text, it's encrypted by some form of encryptor of which there are many avenues. The simplest of all forms are going to be strictly password based, as the others have extra layers of protection but as you've noticed, you'll need to have the key.

SQL Server does not give you a function or any way to access said key. You'll need to reverse engineer this on your own, though there are some base tables with information that could be helpful, however you'll continue to find that the structures and data are undocumented (for a reason). This is a non-trivial task.

There are other options which may work for you, though not with a symmetric key, such as using your own certificate (which is really just an asymmetric key) where you can know the private key in advance. Also, much of this is already completed for you if you want to use Always Encrypted.

Answer 2

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.