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:
- 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.
- 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.
- 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.
DECLARE @HashThis varchar(4000);
SELECT @HashThis = CONVERT(varchar(4000),'1234567890');
SELECT HashBytes('md5', @HashThis);
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):
Other Hashing tools available online:
- Excel add-in: xCELLhash - inexpensive ($5) tool for hashing data within Excel - http://www.fullscopesolutions.com/products.html