Pentaho data integration: jdbc connection to MS SQL Server not working

Question

With the community edition of Pentaho Data Integration - Kettle 5.0.1 (http://community.pentaho.com/projects/data-integration/) I could connect to my MS SQL Server 2014 database with no issues, choosing MS SQL Serever and then Native (JDBC) from tools -? wizard -> create database connection.

I have now upgraded to the latest 5.4 version, and I can no longer connect.

The error I get is the following.

Any suggestions on how to fix this? What I find confusing is that I can't find a way to tell Pentaho where excatly the jdbc drivers are. E.g. I downloaded Microsoft's JDBC drivers (https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774), but how do I get Pentaho to use them? I copied the file sqljdbc42.jar to c:\Pentaho\lib but to no avail. I also tried using it in the 'generic database' option but, again, to no avail.

Error connecting to database [c] : org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.


org.pentaho.di.core.exception.KettleDatabaseException: 
Error occurred while trying to connect to the database

Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.


    at org.pentaho.di.core.database.Database.normalConnect(Database.java:428)
    at org.pentaho.di.core.database.Database.connect(Database.java:358)
    at org.pentaho.di.core.database.Database.connect(Database.java:311)
    at org.pentaho.di.core.database.Database.connect(Database.java:301)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2686)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.test(DatabaseDialog.java:109)
    at org.pentaho.di.ui.core.database.wizard.CreateDatabaseWizardPage2.test(CreateDatabaseWizardPage2.java:157)
    at org.pentaho.di.ui.core.database.wizard.CreateDatabaseWizardPage2$3.widgetSelected(CreateDatabaseWizardPage2.java:147)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.di.ui.core.database.wizard.CreateDatabaseWizard.createAndRunDatabaseWizard(CreateDatabaseWizard.java:111)
    at org.pentaho.di.ui.spoon.Spoon.createDatabaseWizard(Spoon.java:7595)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)
    at org.pentaho.ui.xul.jface.tags.JfaceMenuitem.access$100(JfaceMenuitem.java:43)
    at org.pentaho.ui.xul.jface.tags.JfaceMenuitem$1.run(JfaceMenuitem.java:106)
    at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
    at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:545)
    at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:490)
    at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:402)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1319)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9190)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:654)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
Error connecting to database: (using class net.sourceforge.jtds.jdbc.Driver)
I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:592)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:4697)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:414)
    ... 43 more
Caused by: java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:615)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:578)
    ... 45 more
Caused by: java.io.IOException: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
    at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1893)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:588)

score 4 · Answer 1 · edited May 23 '17 at 11:46

4

I fixed this by copying the ntlmauth.dll file from the old Pentaho installation to c:\Pentaho\libswt\win32.

I got an hint from this post: I/O Error: SSO Failed: Native SSPI library not loaded

In summary, this dll is related to integrated authentication, i.e. logging into a SQL server with your Windows ID, rather than explicitly telling the driver your id and password.

I do not honestly understand why the old installation had this file and the new one doesn't, but this works.

Other alternatives could be getting the same file from Microsof's official jdbc driver (although I'm not sure if that dll would then work only with Microsoft's JDBC or with Pentaho's, too), or from the JTDS package (http://sourceforge.net/projects/jtds/)

edited May 23 '17 at 11:46

Community

1
1

answered Oct 07 '15 at 15:27

Pythonista anonymous

8,140
20
70
112

1

I'm using JTDS with no trouble. I had a similar problem when I started with 5.4. Not sure if it's the "reason", but I understand PDI has dropped official support for SQL Server as a host for DB repositories. I know I couldn't make it work. Good thing Postgres is free. – Brian.D.Myers Oct 07 '15 at 17:32
Do you have a link to the changelog? I'd be curious to understand why. I couldn't find it. It's weird, because PDI 5.4 still gives me the option to connect to SQL Server, and still comes with JDBC drivers that work with SQL Server - it's apparently only the integrated authentication that was causing me a problem. – Pythonista anonymous Oct 08 '15 at 09:06
PS This kind of undocumented or poorly documented changes (especially those which break backward compatibility) are one of the key plagues of open source and freeware software, which hinder their adoption in commercial environments, where time is money and you need an answer yesterday... – Pythonista anonymous Oct 08 '15 at 09:31
I'm afraid I didn't see it in a change log. It was just on a site somewhere, so don't take it as gospel. I did however try to house a repo on SQL Server and failed due to SQL dialect problems. I've always had trouble with the integrated auth. You have to get that DLL in the right place. When I found how to do it with JTDS, I switched to that. – Brian.D.Myers Oct 08 '15 at 16:44

score 1 · Answer 2 · answered Nov 21 '16 at 17:30

1

I solved coping driver sqljdbc.jar in C:\PENTAHO\pdi-ce-7.0.0.0-25\data-integration\lib. And also copy the file sqljdbc_auth.dll for allow another type of autentication.

Finally, in the type of connection I chose MS SQL SERVER(Native).

answered Nov 21 '16 at 17:30

gus

17
4

score 0 · Answer 3 · answered Aug 09 '21 at 15:46

0

i solved coping driver sqljdbc42. jar and jtds-1.3

answered Aug 09 '21 at 15:46

Jose Luis Uyuni

1
1

Pentaho data integration: jdbc connection to MS SQL Server not working

3 Answers3