Microsoft SQL Server Connector - need configuration help

Hi there,
I am on a Windows machine where I can establish a connection to a MS SQL Server with Excel successfully but I have no clue how to configure the Microsoft SQL Server Connector node to establish the connection.

In Excel it looks like this:
server name: server\instance
authentication: using windows authentication
then I can select the database (mydb)

In KNIME:

  • I’ve installed the Microsoft JDBC Driver For SQL Server
  • I’ve selected it in the node
  • Hostname: //server/instance
  • Port: 1.433 (default)
  • Database: mydb
  • Authentication: None/Native
  • JDBC-Parameters: integratedSecurity=true

I played around with the hostname but whatever I type in, I always get this error message:

ERROR Microsoft SQL Server Connector 0:1 Execute failed: Fehler beim Herstellen der TCP/IP-Verbindung mit dem Host '//g84dbserver', Port 1433. Fehler: '//g84dbserver. Überprüfen Sie die Verbindungseigenschaften. Stellen Sie sicher, dass eine SQL Server-Instanz auf dem Host ausgeführt wird und am Port TCP/IP-Verbindungen akzeptiert. Vergewissern Sie sich, dass am Port keine TCP-Verbindungen durch eine Firewall blockiert werden.'.

Can anybody help to configure that node or to find out why it is not working?

Best,
Antje

Hi @niederle

Which version of Knime do you use? I’m using Knime 4.3.1 (and Windows10) and I did not have to install any driver as there was already a Microsoft SQL Server driver present in Knime which works fine with this integrated security.
In the DB Connector node I chose this:
afbeelding

With a Database URL like this
jdbc:sqlserver://mysqlserver:1433;databaseName=mydatabase;integratedSecurity=true

This works for me.

1 Like

One thing I forgot to mention: you need to install these extensions:
afbeelding

The legacy ones are only required when you have workflows created prior to Knime 4.

1 Like

I have this extension installed.

I tried to use the general Database Connector as you suggested with the following URL

jdbc:sqlserver://server\instance;databaseName=mydb

and now I get this error message:

   ERROR DB Connector         0:2        Execute failed: Server "instance" wurde nicht für Überwachung mit TCP/IP konfiguriert.

(I have no idea how to change the error messages from German to English…)

@niederle have you added the portnumber in the URL?

Based on what you showed initially I would expect the URL to be something like:

jdbc:sqlserver://server\instance:1433;databaseName=mydb

True, I forgot the port. With the port number added, I am back to my previous error message…

I don’t know if this is caused by the named instance and whether you should use a slash or backslash in the name (I don’t have these named instances overhere), but I googled a bit and found something using an IP-address instead of using a name.

This would give an URL like this:
jdbc:sqlserver://123.123.123.123:1433;databaseName=mydb;integratedSecurity=true

Can you find the IP-address of this server? Maybe it does work?

Thanks a lot for all your help.
Unfortunately, nothing seems to work. The latest thing I tried was a connection with this URL

jdbc:sqlserver://server;instanceName=instance;databaseName=mydb

I also tried to use the IP address of that server. But then I get again:

ERROR DB Connector         0:2        Execute failed: Server "instance" wurde nicht für Überwachung mit TCP/IP konfiguriert.

I try to find out whether it could be a missing configuration on the server side.
But I would be happy to get any further advice how to solve that problem.

Hello @niederle,

maybe this topic helps:

Br,
Ivan

@niederle : saw this topic which looks similar to yours. Maybe this works for you as well.

I have just learnt how to do this with mine. Hostname is my server, database name is obviously the database name. We just got a new read only access to the database so we would run SQL queries through Tableau, so I used the login details for that. Port was the same as the default.

Hi,
looks promising. Do you know where to find these preference setting for the template?

@ulfalfianita
Maybe you can point me to the right place?

If you find solutions or the root error let me know. I struggle with the error message for ages. My current workaround is always using python to connect.
Thanks

Hi @niederle
I know it is possible to define an URL template for a specific database driver using File > Preferences > KNIME > Databases. When you add, or edit, a driver there is an URL template which one can specify.
BUT … when you install the “KNIME Microsoft JDBC Driver For SQL Server” extension (which you did if I remember correctly), there should be no need to add own additional drivers via the Preferences.
When you use the DB Connector node and you choose this Microsoft driver you will get the default database URL, but once you have replaced it with what is defined in this solution, it will be stored as “last used database URL” and given as option to choose from a next time.

If these default drivers in this extension don’t work for you, but others you have do, you could add them via the Preferences. In the DB Connector node you have to chose this own driver then and provide the database URL you need.

Hope this works for you.

2 Likes

Hello Antje,

please note that the serverName does not start with \\ when you enter it into the Microsoft SQL Server Connector node so in your case the Hostname would be something like g84dbserver\instance instead of \\g84dbserver\instance.

If you are still facing problems please check the following MS docs that describe how the DB server needs to be configured to listen to a certain TCP port (Konfigurieren eines Servers für das Überwachen eines bestimmten TCP-Ports - SQL Server | Microsoft Docs) and how to solve connection problems (Beheben von Verbindungsfehlern mit der SQL Server-Datenbank-Engine - SQL Server | Microsoft Docs).

The JDBC URL has to look like the following:
jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
as described in the MS docs. For the build in driver the URL template is
jdbc:sqlserver://<host>:<port>;databaseName=[database]
So if you need to specify an instance name enter it together with the server name into the Hostname field of the Microsoft SQL Server Connector node:


All JDBC parameter such as integratedSecurity are specified via the JDBC Parameters tab as described here.

If you want to specify the full JDBC connection string yourself you can also use the DB Connector node together with the Microsoft SQL Server driver as JanDuo already mentioned. This would look like this:


with integratedSecurity=true as JDBC Parameters.
Bye
Tobias

2 Likes

Hi @niederle,

Please find the attachment file, I shared it to my colleague, hope it can help you too.
KNIME - Database Connection R1.pptx (569.1 KB)

Regards,
Ulfa

Hello Ulfa,
thanks a lot for sharing this. You do not need to re-register the driver in order to connect to an instance database. The URL template contains tokens that will be replaced by the values entered in the node dialog e.g. the token will be replaced by the value from the Hostname field. So if you use the standard driver with the following template
jdbc:sqlserver://<host>:<port>;databaseName=[database]
and you enter myHost\myInstance into the Hostname field, 1433 into the Port field and myDatabase into the database name field the JDBC URL will look like this:
jdbc:sqlserver://myHost\myInstance:1433;databaseName=myDatabase
Also when using the DB Connector node the URL template is only a preset for the Database URL field which you can alter as you like.
Bye
Tobias

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.