Iām currently trying to create a Foreign Catalog based on a Connection object of type SQLSERVER. This would allow me to directly access our on-premises MS SQL database from within Azure Databricks using Unity Catalog.
As Iām part of a large organization, I have only limited control over the allowed connection types to this on-premises database. Thus, I'm required to specify the authentication scheme and enable integrated security to establish a successful connection.
Unfortunately, only a limited number of connection options are available according to the documentation, supported options are:
- host
- port
- trustServerCertificate
- user
- password
When I connect to the on-premises database using pyspark with the SQL Server JDBC driver it works, as the driver allows me to pass a connection string where I can specify integrated security to be true and the correct authentication scheme. However, with this approach I can't leverage from federated queries and need to connect to each table in the foreign database separately.
Is there any other approach to creating a Connection object in Unity Catalog that allows me to pass additional configuration options? What are the default properties used when creating a Connection object of type SQLSERVER?
Ultimately, I would like to have my on-premises SQL Server database accessible within Unity Catalog so I can leverage federated query execution.
