I am doing a POC using the Community Edition of Dremio. Build 1.1.0-201708121825170680-436784e.
I am trying to connect to a SQL Server instance and I need to use the instance host name “MyHostServer\MyHostInstance”. However, the connection fails. I can connect fine using SQL Server Management Studio and the SQL Auth account that has access.
When I look in the logs I see this exception.
2017-09-02 21:54:08,744 [qtp949483950-105] ERROR c.d.e.store.jdbc.JdbcSchemaFetcher - Error listing datasets for My Data Source
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host MyHostServer, 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.”.
It seems Dremio is ignoring the “\MyHostInstance” portion of the host name and only using “MyHostServer”.
Would you be able to check if TCP/IP connections have been enabled for your MyHostInstance instance? Note that this is disabled initially when setting up a server. You can enable it using the SQL Server Configuration Manager tool, and going to Protocols.
Yes, TCP/IP connections have been enabled on this server. If there weren’t we would be having other issues. jduong, have you been able to connect to a SQL Server instance in the format of [HostServer][Instance] (e.g. SRV4356\SQLHR) from the Community Edition of Dremio?
I tried this out on my end using SQL Server Express edition and was able to connect using the same build of the Community Edition of Dremio.
The error message being thrown from the SQL Server driver most likely just doesn’t report the instance name (since it’s not really part of the ‘host’) so it’s likely there’s another connection problem.
Are you able to connect to your SQL Server instance from a separate JDBC tool such as DBVisualizer?
I am able to connect to my SQL Server instance using DBVisualizer and the jTDS driver from the same server running Dremio using the same account I am using in my Dremio connection. If I try to ping my server in DBVisualizer before connecting, that fails, but actually connecting to the instance works just fine if I connect in DBVisualizer. I wonder if Dremio is pinging the server first and then not trying to connect since the ping fails, even though if it had tried to connect it would have done so successfully on the default port for SQL Server.
Using the IP address makes no difference in behavior. I can try using the Microsoft JDBC driver with DBVisualizer. Is there a particular version I should test?
It’s part of the Community Edition. Under the dremio directory go to:
jars/3rdparty
and get the microsoft-sqljdbc41-4.2.6420.100.jar file. This is the JDBC driver.
So I wrote a simple Java program, compiled it using Java 1.8 (same version Dremio is running under), used the same JDBC driver that comes with Dremio, and I can connect to my SQL Server instance without any errors and pull back data.
My connection string looks like this:
jdbc:sqlserver://[host];instanceName=[instance];databaseName=[db];user=**;password=;
Since I am using the default port of 1433, I don’t explicitly use a port number.
jdbc:sqlserver://[host][instance]:port
It passes username/password in outside of the connection URL.
So the main difference I see between your URL and Dremio’s is that you pass in instanceName using a property, where as Dremio puts it after the host with the backslash (really it just takes the contents of the Server Name field, include the backslash and puts it a the front of the connection URL).
So when you use that driver and you provide an instance name AND a port, the driver ignores the instance name and uses the server name and port thinking it is connecting to the SQL Server instance. (See the “Note” under the “portNumber” section.) If you don’t provide a port but just provide an instance name, then the driver will communicate with the host server and request the port number for the instance and then it will automatically use whatever port the server tells the driver to use which may not be 1433 or whatever was typed in. So I think my issue is I have the port number wrong for the instance and I need to talk to my DBA about what port number the instance is actually listening on for SQL Server connections. That said, it would be nice if a port number were not required and Dremio could simply construct the connection URL without a port if one is not provided. Just let the driver communicate with the SQL Server and figure out port numbers for instances. Right now the SQL Server connection page requires a port, but that should not be necessary for constructing a connection string in Java using the Microsoft SQL Server JDBC driver.
By the way, there are other reasons you don’t want to require a port, especcially if using instances. Sometimes a SQL Server will use dynamic ports for their SQL Server instances. The host server will listen on port 1434 (by default) and when it receives requests to connect to an instance it will send the client to the port the instance happens to be using at that time. So while this required port and the manner of constructing the connection URL may not be a “bug” in Dremio, it is definitely a very undesirable way to construct SQL Server connection URLs. You should do it like Tableau - another great self-service tool like Dremio. It doesn’t require a port to be explicitly provided. Quite often end users simply do not know what the port is and many DBAs are just letting SQL Server run on the default port or they are using the default port of 1434 for the SQL Server Browser service which will tell the driver what port a particular instance is running on and the end user still does not need to provide a port number. Can this be considered for an enhancement request? Meanwhile, I will figure out if a static port is being used for my instances and use that as a workaround. If my DBA set things up using dynamic ports then I’m out of luck and Dremio is broken for me due to how it currently constructs its connection URLs.
Oh, and by the way, I did write a new version of my Java program that constructs connection URLs the way Dremio does it. Thanks for sharing that info jduong. I get the exact same error now in my own app when I follow the Dremio methodology for creating the connection URL. But my argument is Dremio’s code is doing it wrong. You really shouldn’t be requiring a port. The port, especially when using SQL Server instances (since those ports can be assigned dynamically) should really be optional.
Excellent news! Thanks for working with me on this, @jduong. By the way, I just heard back from my DBA. They are running SQL Server Browser service on the default port of 1434. They also are using static ports for each of my SQL Server instances (whew!), so I am in luck and have a temporary workaround for this!! But I do think the better way to solve this is to make the port optional and only include a port in your connection URL if the user explicity lists one. Otherwise, the driver and SQL Server Browser service can take care of all of this auto-magically. Best regards!
Was this issue resolved? I am trying to connect to a SQL Server instance and facing the same issue as reported in this thread. It is not possible to get the static instance port in my case.
Here are the things I tried:
HOST/INSTANCE
using forward slash gives me the following error: Failure while configuring source [SQL SERVER TEST]. Info: Unavailable: The TCP/IP connection to the host HOST/INSTANCE, port 1433 has failed. Error: "null. 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 also tried backslash:
HOST\INSTANCE
For the backslash, I get the following error:
Failure while configuring source [SQL SERVER TEST]. Info: Unavailable: Login failed for user ‘USER’. ClientConnectionId:6e9c462a-918d-45b9-96f0-d01e572b4844
I did try connecting to the database usin DBVisualizer and it works so the setup seems fine.