BAQ for PrEmpMas Social Security Number (Encrypted)

kylepbsps · May 9, 2022, 9:02pm

In doing some testing with 2021.100.26 it looks like the social security numbers are now encrypted through BAQ. So no more accessing from PrEmpMas through baq or baq report.
The suggested fix is to create a UD column.
Just a heads up for anyone interested.

I will need this for some reports, adding UD column seems like a way to revert the security purpose of this. Any recommendations?

Resolution

There is an enhancement request ERPS-152082 Payroll/External Payroll Employee Social Security Number encrypted in database to be exposed as decrypted in BAQ, and re-encrypted back when updated by Updatable BAQ

Notes

As a workaround, if users want SSNs in plain text, add a UD Field to table PREmpMas, get the unencrypted list of SSNs using the BO, using REST(GetRows for example)and just save the unencrypted value into the UD Field. This should easily pull UD fields into a BAQ.

If you require additional assistance setting this up you will need to work with Professional Services.

PrEmpMas

josecgomez · May 10, 2022, 1:00am

Epicor has the facility to decrypt them to print in w2 etc

Is just a matter of calling the right function

Going from memory here but try this on a UBAQ GetList post processing for each of the rows on the BAQ where str is the field holding the encrypted data

Epicor.Security.Cryptography.Encryptor.DecryptToString(str);

kylepbsps · May 12, 2022, 7:51pm

Can you give me a few more hints on the UBAQ ?
My end result is an Baq Report into SSRS. Do I need to setup BPM to trigger when the BAQ runs?

josecgomez · May 12, 2022, 8:07pm

an Updatable BAQ , you can run that bit of code in the Post Processing of GetList that basically “unmasks” the data.

kylepbsps · May 12, 2022, 8:11pm

Thank you, ill dig into that.

kylepbsps · May 13, 2022, 7:06pm

When I post the Encrypted SSN to a message box I get a small portion of it. Same as the BAQ column. 24 characters I believe.
It’s trying to decrypt only part of it so I get Nothing for a result after that.
(Actually might be the entire thing i dunno)

Here is my process (which is a little goofy since im trying to figure this out)

BPM
GetList->Post Processing

PrEmpMas.SocSecNum to Calculated Field (Calculated_SSNph)
Set Argument/Variable of MaskedSSN = resultResultsRow.Calculated_SSNph
Execute Custom Code:
string UnMaskedSSN = Epicor.Security.Cryptography.Encryptor.DecryptToString(MaskedSSN);
Show both MaskedSSN and UnMaskedSSN in message box.

I’m going to build a Dashboard with a customization and see if i can get it to work there maybe.

kylepbsps · May 13, 2022, 7:43pm

It works in Form Customization, same code. Must be missing a reference, or something with how im doing the bpm.

kylepbsps · May 13, 2022, 9:31pm

So if i do a message box through the code block it works yay. Now I confirmed I can decrypt it. Next step is to add it back to the baq column or replace a column in the baq result.

kylepbsps · May 16, 2022, 8:55pm

Ughh… im going to give up and pay Epicor to rewrite our 401k Submission report (Which requires SSN). I wrote the original BAQ Report in E10, which was very simple, just query PrEmpMas table with sub-query on deductions.

I can get the decrypted value to show up in a message box when the baq returns (1) result / employee. Otherwise it’s trying to put it into a list. I imagine its some sort of ‘foreach’ code that would solve this but i would also need to have it dump back into the baq as a new column so I can use it on my SSRS report.

josecgomez · May 16, 2022, 9:15pm

Loop through the results in the dataset, and assign the decrypted value to a Calculated field in the BAQ.

ewidjaja · January 2, 2023, 6:44pm

I got the following error when I try to do it on Form Customization…

‘Epicor.Security.Cryptography.Encryptor.DecryptToString(string)’ is obsolete: ‘Use an overload that takes a key parameter or use ServerEncryptor on the server or ClientEncryptor on the client instead. Note that they use a different key.’

kylepbsps · July 25, 2023, 6:22pm

I got it all working after learning some things.
It does use the UpdateExt for the UBAQ, but i dont believe I had any other choice. Should just update the results table on the BAQ. Inside the BPM its using GetList.

foreach (var ttDecrypt in (from row in result.Results select row))
{
    // Decrypt "PREmpMas_SocSecNum" field for each row
    string encryptedData = ttDecrypt.PREmpMas_SocSecNum;
    string decryptedData = Epicor.Security.Cryptography.Encryptor.DecryptToString(encryptedData);
    
    // Option #1 Update the decrypted value back to the same row
    ttDecrypt.PREmpMas_SocSecNum = decryptedData;
    
    // Option #2 Update the calculated field
    // ttDecrypt.Calculated_ReplacedSSN = decryptedData;
}

kylepbsps · October 10, 2023, 6:06pm

This no longer works in 2022.2.27 (11.2.200.27)

