I'm trying to use sp_OAMethod to connect to a site that requires encryption. It needs to use SHA256 and hash a string that was derived from concatenating a few values. It also needs to apply a secret key (I think this might be what is meant by a salt value) over the hashed result. After that, it needs to convert the result to hexadecimal. I've looked online quite a bit and tried a few functions I've found but nothing seems to work. Does anyone have any experience with this that may be able to help.
Any help is apprecicated.
The HASHBYTES() function supports SHA256:
You can salt a SHA256 hash using HASHBYTES by concatenating the salt with the value being hashed. The hashed value is already in a binary data type, you can use the CONVERT function to turn it into a hexadecimal string format:
Regarding creating an HMAC, while you can do it using sp_OACreate, if you can find another way of doing it (like calling PowerShell, or a command-line utility, or even using a CLR function that is then registered in SQL Server), that would be preferable. sp_OA procedures calling COM objects has been deprecated in SQL Server for quite a while, and if used incorrectly, can cause issues with memory management in SQL Server.