🎉Community Raffle - Win $25

An exclusive raffle opportunity for active members like you! Complete your profile, answer questions and get your first accepted badge to enter the raffle.

Join and Win

Unable to connect to SQL server database

smalunjk

Hi, i recently installed studio and am trying to get it to connect to local SQL server db. When i add a new connection and click test, it just hangs.

note: i have verified the port number, i can telnet localhost 1433, connect to db from SSMS , verified that network parameters are set correctly, even tried connecting using localhost, 127.0.0.1, hostname, IP but to no avail.

Q: Why does the error refer to /etc/hosts (on a windows)?

Here's the exception I get in the log.

org.h2.jdbc.JdbcSQLException: Exception opening port "H2 TCP Server (tcp://<ip address>:62419)" (port may be in use), cause: "timeout; please check your network configuration, specially the file /etc/hosts" [90061-194]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
   at org.h2.message.DbException.get(DbException.java:179)
   at org.h2.tools.Server.start(Server.java:498)
   at org.h2.engine.Database.startServer(Database.java:808)
   at org.h2.engine.Database.open(Database.java:664)
   at org.h2.engine.Database.openDatabase(Database.java:276)
   at org.h2.engine.Database.<init>(Database.java:270)
   at org.h2.engine.Engine.openSession(Engine.java:64)
   at org.h2.engine.Engine.openSession(Engine.java:176)
   at org.h2.engine.Engine.createSessionAndValidate(Engine.java:154)
   at org.h2.engine.Engine.createSession(Engine.java:137)
   at org.h2.engine.Engine.createSession(Engine.java:27)
   at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:354)
   at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:116)
   at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:100)
   at org.h2.Driver.connect(Driver.java:69)
   at java.sql.DriverManager.getConnection(DriverManager.java:664)
   at java.sql.DriverManager.getConnection(DriverManager.java:270)
   at com.rapidminer.tools.usagestats.CtaDataSource.getConnection(CtaDataSource.java:101)
   at com.rapidminer.tools.usagestats.CtaDao.<init>(CtaDao.java:75)
   at com.rapidminer.tools.usagestats.CtaDao.<clinit>(CtaDao.java:45)
   at com.rapidminer.tools.usagestats.CallToActionScheduler.persistEvents(CallToActionScheduler.java:158)
   at com.rapidminer.tools.usagestats.CallToActionScheduler.access$100(CallToActionScheduler.java:48)
   at com.rapidminer.tools.usagestats.CallToActionScheduler$1.run(CallToActionScheduler.java:96)
   at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
   at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
   at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
   at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
   at java.lang.Thread.run(Thread.java:745)

Find more posts tagged with

AI Studio

Server

Databases

SQL

Accepted answers

All comments

BalazsBaranyRM

Hi,

you're referring to SQL Server and port 1433. So you're using Microsoft SQL Server, right?

The errors are coming from the H2 SQL engine, an open source embedded database written in Java.

So please select the correct database type first.

Regards,

Balázs

M_Martin

Good morning:

Connecting to SQL Server shouldn't be a problem using either the generic JDBC Driver for SQL Server or Microsoft's own JDBC Driver. I've attached two screenshots showing the paramters for one of the SQL Server connections I use often.

The file "MS_SQL_Connection_Parameters_Nr_1.png" is the baisc connection dialouge that you couls use as a model. Note the connection URL.

The file MS_SQL_Connection_Parameters_Nr_2.png is what you see if you click on the "Advanced" button in the first dialog - whoich contains other useful options that may apply at some point.

Hope this helps and best wishes, Michael Martin

MS_SQL_Connection_Parameters_Nr_2.png

MS_SQL_Connection_Parameters_Nr_1.png

smalunjk

good morning,

I have configured it the way you show it in your screenshots. It still won't work. as I said in my orginal posts, i used localhost, 127.0.0.1, hostname, ip address, with/without schema name but no luck.

any clues?

M_Martin

Sorry to hear that the issue persists.

I have never used an IP (http) address (i.e. 127.0.0.1) to refer to the SQL Server Instance Name in the connection dialouge - I have always used localhost as the Instance Name. In your environment, are SQL Server and rapidMiner Studio on the same machine?

What also works (I just tested it) is using the Machine Name as the SQL Server Instance Name. You can see your current Machine Name in Windows under Control Panel -> System -> Advanced system Settings. A tabbed dialog will come up that shows the current Full Machine Name (assuming Windows).

At the end of the configuration process I think that you want a connection URL to SQL Server that looks like:

jdbc:sqlserver//<localhost or Machine Name>:1433;databaseName=<SQL Server database Name>

