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.
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.