7 Replies Latest reply on Jul 7, 2021 3:21 PM by inuser545126

    Connection to MS SQL fails on Task, succeeds when testing connection

    inuser545126 New Member

      Hello! I am setting up a set of tasks that pull data from views in Microsot SQL Server (loading into Salesforce).

       

      I have the connection set up using the SQL Server connection type (using Windows Authentication v2), and the connection test runs successfully (see Connection_Settings.png)

       

      When I try and run a task, however, the task fails. The logs are below - I have redacted some info, in bold.

       

      ======================================================================

      DIRECTOR> VAR_27085 [2021-06-29 16:23:30.508] Parameter file [C:\Program Files\Informatica Cloud Secure Agent\apps\Data_Integration_Server\data\parameters\s_dss_010OLF0I000000000045.param] is opened for [session [wf_dss_010OLF0I000000000045.s_dss_010OLF0I000000000045]].

      DIRECTOR> VAR_27062 [2021-06-29 16:23:30.513] Warning! Cannot find section for worklet [wf_dss_010OLF0I000000000045] and folder [] in parameter file [C:\Program Files\Informatica Cloud Secure Agent\apps\Data_Integration_Server\data\parameters\s_dss_010OLF0I000000000045.param].

      DIRECTOR> TM_6014 [2021-06-29 16:23:30.525] Initializing session [s_dss_010OLF0I000000000045] at [Tue Jun 29 16:23:30 2021].

      DIRECTOR> TM_6683 [2021-06-29 16:23:30.528] Repository Name: [XMLRepository]

      DIRECTOR> TM_6684 [2021-06-29 16:23:30.528] Server Name: [rDTM]

      DIRECTOR> TM_6686 [2021-06-29 16:23:30.528] Folder: []

      DIRECTOR> TM_6685 [2021-06-29 16:23:30.528] Workflow: [wf_dss_010OLF0I000000000045] Run Instance Name: [] Run Id: [0]

      DIRECTOR> TM_6992 [2021-06-29 16:23:30.528] Opearting System Type [NT]

      DIRECTOR> TM_6101 [2021-06-29 16:23:30.528] Mapping name: m_dss_010OLF0I000000000045.

      DIRECTOR> TM_6964 [2021-06-29 16:23:30.529] Date format for the Session is [MM/DD/YYYY HH24:MI:SS]

      DIRECTOR> TM_6703 [2021-06-29 16:23:30.545] Session [s_dss_010OLF0I000000000045] is run by 64-bit Integration Service  [], version [10.2.0 HotFix2], build [0320].

      MANAGER> PETL_24091 [2021-06-29 16:23:31.964] Thread [MANAGER] has the ID [1].

      MANAGER> PETL_24058 [2021-06-29 16:23:31.964] Running Partition Group [1].

      MANAGER> PETL_24000 [2021-06-29 16:23:31.975] Parallel Pipeline Engine initializing.

      MONITOR> PETL_24091 [2021-06-29 16:23:31.981] Thread [MONITOR] has the ID [2].

      MANAGER> PETL_24001 [2021-06-29 16:23:31.986] Parallel Pipeline Engine running.

      MANAGER> PETL_24003 [2021-06-29 16:23:31.989] Initializing session run.

      MAPPING> PETL_24091 [2021-06-29 16:23:32.036] Thread [MAPPING] has the ID [3].

      MAPPING> CMN_1569 [2021-06-29 16:23:32.056] Server Mode: [UNICODE]

      MAPPING> CMN_1570 [2021-06-29 16:23:32.056] Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]

      MAPPING> TM_6151 [2021-06-29 16:23:32.056] The session sort order is [Binary].

      MAPPING> TM_6185 [2021-06-29 16:23:32.056] Warning. Code page validation is disabled in this session.

      MAPPING> CMN_65110 [2021-06-29 16:23:32.056] Current Timezone:[UTC -8:0]

      MAPPING> CMN_65111 [2021-06-29 16:23:32.056] Current Process ID:[7936]

      MAPPING> TM_6155 [2021-06-29 16:23:32.056] Using HIGH precision processing.

      MAPPING> TM_6180 [2021-06-29 16:23:32.057] Deadlock retry logic will not be implemented.

      MAPPING> TM_6181 [2021-06-29 16:23:32.057] Session source-based commit interval is [5000].

      MAPPING> PMJVM_42020 [2021-06-29 16:23:32.437] [INFO] Loaded library : C:\PROGRA~1\INFORM~1\apps\jdk\1.8.0_275_SA\jre\bin\server\jvm.dll.

      MAPPING> PMJVM_42014 [2021-06-29 16:23:32.441] [DEBUG] The value specified for option  is not valid and will be ignored.

      MAPPING> PMJVM_42014 [2021-06-29 16:23:32.441] [DEBUG] The value specified for option  is not valid and will be ignored.

      MAPPING> PMJVM_42014 [2021-06-29 16:23:32.441] [DEBUG] The value specified for option  is not valid and will be ignored.

      MAPPING> PMJVM_42014 [2021-06-29 16:23:32.441] [DEBUG] The value specified for option  is not valid and will be ignored.

      MAPPING> PMJVM_42014 [2021-06-29 16:23:32.441] [DEBUG] The value specified for option  is not valid and will be ignored.

      MAPPING> PMJVM_42009 [2021-06-29 16:23:32.699] [INFO] Created Java VM successfully.

      MAPPING> SDKS_38029 [2021-06-29 16:23:33.420] Loaded plug-in 500100: [plugin description].

      MAPPING> SDKS_38024 [2021-06-29 16:23:33.773] Plug-in 500100 initialization complete.

      MAPPING> SDKS_38017 [2021-06-29 16:23:33.774] Writer SDK plug-in initialization complete.

      MAPPING> SDKS_38509 [2021-06-29 16:23:35.318] SDK target and group initialization complete.

      MAPPING> TM_6308 [2021-06-29 16:23:35.333] DTM error log using code page UTF-8.

      MAPPING> TM_6310 [2021-06-29 16:23:35.336] DTM error log enabled at file [C:\PROGRA~1\INFORM~1\apps\Data_Integration_Server\data\error\s_dss_010OLF0I000000000045_4_error.csv].

      MAPPING> TM_6682 [2021-06-29 16:23:35.336] Warning: Since DTM Error Logging is enabled, row level errors will not be written to the session log. Please check the DTM Error Log for any row level errors.

      MAPPING> TM_6701 [2021-06-29 16:23:35.345] Warning: The data column delimiter for DTM Error logging and the flat file delimiter are the same [','].

      MAPPING> TE_7022 [2021-06-29 16:23:35.358] TShmWriter: Initialized

      MAPPING> DBG_21491 [2021-06-29 16:23:35.451] Source-based commit interval based on rows from Source Qualifier [SQ_vw_Employee]

      MAPPING> DBG_21244 [2021-06-29 16:23:35.489] Initializing Load Order Group [1]. Source Qualifier is [SQ_vw_Employee]

      MAPPING> TM_6007 [2021-06-29 16:23:35.490] DTM initialized successfully for session [s_dss_010OLF0I000000000045]

      DIRECTOR> PETL_24033 [2021-06-29 16:23:35.494] All DTM Connection Info: [<NONE>].

      MANAGER> PETL_24004 [2021-06-29 16:23:35.497] Starting pre-session tasks. : (Tue Jun 29 16:23:35 2021)

      PRE-SESS> PETL_24091 [2021-06-29 16:23:35.497] Thread [PRE-SESS] has the ID [4].

      MANAGER> PETL_24027 [2021-06-29 16:23:35.592] Pre-session task completed successfully. : (Tue Jun 29 16:23:35 2021)

      DIRECTOR> PETL_24006 [2021-06-29 16:23:35.592] Starting data movement.

      MAPPING> TM_6660 [2021-06-29 16:23:35.593] Total Buffer Pool size is 4786384 bytes and Block size is 263688 bytes.

      READER_1_1_1> PETL_24091 [2021-06-29 16:23:35.604] Thread [READER_1_1_1] has the ID [5].

      READER_1_1_1> DBG_21438 [2021-06-29 16:23:35.641] Reader: Source is [odbc://dbtype=SqlServer;host=HOST;port=PORT;database=DATABASE;AuthenticationMethod=ntlm2java], Type [ODBC], User [ACCOUNT CONFIGURED IN CONNECTION]

      READER_1_1_1> BLKR_16051 [2021-06-29 16:23:35.730] Source database connection [SrcConn_010OLF0B00000000000K] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]

      READER_1_1_1> CMN_1761 [2021-06-29 16:23:36.284] Timestamp Event: [Tue Jun 29 16:23:36 2021]

      READER_1_1_1> RR_4036 [2021-06-29 16:23:36.284] Error connecting to database [

      [Informatica][ODBC SQL Server Wire Protocol driver]Socket closed.[Informatica][ODBC SQL Server Wire Protocol driver][SQL Server]Login failed for user '[ACCOUNT WHICH AGENT IS RUNNING AS]$'.

       

      Database driver error...

       

      Function Name : Connect

       

      Database driver error...

       

      Function Name : Connect

       

      Database Error: Failed to connect to database using user [USERNAME CONFIGURED IN CONNECTION SETTINGS] and connection string [Driver=DataDirect CLOSED 8.0 SQL Server Wire Protocol for Informatica - R36;DB=TargetX;HOST=[HOST];PORT=1433;UID=[SAME USERNAME];WorkArounds2=2;EQI=1;AM=4;APP=Informatica;TimestampTruncationBehavior=1].].

      READER_1_1_1> CMN_1761 [2021-06-29 16:23:36.285] Timestamp Event: [Tue Jun 29 16:23:36 2021]

      READER_1_1_1> BLKR_16001 [2021-06-29 16:23:36.285] Error connecting to database...

      MANAGER> PETL_24031 [2021-06-29 16:23:36.335]