Connect Azure databricks to SQL Server with instance name

Question

I am trying to connect databricks to an on premise SQL Server with a non-default instance name. Before I raise a ticket to investigate it is a firewall issue I wanted to see if my syntax is correct first.

DROP TABLE IF EXISTS sqlserver_table;

CREATE TABLE sqlserver_table
USING sqlserver
OPTIONS (
  dbtable 'dbo.tablename',
  host 'hostname\INSTANCE01',
  port '56883',
  database 'databasename',
  user 'username',
  password 'password'
);

This is the code I'm using in databricks. When I try to get this to run I get the error message.

Error in SQL statement: SQLServerException: The TCP/IP connection to the host hostnameINSTANCE01, port 56883 has failed

Note that the server is missing the \ between the server name and the instance name.

So I thought maybe the \ needed to be escaped but when I run the above code with servername\instancename I get the following error.

Error in SQL statement: URISyntaxException: Illegal character in opaque part at index 32: jdbc:sqlserver://servername\INSTANCE01:56883

Any idea what the correct syntax is to connect databricks to a named instance? I've done some searching but haven't found anything with an instance as of yet.

Answer 1

As siggemannen pointed out in the comment one does not need to provide the instance name when providing the port number. So the corrected code would be.

DROP TABLE IF EXISTS sqlserver_table;

CREATE TABLE sqlserver_table
USING sqlserver
OPTIONS (
  dbtable 'dbo.tablename',
  host 'hostname',
  port '56883',
  database 'databasename',
  user 'username',
  password 'password'
);