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.
Pythonista anonymous :
I ran the SQL Server 2014 configuration manager, and checked:\n\nSQL Server network configuration --> Protocols for SQLEXPRESS --> TCP/IP\n\nIn the protocol tab, I had: enabled: yes and Listen All: Yes\n\nIn the \"IP Addresses\" tab, under IPAll at the bottom, \"TCP Dynamic ports\" was set to 49178. (No idea why).\n\nSo 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!\n\nIf I don't specify the port, Pentaho tries port 1433, and it doesn't work.\n\nFor reference, the connection string I use with Python and SQL Alchemy does not specify the port explicitly.\n\nparams = '?driver=SQL+Server+Native+Client+11.0'\nengine = create_engine('mssql+pyodbc://' + ServerName + '/'+ Database + params, encoding ='latin1' )\nconn=engine.connect()\n",
2018-02-02T14:15:23