cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Nick_Pacey
New Contributor III

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
 

 

3 REPLIES 3

trueray_3150
New Contributor II

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

Nick_Pacey
New Contributor III

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
New Contributor II

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