foreach (var ttDecrypt in (from row in result.Results select row))
{
// Decrypt “PREmpMas_SocSecNum” field for each row
string encryptedData = ttDecrypt.PREmpMas_SocSecNum;
string decryptedData = Epicor.Security.Cryptography.Encryptor.DecryptToString(encryptedData);
// Option #1 Update the decrypted value back to the same row
ttDecrypt.PREmpMas_SocSecNum = decryptedData;
// Option #2 Update the calculated field
//ttDecrypt.Calculated_ReplacedSSN = decryptedData;
}

klincecum · October 11, 2023, 2:34pm

Post the text of that error message instead of a screen shot.

kylepbsps · October 11, 2023, 2:48pm

Server Side Exception

BPM runtime caught an unexpected exception of ‘CryptographicException’ type.
See more info in the Inner Exception section of Exception Details.

Exception caught in: Epicor.ServiceModel

Error Detail

Correlation ID: 909e6f8f-00d2-459d-87a7-71ed103828c6
Description: BPM runtime caught an unexpected exception of ‘CryptographicException’ type.
See more info in the Inner Exception section of Exception Details.
Program: System.Security.Cryptography.Algorithms.dll
Method: GetPaddingLength
Original Exception Type: CryptographicException
Framework Method: Throw
Framework Line Number: 0
Framework Column Number: 0
Framework Source: Throw at offset 22281712 in file:line:column :0:0

Server Trace Stack: at Internal.Cryptography.UniversalCryptoDecryptor.GetPaddingLength(ReadOnlySpan1 block) at Internal.Cryptography.UniversalCryptoDecryptor.UncheckedTransformFinalBlock(ReadOnlySpan1 inputBuffer, Span1 outputBuffer) at Internal.Cryptography.UniversalCryptoDecryptor.UncheckedTransformFinalBlock(Byte[] inputBuffer, Int32 inputOffset, Int32 inputCount) at Internal.Cryptography.UniversalCryptoTransform.TransformFinalBlock(Byte[] inputBuffer, Int32 inputOffset, Int32 inputCount) at System.Security.Cryptography.CryptoStream.ReadAsyncCore(Memory1 buffer, CancellationToken cancellationToken, Boolean useAsync)
at System.Security.Cryptography.CryptoStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.StreamReader.ReadBuffer()
at System.IO.StreamReader.ReadToEnd()
at Epicor.Security.Cryptography.Encryptor.Decrypt(Byte[] cipherBytes, Byte[] key, Byte[] iv) in C:_releases\ICE\ICE4.2.200.27\Source\Shared\Framework\Epicor.ServiceModel\Security\Cryptography\Encryptor.cs:line 106
at Epicor.Customization.Bpm.Ubaq.GetListPostProcessingDirective_401k_Replace_SSN_2FD095DEB03445E8839F250FB5AB04CA.A001_CustomCodeAction()
at Epicor.Customization.Bpm.Ubaq.GetListPostProcessingDirective_401k_Replace_SSN_2FD095DEB03445E8839F250FB5AB04CA.ExecuteCore(Int32 step)
at Epicor.Customization.Bpm.DirectiveBase2.Execute() in C:\_releases\ICE\ICE4.2.200.27\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 330 at Epicor.Customization.Bpm.DirectiveBase2.Execute(TParam parameters) in C:_releases\ICE\ICE4.2.200.27\Source\Server\Internal\Lib\Epicor.Customization.Bpm\DirectiveBase.Generic.cs:line 222

Client Stack Trace

at Ice.Cloud.ProxyBase1.CallWithCommunicationFailureRetry(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, RestRpcValueSerializer serializer) at Ice.Cloud.ProxyBase1.CallWithMultistepBpmHandling(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy)
at Ice.Cloud.ProxyBase1.Call(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy) at Ice.Proxy.BO.DynamicQueryImpl.GetList(DynamicQueryDataSet queryDS, QueryExecutionDataSet executionParams, Int32 pageSize, Int32 absolutePage, Boolean& hasMorePage) at Ice.Adapters.DynamicQueryAdapter.<>c__DisplayClass45_0.<GetList>b__0(DataSet datasetToSend) at Ice.Adapters.DynamicQueryAdapter.ProcessUbaqMethod(String methodName, DataSet updatedDS, Func2 methodExecutor, Boolean refreshQueryResultsDataset)
at Ice.Adapters.DynamicQueryAdapter.GetList(DynamicQueryDataSet queryDS, QueryExecutionDataSet execParams, Int32 pageSize, Int32 absolutePage, Boolean& hasMorePage)
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.TestCallListBckg()
at Ice.UI.App.BAQDesignerEntry.BAQTransaction.<>c__DisplayClass220_0.b__0()
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()

Inner Exception

Padding is invalid and cannot be removed.

klincecum · October 11, 2023, 2:53pm

Does that method have an overload to set the padding?

kylepbsps · October 11, 2023, 3:02pm

I have no idea.

josecgomez · January 22, 2024, 1:05am

The fix for 2022+ is

Epicor.Security.Cryptography.ServerEncryptor.DecryptToString(encryptedData);

utaylor · January 22, 2024, 2:45am

Thanks for the update!

kylepbsps · January 22, 2024, 3:50am

Awesome! Appreciate the update.