Prev
Next
Forums Infor / Lawson Platforms Lawson Landmark

SQL Query of Encrypted Data (SSN)

 3 Replies
 3 Subscribed to this topic
 32 Subscribed to this forum
Sort:
Author
Messages
Woozy
Veteran Member
Posts: 709
Veteran Member
1/17/2017 5: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
    Veteran Member
    Posts: 510
    Veteran Member
    1/17/2017 5: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
      Veteran Member
      Posts: 709
      Veteran Member
      1/25/2017 9: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
        New Member
        Posts: 2
        New Member
        8/7/2017 6: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)