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.