Manage SQL Server Connection

michaelgloven

I'm running RapidMiner Studio 9 and am unable to connect to a SQL Server database thru the manage database connections. My SQL Server is on the same pc as RapidMiner, and I have three other applications which connected to SQL Server in minutes (ESRI ArcMap, Tableau and Excel) so I believe my settings are correct (TCP\IP enabled, etc.).

 

According to the tutorial the "Host" field to be entered in the connection details is the driver path (?). Can someone explain what the Host field requires? Also, I'm using port 1433 and my instance uses windows authentication. I see the instructions for setting up WA but I'm concerned about the consequences of making these types of system changes just for RM. Should I set-up the database instance to use database security instead (vs WA)? Anyways, just looking for some guidance on setting-up my development environment on my local pc to allow RM to easily read and write to SQL Server.

 

My error message is The TCP/IP connection to the host localhost, port 1433 failed. Error:"connect timed out. Verify.

 

Thank you!

michaelgloven

ok, I had to do a few things to get this to work with SQL Server 2014:

- Go to SQL Server Configuration Manager, SQL Server Network Configuration, Protocols <Instance Name>, right click on TCP/IP and for IP Addresses enter in the TCP Port as 1433 (or port of choice) for IPAll. Make sure TCP\IP is enabled, right click on TCP/IP.

- Other settings are User & Password are blank for Windows Authentication, Integrated Security set to true, need to specify host (or use localhost if local) and database name

 

At least this worked for me....

rfuentealba

Hello @michaelgloven,

 

Are you using the Microsoft JDBC driver or the jTDS one? In the past I had issues with jTDS connecting to Windows Authentication but I switched to this Microsoft JDBC driver and had no more issues. Yes, the host you should specify is localhost if you are using database security, but for Windows Authentication it is most likely that the authentication will be transported via WINS or something like that. In any case, do you mind sharing the tutorial you are reading?

 

My connection URL is jdbc:sqlserver://PC-Rodrigo\Rodrigo;databaseName=rodrigo;integratedSecurity=true; and it works. Make sure you go to the Advanced button and mark integratedSecurity as true.

 

Regarding database security v/s operating system security, if you are on Windows it shouldn't matter, as it resolves things with its internals and every non-open source thing knows how to use the Windows stack for it (the JDBC driver isn't an exception). However, if you plan to use some old UNIX libraries, these might complain if you use Windows Authentication. In that case I'd prefer to switch to database security.

 

Hope it helps, and looking forward to meet you in New Orleans! I remember reading you in the list of speakers.

 

All the best,

 

michaelgloven

thanks Rodrigo.

 

I can't tell for sure which driver RM is using except for what is shown in the "manage database drivers" screen:

Microsoft SQL Server (Microsoft)

com.microsoft.sqlserver.jdbc.SQLServerDriver

jar:file:/C:/Program%20Files/RapidMiner/RapidMiner%20Studio/lib/rapidminer-studio-core-9.0.2.jar!/com/rapidminer/resources/icons/16/ok.png

 

I also tried "localhost" without success, same error. Here's the tutorial https://rapidminer.wistia.com/medias/th4jxcrww6 and you'll find the odd instruction at 3:38.

 

I tried the advanced security setting as suggested, but still get a time out error. I think I'll eventually find an simple fix since I integrate numerous other applications with my SQL server 2014 quite easily. Here's one of many iterations I attempted:

 

 

sgenzer

cc @jmergler @Knut-RM

Jeff_Mergler

Unfortunately I don't have a quick solution to the problem, but I can help explain the tutorial.  The comment about the driver is merely a statement that the driver is a critical part of the definitition of the database system. So when you select the database system you are selecting the driver associated with that database system definition. I think the timing of the audio and video could be improved. While the audio portion is still discussing the database system, the cursor in the video moves ahead to the host field. The host field does not define the driver, the database system does. I hope that helps clarify one point!

Jeff

michaelgloven

thanks Jeff, that helps clarify but I'm still stuck. What's interesting is I have other complex applications working against the SQL 2014 database I'm trying to connect with RapidMiner. I've worked with the connection parameters I understand without success, maybe somebody with similar experience can offer a suggestion or troubleshooting ideas.

Edin_Klapic

Hi @michaelgloven,

 

I am not sure if this is the same error but perhaps the solution for the user KellyM in the thread https://community.rapidminer.com/t5/RapidMiner-Studio-Forum/Write-Database-ODBC-connection/m-p/43212 works for you as well.

 

Happy Mining,

Edin

michaelgloven

I'm troubleshooting thru the log files but it does not look like actions taken i.e. "Test Connection" are being logged in the rapidminer-studio.log file. I do get a start-up warning WARNING: Missing database driver class name for ODBC Bridge, but am not sure what this means. Anyone have other ideas on troubleshooting this issue?

michaelgloven

ok, I had to do a few things to get this to work with SQL Server 2014:

- Go to SQL Server Configuration Manager, SQL Server Network Configuration, Protocols <Instance Name>, right click on TCP/IP and for IP Addresses enter in the TCP Port as 1433 (or port of choice) for IPAll. Make sure TCP\IP is enabled, right click on TCP/IP.

- Other settings are User & Password are blank for Windows Authentication, Integrated Security set to true, need to specify host (or use localhost if local) and database name

 

At least this worked for me....

rfuentealba

Hi @michaelgloven,

 

Great you finally found the issue! Just one comment in case others get this:

 

If you are using Windows Authentication and you are the logged in user in your local machine, sure! there is no need for you to actually enter a username and a password (I found this today). However, Windows Authentication will very likely fail if your SQL Server is not in your machine. In that case, you must provide a username and password that is both enabled to login to your server machine and has enough permissions to connect to the server, and have TCP/IP enabled as you mention.

 

All the best,