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

Hope you're having a good day. This is in continuation to [my previously posted question in Salesforce StackExchange](https://salesforce.stackexchange.com/questions/388321/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via) . 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](https://developer.salesforce.com/docs/atlas.en-us.apexref.meta/apexref/apex_classes_restful_crypto.htm#apex_System_Crypto_encrypt) . 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', 'MyName@gmail.com', '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: MyName@gmail.com
--| 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](https://salesforce.stackexchange.com/users/4142/identigral) in my [previous question](https://salesforce.stackexchange.com/questions/388321/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via) , 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.