Toll Free 1-877-506-2744
How can we help?

Can I hash our relationship data before making it available to the Relationship Awareness upload tool?

Print Friendly Version of this pagePrint Get a PDF version of this webpagePDF

The PrecisionLender Relationship Awareness data upload tool (pli.exe) contains built-in one-way cryptographic hashing functions that ensure that sensitive data fields (e.g., account number, TIN number, port number etc.) never leave your premises.  Such fields are configured to be hashed (using a salted MD5 hash) and optionally masked (e.g. XXXXXXX123) by the upload tool.

Some of our clients have expressed an interest in hashing these fields during their upstream data extract process, and it so happens that SQL Server has a built-in hash generation function called 'HashBytes' that can serve this exact purpose.

 

Critical Notes ---  to avoid corrupting your Relationship data:

  1. While pre-hashing these "Id Fields" is possible, it is critical that the method of hashing never change since this would result in a new hash output for a given input.  PrecisionLender relies upon these fields remaining constant with every data upload and would see a new hash value essentially as a new account number or relationship identifier.
  2. If you already have a functioning integration with PrecisionLender, there is really no need to pre-hash your data. In fact, you should not do so with without letting us know as it will appear to us as if all of our account identifiers have suddenly changed.  This could result in your relationship data being duplicated or becoming unusable within PrecisionLender.
  3. The PrecisionLender upload tool (pli.exe) will still perform its hash (a "salted" MD5 hash) of all Id fields to ensure that account number data does not make its way into PrecisionLender.  This means your Id Fields will be double-hashed in the software.  While this does not present any functional problems, you will not be able to find your hashed values within PrecisionLender.

 

Hashing fields within SQL Server:

To get an MD5 hash of a value from within SQL Server, you can use the HashBytes function combined with the MD5 algorithm argument.

 

Sample 1: 

DECLARE @HashThis varchar(4000);
SELECT @HashThis = CONVERT(varchar(4000),'1234567890');
SELECT HashBytes('md5', @HashThis);
GO

 

Result 1:

0xE807F1FCF82D132F9BB018CA6738A19F

 

 

Sample 2:

SELECT
HashBytes('md5',CONVERT(varchar(4000),'brians account number')) AS hash1,
HashBytes('md5',CONVERT(varchar(4000),'123456789')) AS hash2,
HashBytes('md5',CONVERT(varchar(4000),'1234567890')) AS hash3,
HashBytes('md5',CONVERT(varchar(4000),'abc123')) AS hash4

 

Result 2 (reformatted for clarity):

hash1
0x5A64D65DBBFEA95D44FAEA488320813F

hash2
0x25F9E794323B453885F5181F1B624D0B

hash3
0xE807F1FCF82D132F9BB018CA6738A19F

hash4
0xE99A18C428CB38D5F260853678922E03

 

Reference Materials:

 

Other Hashing tools available online:

Was this article helpful?
Have more questions?