Pythonista anonymous
Pythonista anonymous

Reputation: 8980

Pentaho Kettle: cannot connect to MS SQL Server Express

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.

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.

Upvotes: 2

Views: 6305

Answers (2)

maQbex
maQbex

Reputation: 21

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..

Upvotes: 0

Pythonista anonymous
Pythonista anonymous

Reputation: 8980

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()

Upvotes: 5

Related Questions