none
JDBCConnector cannot connect to database locally RRS feed

  • Question

  • Hi,

    I have FASTSearch for Sharepoint2010 with a locally installed SQL database on the same server.  This is a single server FAST installation. I am trying to crawl a database using the JDBC connector with integratedSecurity.  I am able to crawl a database on another server but cannot on this server.  I was also able to crawl the database on this server from a remote server using JDBC so I think permissions are fine since I used the same userid for all crawls.  TCP is enabled and I did not enable EnableStatustracker gathering but I still get this error:

    PS D:\FASTSearch\bin> .\jdbcconnector.bat start -f ..\etc\Test.xml

    Copyright (c) Microsoft Corporation.  All rights reserved.
    18:34:51,267 INFO  [JDBCConnector] Starting the connector!
    18:34:51,267 INFO  [JDBCConnector] Validating config.......
    18:34:51,407 WARN  [JDBCConnector] Empty parameter Input/JDBCPassword in config file
    18:34:51,517 INFO  [JDBCConnector] Testing connections to external systems
    18:34:51,532 WARN  [JDBCConnector] Empty parameter Input/JDBCPassword in config file
    18:34:51,548 INFO  [JDBCConnector] Checking if connections to source and target work....
    18:36:53,902 ERROR [JDBCConnector] Failed creating adapter instace. Test connection failed.
    18:36:53,902 ERROR [JDBCConnector] Caused by: Could not connect to database: Connection reset
    18:36:53,902 ERROR [JDBCConnector] Caused by: Connection reset
    18:36:53,902 INFO  [JDBCConnector] Connection made to source system

    The following are the contents of the JDBC xml file:

    <!DOCTYPE FastConfig SYSTEM "dtd\FastConnectorConfig-1.0.dtd"[]>
    <!-- For permissions and the most current information about FAST Search Server 2010 for SharePoint configuration files, see the online documentation, (http://go.microsoft.com/fwlink/?LinkId=1632279). -->
    <FastConfig>
      <description><![CDATA[This file (or usually a copy of it) need to be configured with the right values for the connector to perform properly.]]></description>
      <group name="Input">
        <description><![CDATA[The parameters in the "Input" group are defining properties of the sources from where the data will be fetched ]]></description>
        <parameter name="JDBCDriver" type="string">
          <description><![CDATA[MANDATORY. JDBC driver class name. See JDBC driver documentation. Examples: <br>Oracle: oracle.jdbc.driver.OracleDriver <br>MS SQL 2k: com.microsoft.jdbc.sqlserver.SQLServerDriver <br>MS SQL 2005: com.microsoft.sqlserver.jdbc.SQLServerDriver <br>DB2 (UDB 8 JDBC): com.ibm.db2.jcc.DB2Driver <br>MySQL: com.mysql.jdbc.Driver <br>Sybase net.sourceforge.jtds.jdbc.Driver <br>Default: (none)]]></description>
          <!-- Example driver values. Remove comment around value tag to use them -->
          <!-- <value>oracle.jdbc.driver.OracleDriver</value> -->
          <!-- Oracle -->
          <!-- <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value> -->
          <!--  MS SQL 2000 -->
          <!-- <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value> -->
          <!--  MS SQL 2005/2008 -->
          <!-- <value>com.ibm.db2.jcc.DB2Driver</value> -->
          <!--  DB2 -->
          <!-- <value>com.mysql.jdbc.Driver</value> -->
          <!--  MySQL -->
          <!-- <value>net.sourceforge.jtds.jdbc.Driver</value> -->
          <!--  Sybase -->
          <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value>
        </parameter>
        <parameter name="JDBCURL" type="string">
          <description><![CDATA[MANDATORY. JDBC URL. See JDBC driver documentation for format. Examples: <br>Oracle: jdbc:oracle:thin:@yourServerHere:1521:yourDataBaseNameHere <br>MS SQL 2k: jdbc:microsoft:sqlserver://yourServerHere:1433;DatabaseName=yourDataBaseNameHere <br>MS SQL 2005: jdbc:sqlserver://yourServerHere:1433;database=yourDataBaseNameHere(default is 1433) <br>DB2 (UDB 8) JDBC: jdbc:db2://yourServerHere:50000/yourDatabaseNameHere<br>MySQL: jdbc:mysql://host:3306/yourDatabaseHere?param1=value1&amp;param2=value2 <br>jTDS driver against Sybase: jdbc:jtds:sybase://yourServerHere:<port>/yourDataBaseNameHere <br>Default: (none)]]></description>
          <!-- Example connection string values. Remove comment around value tag to use them -->
          <!-- <value>jdbc:oracle:thin:@yourServerHere:1521:yourDataBaseNameHere</value> -->
          <!-- Oracle -->
          <!-- <value>jdbc:microsoft:sqlserver://yourServerHere:1433;DatabaseName=yourDataBaseNameHere</value> -->
          <!--  MS SQL 2000 -->
          <!-- <value>jdbc:sqlserver://yourServerHere:1433;database=yourDataBaseNameHere</value> -->
          <!--  MS SQL 2005/2008 -->
          <!-- <value>jdbc:db2://yourServerHere:50000/yourDatabaseNameHere</value> -->
          <!--  DB2 -->
          <!-- <value>jdbc:mysql://host:3306/yourDatabaseHere?param1=value1&amp;param2=value2</value> -->
          <!--  MySQL -->
          <!-- <value>jdbc:jtds:sybase://yourServerHere:<port>/yourDataBaseNameHere</value> -->
          <!--  Sybase -->
          <value>jdbc:sqlserver://serverA:1433;database=TestMetrics;integratedSecurity=true</value>
        </parameter>
        <parameter name="JDBCUsername" type="string">
          <description><![CDATA[Database username. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCPassword" type="string">
          <description><![CDATA[Database password. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCSQL" type="string">
          <description><![CDATA[This or JDBCSQLFile must be provided. SQL query to crawl against.  Note, any valid SQL is valid here.  <br>Use %TIMESTAMP% where last crawl time gets inserted as a datetime value.  <br>Use %TIMESTAMPSEC% where last crawl time gets inserted as number of seconds since epoch <br> Examples: <br> Oracle: SELECT * FROM tableName WHERE dateField > TO_TIMESTAMP('%TIMESTAMP%','yyyy-MM-DD"T"hh24:mi:ss')&nbsp; <br>Note that the time stamp format used must be as indicated here. <br>MS SQL Server: SELECT * from tableName WHERE dateField > convert(datetime,'%TIMESTAMP%',126)<br>select * from employees <br>Default: (none)]]></description>
          <value><![CDATA[SELECT [TestIndent],[Field1],[Field2]  FROM [TestMetrics].[dbo].[TestTable]]]></value>
        </parameter>
        <parameter name="JDBCSQLFile" type="string">
          <description><![CDATA[This or JDBCSQL must be provided. Name of file (absolute path) containing SQL query to crawl against. Use if the query is too long for the GUI. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCDeleteSQL" type="string">
          <description><![CDATA[SQL query to delete against.  Note, any valid SQL is valid here.  <br>Use %TIMESTAMP% where last delete time gets inserted as a datetime value.  <br>Use %TIMESTAMPSEC% where last crawl time gets inserted as number of seconds since epoch <br> Examples: <br> Oracle: SELECT * FROM tableName WHERE dateField > TO_TIMESTAMP('%TIMESTAMP%','yyyy-MM-DD"T"hh24:mi:ss')&nbsp; <br>Note that the time stamp format used must be as indicated here. <br>MS SQL Server: SELECT * from tableName WHERE dateField > convert(datetime,'%TIMESTAMP%',126)<br>select * from employees <br>Default: (none)]]></description>
          <value><![CDATA[]]></value>
        </parameter>
        <parameter name="JDBCDeleteSQLFile" type="string">
          <description><![CDATA[Name of file (absolute path) containing SQL query to delete against. Use if the query is too long for the GUI. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCPreSQL" type="list" separator=";">
          <description><![CDATA[SQL statements that are run prior to running the extract statement. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCPostSQL" type="list" separator=";">
          <description><![CDATA[SQL statements that are run after the extract statement. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCTimeStampField" type="string">
          <description><![CDATA[Timestamp field to use as the source for last crawl time when using time stamp based update detection. Give the name of the DB timestamp column as value. The highest value for the given attribute is set as the last crawl time. Note: using this field implies that a timestamp is extracted by the SQL statement. <br>Default: (none)]]></description>
          <value></value>
        </parameter>
        <parameter name="JDBCTimeType" type="string">
          <description><![CDATA[Choose date or seconds.<br>Default: date]]></description>
          <value>date</value>
        </parameter>
        <parameter name="JDBCDeleteTimeStampField" type="string">
          <description><![CDATA[Timestamp field to use as the source for last delete time when using time stamp based deletion. Give the name of the DB timestamp column as value. The highest value for the given attribute is set as the last crawl time. Note: using this field implies that a timestamp is extracted by the SQL statement. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCDeleteTimeType" type="string">
          <description><![CDATA[Time used in the JDBC delete timestamp file. Choose date or seconds.<br>Default: date]]></description>
          <value>date</value>
        </parameter>
        <parameter name="JDBCUpdateTimeStampOnFailure" type="boolean">
          <description><![CDATA[If the connector receives error callbacks from FAST Search Server 2010 for SharePoint or there are missing callbacks, should the timestamp still file be updated? <br>Default: false]]></description>
          <value>false</value>
        </parameter>
        <parameter name="JDBCUpdateTimeStampOnDeletion" type="boolean">
          <description><![CDATA[should the delete timestamp file still needs to be updated upon deletion <br>Default: false]]></description>
          <value>false</value>
        </parameter>
        <parameter name="JDBCFetchSizeHint" type="integer">
          <description><![CDATA[Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. The number of rows specified affects only result sets created using this statement. If the value specified is zero, then the hint is ignored. <br>Default: 0]]></description>
          <value>0</value>
        </parameter>
        <parameter name="JDBCCharacterEncoding" type="string">
          <description><![CDATA[Encoding used in database. Relevant when the table contains character data (LONGVARCHAR type) <br>Default: UTF-8]]></description>
          <value>UTF-8</value>
        </parameter>
      </group>
      <group name="Transformation">
        <description><![CDATA[The parameters in "Transformation" group are describing which data, and how data are transformed into the FAST Search Server 2010 for SharePoint items. ]]></description>
        <parameter name="JDBCPrimaryKeyField" type="string">
          <description><![CDATA[MANDATORY. The database view/table primary key.  Used to create the Fast Document ID. If GetValidFieldNames is true, you can also use the converted key name here (lowercase, only alphanumeric). If GetValidFieldNames is false, make sure that the use of case matches what comes back from the database in your SELECT statement. <br>Example: employeeid <br>Default: (none)]]></description>
          <value>TestIndent</value>
        </parameter>
        <parameter name="JDBCDeletePrimaryKeyField" type="string">
          <description><![CDATA[The database view/table primary key.  Used to Delete the Fast Document ID. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCUseBytesForString" type="boolean">
          <description><![CDATA[Create byte arrays instead of strings.  Workaround for some encoding issues. <br>Default: false]]></description>
          <value>false</value>
        </parameter>
        <parameter name="UseMultiRowNormalizer" type="boolean">
          <description><![CDATA[If true, values for columns of records with identical identifiers are merged. Note: In order for
       this feature to work, the SELECT statement must have an ORDER BY clause on the primary key field. <br>Default: false]]></description>
          <value>false</value>
        </parameter>
        <parameter name="JDBCAttributeColumn" type="string">
          <description><![CDATA[For normalized tables, the column that contain attribute names.
       This setting must not be used with JDBCMultiValueIncludeFields and JDBCMultiValueSkipFields.
       Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCValueColumn" type="string">
          <description><![CDATA[For normalized tables, the column that contain attribute values.
       This setting must not be used with JDBCMultiValueIncludeFields and JDBCMultiValueSkipFields.
       Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCMultiValueIncludeFields" type="string">
          <description><![CDATA[Semi colon separated list of column names that should be concatenated in the output.
      If this field is set, it'll override any settings to JDBCMultiValueSkipFields.
      This setting must not be used with JDBCAttributeColumn and JDBCValueColumn.]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="JDBCMultiValueSkipFields" type="string">
          <description><![CDATA[Semi colon separated list of column names that have the same values in each row, and that should not be repeated in the output.
       This setting must not be used with JDBCAttributeColumn and JDBCValueColumn.
       Default: (none)]]></description>
          <value>
          </value>
        </parameter>
      </group>
      <group name="Output" expand="no">
        <description><![CDATA[The parameter in the "Output" group is defining the operation mode of the connector: Add/Update]]></description>
        <parameter name="OperationMode" type="string">
          <description><![CDATA[Must be one of add, or update.
       Update should only be used if you want to do a partial update on already existing documents, i.e. during an incremental crawl. 
       Default: (add)]]></description>
          <valid-values>add;update</valid-values>
          <value>add</value>
        </parameter>
      </group>
      <group name="FASTSearchSubmit" expand="no">
        <description><![CDATA[The parameters in the "FASTSearchSubmit" group describe properties of the communication with FAST Search Server 2010 for SharePoint when submitting data.  ]]></description>
        <parameter name="Collection" type="string">
          <description><![CDATA[MANDATORY. Name of collection to feed to. <br>Default: (none)]]></description>
          <value>FastTest</value>
        </parameter>
        <parameter name="ActuallyPublish" type="boolean">
          <description><![CDATA[If true, actually submit the documents to FAST Search Server 2010 for SharePoint. <br>Default: true ]]></description>
          <value>true</value>
        </parameter>
      </group>
      <group name="XMLExport" expand="no">
        <description><![CDATA[The parameters in the "XMLExport" group are defining values regarding export to XML formatted file(s)  ]]></description>
        <parameter name="ExportToXML" type="boolean">
          <description><![CDATA[If true, export all documents as XML. <br>Default: false ]]></description>
          <value>false</value>
        </parameter>
        <parameter name="BinaryFields" type="string">
          <description><![CDATA[Semi colon separated list of field names of binary fields. Will be BASE64 encoded in the XML. <br>Default: data]]></description>
          <value>data</value>
        </parameter>
      </group>
      <group name="ChangeDetection" expand="no">
        <description><![CDATA[The parameters in the "ChangeDetection" group specify how to detect changes  in the database since last feeding from the database(s), using row checksums  ]]></description>
        <parameter name="Enabled" type="boolean">
          <description><![CDATA[If true, changedetection will be enabled. <br>Default: false ]]></description>
          <value>false</value>
        </parameter>
        <parameter name="ChangeDBPurge" type="boolean">
          <description><![CDATA[If true, will delete all entries in the checksum database on startup. <br>Default: false ]]></description>
          <value>false</value>
        </parameter>
        <parameter name="ChangeDBAbortPercent" type="integer">
          <description><![CDATA[When using checksum based change detection, this value defines the maximum percentage of accepted deleted records in the source.  If a higher percentage of deletions is observed during a run, this is considered an error situation, and no changes are performed to the FAST Search Server 2010 for SharePoint collection. <br>Default: 10]]></description>
          <value>10</value>
        </parameter>
        <parameter name="ChangeDBIncludeFields" type="string">
          <description><![CDATA[A semi colon separated list of field names that are to be included in the checksum computations. <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
        <parameter name="ChangeDBExcludeFields" type="string">
          <description><![CDATA[A semi colon separated list of field names that are to be excluded from the checksum computations. If present, these take precedence over the include field list. As an example, if one knows that only the metadata of a document may change, not the binary content, one can exclude the data element from the computations.  <br>Default: (none)]]></description>
          <value>
          </value>
        </parameter>
      </group>
      <group name="ConnectorExecution" expand="no">
        <description><![CDATA[The parameters in the "ConnectorExecution" group specify general values used during execution of the connector  ]]></description>
        <parameter name="QueueSize" type="integer">
          <description><![CDATA[Queue size for internal queue of documents. <br>Default: 200, Range: 1 - 10000]]></description>
          <range>1-10000</range>
          <value>200</value>
        </parameter>
        <parameter name="EnableStatustracker" type="boolean">
          <description><![CDATA[The status tracker logs the item crawl status to the database. Set to false to disable status tracking.]]></description>
          <value>false</value>
        </parameter>
        <parameter name="WorkFolder" type="string">
          <description><![CDATA[The work folder for all file output, including log files and XML files]]></description>
          <value>../var/jdbcconnector</value>
        </parameter>
      </group>
      <group name="General">
        <parameter name="Name" type="string">
          <description><![CDATA[Configuration name. This specifies the prefix for the propset, and must only consist of the characters 0-9 and a-f. Maximum 32 characters. The parameter is optional.]]></description>
          <value>e3b75af2346c4b5495683bbc1d567323</value>
        </parameter>
      </group>
    </FastConfig>

    Does anyone have any ideas? Any help is greatly appreaciated.


    Frank Chin

    Monday, February 27, 2012 2:57 AM

All replies

  • Hi

    Instead of:jdbc:sqlserver://serverA:1433;database=TestMetrics;integratedSecurity=true
    Try using: jdbc:sqlserver://serverA;DatabaseName=TestMetrics;integratedSecurity=true

    I've been using FAST and the JDBC Connector to índex a database on the same server, and it works fine.

    Best Regards,
    André.

    • Proposed as answer by Apprentice12 Monday, May 20, 2013 4:36 AM
    Tuesday, May 15, 2012 9:42 AM