1

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.

7
  • 1
    Crypto.decryptWithManagedIV wants IV + encrypted data only. SQL Server adds a bunch of junk to the encrypted string. You'd have to convert what you get from SQL Server to hex, manipulate the hex representation by cutting out all the junk, then pad appropriately to arrive at IV + encrypted data. See salesforce.stackexchange.com/questions/271648/… for a similar issue.
    – identigral
    Oct 22, 2022 at 16:22
  • Hey @identigral, thanks for your response. I followed your suggestion. I would like to know; how do you suggest getting the key? Using the Password as the key doesn't return similar results. Oct 23, 2022 at 9:37
  • You'll have to extract the key from SQL Server. For a very basic key definition/storage scheme with a certificate-protected key, this extraction is possible. For password-protected keys, not sure it's possible. Either way, this is a question for StackOverflow or DBA SE
    – identigral
    Oct 23, 2022 at 18:14
  • Thank you for your help @identigral. I'll try seeking help there as well. Oct 24, 2022 at 5:42
  • @identigral, Could you suggest how to pad the data after removing the junk as you mentioned? that'd be helpful. EDIT: Posted the topic here: dba.stackexchange.com/questions/318619/… Oct 24, 2022 at 8:15

1 Answer 1

2

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.

1
  • The private key stored in SQL Server is symmetric. Its encryption as Sean pointed out has lots of different implementations. For an asymmetric encryption of this key with your own private/public keypair (aka Certificate), you can extract the symmetric key from SQL Server. You literally own the keys. Is it trivial? Far from it, but not impossible. Glad you found the solution to your issue and posted it, have an upvote.
    – identigral
    Oct 25, 2022 at 23:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .