Issue when trying to create a Foreign Catalog to a On Prem SQL Server Instance

[Nick_Pacey]

Hi,

We are creating a lakehouse federated connection to our 2016 On Prem SQL Server.  This has an instance in place, so we only want and need to connect to this instance.  From this connection, we want to create a foreign catalog of a database on the SQL Instance.  Our problem is that no matter what syntax we use, we cannot get the connection to work.  Our code is as follows:

CREATE CONNECTION On_Prem_SQL_Svr TYPE sqlserver

OPTIONS

(

host '192.xxx.xxx.xxx',

port 'xxxxx',

user 'username',

password 'password',

trustServerCertificate 'true'

)

On the host name we have tried every combination of syntax we can think of after the server IP address (\instance \\instance /instance %5Cinstance //instance and some other crazy ones!).  We sometimes get different errors, but none work.  We know connectivity and authentication all work fine, so the problem is either a) syntax or b) you can't create a connection to an On Prem SQL Instance?

We've also tried this through the create federated connection UI and hit the same problem.

Any help out there much appreciated!  Anyone else managed to get this working?

Thanks

Nick

[trueray_3150]

I m facing the same issue, any leads on this please

[Nick_Pacey]

Hi @trueray_3150 

In the end, I had to create the connection in code and not directly reference the instance.  See code below.  This seems to work and give me access to all the databases we have across our different instances (providing security permissions are in place at the SQL end).  This still doesn't feel quite right too me and doesn't quite have the granularity I want, but it works as we can create foreign catalogs to each database and allows us to read and use the data from it quite nicely.

Give this a go, good luck!

CREATE CONNECTION your_connection_name TYPE sqlserver

OPTIONS

(

host '999.999.999.99',

port '9999',

user 'your_sql_user',

password 'your_sql_password',

trustServerCertificate 'true'

);

CREATE FOREIGN CATALOG IF NOT EXISTS foreign_catalog_name_1 USING CONNECTION your_connection_name

OPTIONS (database 'your_sql_db_name');

CREATE FOREIGN CATALOG IF NOT EXISTS foreign_catalog_name_2 USING CONNECTION your_connection_name

OPTIONS (database 'your_sql_db_name_2');

[trueray_3150]

Hi @Nick_Pacey  Thank you I already did that using in the code

jdbc_url = "jdbc:sqlserver://999.99.999.99\\instance:7777;encrypt=true;trustServerCertificate=true;database=mydatabase"

jdbc_username = "myusername"

jdbc_password = "mypassword"

jdbc_driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

wish the create connection syntax has the option for instance  or  the databricks UI provisions that 

[DaveCh]

really appreciate everyone's feedback on this - I took the approach of avoiding the test connection and using only the ip not the secret, whcih included the port. when i created the catalog using a basic script the catolog was created no problem and after spinning up the compute i'm able to view the tables and sample data data no problem - so the connection is definitely good to go now.

However, I cannot select from the foreign tables - just getting a 

really appreciate everyone's feedback on this - I took the approach of avoiding the test connection and using only the ip not the secret, whcih included the port. when i created the catalog using a basic script the catolog was created no problem and i'm able to view the tables and sample data data no problem - so the connection is definitely good to go now.

However, I cannot select from the foreign tables - just getting a jdbc connection failed message