You might also check your SQL Server Network configuration settings using the SQL Server Configuration Manager as it could be that somehow 1433 is not the port your SQL Server instance is running on. Checking the properties of the TCP / IP protocol shows which port SQL Server is running on. On my machine all netowrk protocols are enabled excepting VIA.

Also double check to be sure that your SQL Server instance currently permits remote connections. You can check this under the Properties dialog for the Instance Name of SQL Server on your machine on the the "Connections" page. There is an option that says "Allow remote connections to this server". Make sure that it is ticked.

From my experience, if you accepteed the default SQL Server Instance name when installing SQL Server on your machine, your instance name would be localhost.

Again, I've never used an IP address to refer to a SQL Server instance - either the deafult instance name of localhost or the Machine Name - both of which work for me with RapidMiner.

I hope the above was helpful - let me know how things go.

Michael

smalunjk

Yes, the database and the studio are on the same windows server. I have enabled the protocols, verified the port(1433) and ensured the "allow remote connections" is checked.

Any other clues?

M_Martin

OK - can you try connecting to SQL Server using your Machine Name (instead of localhost) as the SQL Server Instance Name? (and confirm there is no IP address in the connection string.)

Out of interest: have you connected to this SQL Server instance using other client programs or BI moddelware tool; (Tableau, QlikView, Omniscope, or via .NET programming (i.e. OLEDB), etc.) ?

Do you have the most recent version of the MS SQL Server jdbc driver? Have you set the PATH command to include the directory where the MS SQL Server driver is?

If this fails, can you please post what the acutal connection URL isin a reply? I can proxy your connection string on my system by creating a SQL Server database with a single table on my machine that matches the database you want to connect to on your machine.

Let me know and best wishes, Michael

smalunjk

I can login to the schema using SQL server mangement studio. I don't have any other clients on the server that i can try. I also tried connectiving to remote oracle db and same error. I am able to connect to remote oracle DB using SSIS from this box but not from RMS.

Pardon my ignorance, but i didn't realize i needed to setup JDBC drivers, paths etc. I think i know how to add a driver from using studio GUI but don't know how to set the path. can you please send instructions? what jdbc driver should i add? Doesn't the out of the box driver suffice?

I tried using the full machine name. and still no luck. can you email me directly for any screenshots of my system/URLs?

smalunjk

I was able to connect using same user from SSIS on same box. so, the user setup seems right to me.

M_Martin

Hi again:

I am attaching three .png files:

Manage_Database_Drivers_(Connections_Menu).png

MS_JDBC_Driver_Setup_Dialog.png

Databse_Configuration_Parameters.png

From the RM "Connections" menu, there is an option named "Manage Database Drivers". The first screenshot in my list above shows you what drivers I have on my system. Open that same dialog on your system and see if the "Microsoft SQL Server (Microsoft)" driver is installed. From what you have written, it sounds like the Microsoft jdbc Driver for SQL Server is not on your system.

The diownload URL for the MS JDBC Driver for SQL Server is:

https://www.microsoft.com/en-ca/download/details.aspx?id=11774

There are instructions on this page that explain how to set up the driver on your system. I was wrong about having to modify the PATH environment variable, I must have confused the jdbc driver with another utility I use.

After you have installed the JDBC driver from Microsoft, you need to configure RM Studio to use that driver. Take a look at the second .png file. The file shows the setup parameters. Setting up the driver includes browsing to the folder where you installed the jdbc driver on your system. Once you select the jar file that contains the driver (sqljdbc.jar - or it could be sqljdbc-4.1.jar) the value for the "Driver Class" will appear in the set up dialog. You can copy the info for the rest of the fields into your setup dialog from the second screenshot - in which I set up a second instance of the SQL Server jdbc Driver on my system.

After setting up the driver, save your changes, exit RapidMiner Studio and then open it again - then try to configure a connection to your SQL Server instance - an example configuration dialog from my system is hown in the third screen shot referenced above.

Let me know how things go - good luck!

Best wishes,

Michael Martin

Databse_Configuration_Parameters.png

MS_JDBC_Driver_Setup_Dialog.png

Manage_Database_Drivers_(Connections_Menu).png

M_Martin

Hi smalunjk;

Are you now able to connect to your SQL Server database?

darrel

I'm facing similar problem too, I have updated my firewall to change incoming rules. I can connect to my databas ethrough SSMS but not through rapid miner studio. I updated the JDBC drivers for SQL Server.

reaiity

I've had the same problem.

For me it was cause by proxy setting such a silly mistake.

But does this kind of program really need to respect system proxy? at least let us specify them in connection creation screen

I just disable proxy in internet option and pow! it's work now