Pentaho DI Connection driver issues

0
votes

I'm having issues adding a MSSQL SERVER connection to Pentaho Data Intergration.

I tried the following:

  • I have downloaded the latest jdbc drivers (sqljdbc42) and moved them to the lib folder
  • I have created a new user in SQL SERVER and gave it all rights...didn't help.
  • very sure about username/password and login settings
  • reinstalled pentaho multiple times, reinstalled the jdbc drivers

I'm have a mac but use windows on parallels, and I work from this environment.

This is the error that I'm getting:

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

Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver) Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8

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

Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver) Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8

at org.pentaho.di.core.database.Database.normalConnect(Database.java:472) at org.pentaho.di.core.database.Database.connect(Database.java:370) at org.pentaho.di.core.database.Database.connect(Database.java:341) at org.pentaho.di.core.database.Database.connect(Database.java:331) at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80) at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2783) at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:597) 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.swt.tags.SwtButton.access$500(SwtButton.java:43) at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:137) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.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.xul.KettleDialog.show(KettleDialog.java:80) at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47) at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116) at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60) at org.pentaho.di.ui.repository.repositoryexplorer.controllers.ConnectionsController.editConnection(ConnectionsController.java:363) 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.swt.tags.SwtButton.access$300(SwtButton.java:43) at org.pentaho.ui.xul.swt.tags.SwtButton$2.mouseUp(SwtButton.java:103) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.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.xul.KettleDialog.show(KettleDialog.java:80) at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47) at org.pentaho.di.ui.repository.repositoryexplorer.RepositoryExplorer.show(RepositoryExplorer.java:146) at org.pentaho.di.ui.spoon.Spoon$24$1.run(Spoon.java:4045) at org.eclipse.swt.widgets.RunnableLock.run(Unknown Source) at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Unknown Source) at org.eclipse.swt.widgets.Display.runAsyncMessages(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1366) at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:8022) at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9277) at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:692) 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 com.microsoft.sqlserver.jdbc.SQLServerDriver) Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:585) at org.pentaho.di.core.database.Database.normalConnect(Database.java:456) ... 62 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4346) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:561) ... 63 more

Hostname :DESKTOP-LM88EPQ Port :1433 Database name :Velo

2
sql-serverjdbcpentaho-spoon

2 Answers

1
votes

Apparently this issue was caused because of an authentication issue with the driver. If you download your drivers (sqljdbc), also copy the file 'sqljbdc_auth.dll' to %PENTAHO_HOME%\design-tools\data-integration\libswt\win64

This will make sure your users and logins get the proper authorisation.

0
votes

On the Database connection definition, between the Test and the Explore button, you have a Feature List which gives you all the parameters used by the PDI for that connection.

Check the the driver class on line 20 (sometime you have more than one on your classpath).

Then drop the url on line 21, in your SQL-Developer or equivalent.

You can also write your own java class ; replace the driver, url, username, password by the walue ayou are using; compile it; run it; it tell you what went wrong.

import java.sql.DriverManager;
public class TestConnection {
    public static void main(String[] args) throws Exception{
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        DriverManager.getConnection("url", "username", "password");
        System.out.println("Ok");
    }    
}