PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 08/07/2017 2:29 PM by  ddub
SQL Query of Encrypted Data (SSN)
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Woozy
Private
Private
Veteran Member
(3469 points)
Veteran Member
Posts:701


Send Message:

--
01/17/2017 12:30 PM

    Hi All,

    We use DB2 SQL OLEDB queries to pull data from Landmark/TM into our data warehouse.  We need to be able to do this with EmployeeIdentificationNumber to get the IdentificationNumber (SSN), but this field happens to be encrypted.  Does anyone know the trick to decrypt this data via SQL?

    NOTE:  We're using the IBM DB2 drivers.

    Thanks!  Kelly

    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    JimY
    Private
    Private
    Veteran Member
    (1089 points)
    Veteran Member
    Posts:389


    Send Message:

    --
    01/17/2017 12:55 PM
    We weren't able to do it using Sql so we used a Landmark Transaction node to extract it. It un-encrypts it.
    Woozy
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    01/25/2017 4:41 PM
    For those who are interested, I contacted Infor Support about this, and the answer is "you can't do it with SQL". The encryption is handled by the application, so the database doesn't have the ability to see the manage the decryption.

    JimY's solution certainly works - but it isn't helpful for us because so much of our extract work which is done using MSSQL SSIS packages. It is also possible to use dataexport from the command line to dump the data in CSV or XML and then manipulate it, but it's very slow.

    I ended up building a hybrid IPA/SQL solution where I have a flow that uses DB2 SQL to query the EmployeeIdentificationNumber table to identify recently updated records, the uses a Landmark query to pull the decrypted field values for those records, and finally calls a MS SQL stored procedure to update the records into a table on our data warehouse. This solution allows us to run the flow as often as we want without having it take all day. Hopefully this will work for us.
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    ddub
    SOFTWARE INTEGRATOR
    Private
    New Member
    (4 points)
    New Member
    Posts:2


    Send Message:

    --
    08/07/2017 2:29 PM

    We just went thru a long arduous battle with inforXtreme that culminated in a phone conversation with several members of infor development and support. Needless to say, we were told that the encryption keys would not be made available to us due to security concerns and that the only way to extract these encrypted Landmark values would be thru infor/Lawson applications (i.e. IPD, infor Excel Add-ins, etc.). That said we will probably do something similar to what Woozy detailed but wanted to provide anyone reviewing this with the most beneficial piece of info that came out of our meeting. It was a listing of secured Landmark fields. 

    For anyone interested, those fields are as follows: 


    Table (Module)
    ParticipantDepIdentificationNumber (Benefits)
    ParticipantIdentificationNumber (Benefits

    DependentIdentificationNumber (Global HR)
    DependentTravelDocument (Global HR)
    EmployeeAlternateIdentificationNumber (Global HR)
    EmployeeBankDetails (Global HR)
    EmployeeIdentificationNumber (Global HR)
    EmployeeIDSearch (Global HR)
    EmployeeTravelDocument (Global HR

    HRMDependentIdNbr (Lawson Talent Management to Lawson HRM interface)
    HRMDependentIdNbrHistory (Lawson Talent Management to Lawson HRM interface)
    HRMEmployeeIdNbr (Lawson Talent Management to Lawson HRM interface)
    HRMEmployeeIdNbrHistory (Lawson Talent Management to Lawson HRM interface)
    CandidateIdNumberExport (Lawson Talent Management Universal Interface)
    DependentIdNumberExport (Lawson Talent Management Universal Interface)
    EmployeeAltrIdNbrExport (Lawson Talent Management Universal Interface)
    EmployeeBankDetailsExport (Lawson Talent Management Universal Interface)
    EmployeeBankDetailsImport (Lawson Talent Management Universal Interface)
    EmployeeIdNumberExport (Lawson Talent Management Universal Interface)
    InterfaceCandidate (Lawson Talent Management Universal Interface)
    InterfaceEmpAltrIdNbr (Lawson Talent Management Universal Interface)
    InterfaceEmpAltrIdNbrHistory (Lawson Talent Management Universal Interface)
    InterfaceEmployeeIdNbr (Lawson Talent Management Universal Interface)
    InterfaceEmployeeIdNbrHistory (Lawson Talent Management Universal Interface)
    JobRequisitionExport (Lawson Talent Management Universal Interface

    CandidateDependentIdNumber (Talent Acquisition)
    CandidateDependentTravelDoc (Talent Acquisition)
    CandidateIdentificationNumber (Talent Acquisition)
    CandidateTravelDocument (Talent Acquisition)



    You are not authorized to post a reply.