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)
I’ve installed the Microsoft JDBC Driver For SQL Server
I’ve selected it in the node
Port: 1.433 (default)
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?
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:
With a Database URL like this jdbc:sqlserver://mysqlserver:1433;databaseName=mydatabase;integratedSecurity=true
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://126.96.36.199:1433;databaseName=mydb;integratedSecurity=true
Can you find the IP-address of this server? Maybe it does work?
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.
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.
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.
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:
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:
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.