Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Data Integration Connection to MSSQL with SSL

  1. #1
    Join Date
    May 2015
    Posts
    4

    Default Data Integration Connection to MSSQL with SSL

    Hi,

    I am attempting to configure a connection in spoon to an MSSQL server with SSL enabled and am unable to properly configure the connection.

    The MSSQL server uses our own CA Root Authority. I have imported the CA Cert and the public cert into the local java truststore /etc/ssl/certs/java/cacert (this is Ubuntu), and have set the options in the spoon connections property to include ssl = request (I have also tested ssl = require).

    Both methods fail to connect and generate this error on the Pentaho DI node:

    2015/06/01 13:52:16 - src_customers.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : An error occurred, processing will be stopped:
    2015/06/01 13:52:16 - src_customers.0 - Error occured while trying to connect to the database
    2015/06/01 13:52:16 - src_customers.0 -
    2015/06/01 13:52:16 - src_customers.0 - Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
    2015/06/01 13:52:16 - src_customers.0 - I/O Error: DB server closed connection.



    and generate this error on the MSSQL server....
    "Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: 10.1.1.1".


    Here is my configuration (I have also tried MSSQLNATIVE):
    <connection>
    <name>TestConnection</name>
    <server>host.example.com</server>
    <type>MSSQL</type>
    <access>Native</access>
    <database>TESTDB</database>
    <port>1433</port>
    <username>testuser</username>
    <password>Encrypted xxxxxxxxxxxxxxxxxxxxxx</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
    <attribute><code>FORCE_IDENTIFIERS_TO_LOWERCASE</code><attribute>N</attribute></attribute>
    <attribute><code>FORCE_IDENTIFIERS_TO_UPPERCASE</code><attribute>N</attribute></attribute>
    <attribute><code>IS_CLUSTERED</code><attribute>N</attribute></attribute>
    <attribute><code>MSSQL_DOUBLE_DECIMAL_SEPARATOR</code><attribute>N</attribute></attribute>
    <attribute><code>PORT_NUMBER</code><attribute>1433</attribute></attribute>
    <attribute><code>PRESERVE_RESERVED_WORD_CASE</code><attribute>N</attribute></attribute>
    <attribute><code>QUOTE_ALL_FIELDS</code><attribute>N</attribute></attribute>
    <attribute><code>SUPPORTS_BOOLEAN_DATA_TYPE</code><attribute>Y</attribute></attribute>
    <attribute><code>SUPPORTS_TIMESTAMP_DATA_TYPE</code><attribute>Y</attribute></attribute>
    <attribute><code>USE_POOLING</code><attribute>N</attribute></attribute>
    <attribute><code>EXTRA_OPTION_MSSQLNATIVE.ssl</code><attribute>request</attribute></attribute>
    </attributes>
    </connection>

    As a side note, I have other systems connecting to the mssql server without issue using these certs and SSL, so I believe the certs are in order.

    Any help would be appreciated!

    Thanks,
    Brian

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    According to the DB Connection you provided, you're not using jTDS, you're using MS SQL Native, but the error message says you're using jtds... Something doesn't match up here.

    ----
    Never mind, your post says you also tried Native.
    Did you download the JDBC driver from MS before trying the Native connection?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.