I suppose this is obvious and not very attractive, but... One option is to do the hashing outside the database with python or Java or C# or whatever. That is: export the relevant data from your database, do the hashing, and import it again.
-- Dan ________________________________ From: Smith, Nicholas C [nicholas-c-sm...@uiowa.edu] Sent: Wednesday, May 22, 2019 10:51 AM To: Pedersen, Jay G; Dan Connolly Cc: gpc-dev@listserv.kumc.edu; Ryu, Gi-Yung Subject: RE: [External] Re: MS-SQL hashing function?, re: Death index file Hello, Jay: Did you ever get a function to work for MSSQL that can take in the parameters of ‘SHA1’, SSN+DOB/LNAME/etc, and a hash key and kick out a hashed value? I’m trying this now by taking SSNs in our data warehouse that I know exist in the SSDMF, concatenating that SSN with DOB, attempting to hash it using the hash-key but no matter what I do I cannot find that hashed value in the SSDMF file from KUMC. I am not sure I am doing this right and am looking for help from anyone doing this process via MSSSQL. Thanks! Nick [cid:image001.jpg@01D3FCE4.B5E1E620] Nicholas C. Smith, MS Senior Database Architect Institute for Clinical & Translational Science (http://www.icts.uiowa.edu<http://www.icts.uiowa.edu/>) Email: nicholas-c-sm...@uiowa.edu<mailto:nicholas-c-sm...@uiowa.edu> Phone: 319-467-8173 From: Gpc-dev <gpc-dev-boun...@listserv.kumc.edu> On Behalf Of Pedersen, Jay G Sent: Friday, March 01, 2019 9:35 AM To: Dan Connolly <dconno...@kumc.edu> Cc: gpc-dev@listserv.kumc.edu Subject: [External] Re: MS-SQL hashing function?, re: Death index file RE: <<A web search for "sqlserver hmac sha1" showed a few approaches.>> Fair enough. I will do some testing and report back to the GPC. The following looked worth a first area of investigation: << DECLARE @HashThis nvarchar(4000); SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf'); SELECT HASHBYTES('SHA1', @HashThis); >> https://www.sqlservercentral.com/Forums/Topic1376183-391-1.aspx On the good side, I have both Oracle and MS-SQL available at the moment, so I can directly compare an Oracle result to an MS-SQL result. Jay Pedersen, M.A. Department of Pathology/Microbiology University of Nebraska Medical Center 985900 Nebraska Medical Center Omaha NE 68198-5900 402-559-9487<tel:402-559-9593> (office) 402-739-3496<tel:402-350-7851> (mobile) ________________________________ From: Dan Connolly <dconno...@kumc.edu<mailto:dconno...@kumc.edu>> Sent: Friday, March 1, 2019 9:26 AM To: Pedersen, Jay G Cc: gpc-dev@listserv.kumc.edu<mailto:gpc-dev@listserv.kumc.edu> Subject: Re: MS-SQL hashing function?, re: Death index file Non-UNMC email A web search for "sqlserver hmac sha1" showed a few approaches. I'm not familiar enough with sqlserver to know which ones are likely to work well. On Mar 1, 2019 6:47 AM, "Pedersen, Jay G" <jay.peder...@unmc.edu<mailto:jay.peder...@unmc.edu>> wrote: Hi, I am wondering if anyone knows if it is possible to create a MS-SQL analog of the following Oracle function ... which provides hasing of SSN and DOB: I am hoping Dan Connolly might be able to provide direction. The fallback is to create a python script to interface to MS-SQL and create hashed versions of DOB and SSN. Oracle code: """ create function dmf.keyed_hash(data varchar2, hash_key varchar2) return varchar2 is begin return DBMS_CRYPTO.mac( UTL_I18N.string_to_raw(data, 'AL32UTF8'), DBMS_CRYPTO.hmac_sh1, UTL_I18N.string_to_raw(hash_key, 'AL32UTF8')) end """ Jay Pedersen, M.A. Department of Pathology/Microbiology University of Nebraska Medical Center 985900 Nebraska Medical Center Omaha NE 68198-5900 402-559-9487<tel:402-559-9593> (office) 402-739-3496<tel:402-350-7851> (mobile) The information in this e-mail may be privileged and confidential, intended only for the use of the addressee(s) above. Any unauthorized use or disclosure of this information is prohibited. If you have received this e-mail by mistake, please delete it and immediately contact the sender. The information in this e-mail may be privileged and confidential, intended only for the use of the addressee(s) above. Any unauthorized use or disclosure of this information is prohibited. If you have received this e-mail by mistake, please delete it and immediately contact the sender. ________________________________ Notice: This UI Health Care e-mail (including attachments) is covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521 and is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If you are not the intended recipient, any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy all copies of the original message and attachments thereto. Email sent to or from UI Health Care may be retained as required by law or regulation. Thank you. ________________________________
_______________________________________________ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev
