Pentaho Kettle: cannot connect to MS SQL Server Express

Question

I have Microsoft SQL Server 2014 Express running on my local machine, a Windows 7 PC.

I use Python and can connect to the server (from the very same PC) without any problems, using SQL alchemy. I can also connect using Excel and the PowerPivot add-in.

However, I cannot connect with Pentaho - Kettle.

• I have downloaded the JDBC driver from Microsoft: https://www.microsoft.com/en-gb/download/details.aspx?id=11774
• I took the file jre8\sqljdbc42.jar and moved it to the lib subfolder of the Pentaho Data Integration installation. I took the file in jre8, not jre7, because my Java is JRE1.8.0_144
  • I then took the file in auth\x86\sqljdbc_auth.dll and copied it to the same lib folder. I took the x86 file, not the x64, because Java is in c:\Program Files (x86)\Java\jre1.8.0_144\
  • I add 'table input' and set connection type= MS SQL Server (native) and Access = Native (JDBC)

The error message I get is:

Error connecting to database [mydatabase] :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) The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".


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) The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

I have tried various combinations of specifying port number 1433 and of leaving it blank, of using integrated security and of entering my password manually, of setting host name to localhost and to DESKTOP-MYNAME\SQLEXPRESS, but to no avail.

I have read lots of discussions about it but none seems relevant; the SQL Server does accept incoming connections, e.g. from Python and SQL Alchemy.

Answer 1

SQL Server network configuration --> Protocols for SQLEXPRESS --> TCP/IP

In the protocol tab, I had: enabled: yes and Listen All: Yes

In the "IP Addresses" tab, under IPAll at the bottom, "TCP Dynamic ports" was set to 49178.

So I set the port in the 'table input' setup of Pentaho to 49717.

this worked for me also..

Answer 2

I ran the SQL Server 2014 configuration manager, and checked:

SQL Server network configuration --> Protocols for SQLEXPRESS --> TCP/IP

In the protocol tab, I had: enabled: yes and Listen All: Yes

In the "IP Addresses" tab, under IPAll at the bottom, "TCP Dynamic ports" was set to 49178. (No idea why).

So I set the port in the 'table input' setup of Pentaho to 49178. It managed to find the server, but there was an error about integrated security. So I copied sqljdbc_auth.dll to both the bin and the lib subfolders of C:\Program Files (x86)\Java\jre1.8.0_144 .... and now it works!

If I don't specify the port, Pentaho tries port 1433, and it doesn't work.

For reference, the connection string I use with Python and SQL Alchemy does not specify the port explicitly.

params = '?driver=SQL+Server+Native+Client+11.0'
engine = create_engine('mssql+pyodbc://' + ServerName + '/'+ Database + params, encoding ='latin1' )
conn=engine.connect()