Unity Catalog - Limited Options for Connection Objects

[MiroFuoli]

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? 

Ultimately, I would like to have my on-premises SQL Server database accessible within Unity Catalog so I can leverage federated query execution.

[Kaniz_Fatma]

Hi @MiroFuoli, Firstly, ensuring you’re using the correct versions of dbt-core and dbt-databricks is important. There have been instances where specific versions of these packages have caused issues. You might want to try using an older version, such as dbt-core<=1.3.1 and dbt-databricks<=1.3.11.

Secondly, according to the Azure Databricks documentation, you can run your dbt Core projects as a t.... This allows you to automate your dbt tasks, schedule workflows that include dbt tasks, monitor your .... It also automatically archives the artefacts from job runs, including logs, results, manifests, and ....

I hope this helps! Let me know if you have any other questions.

[MiroFuoli]

Hi @Kaniz_Fatma,

Thank you for your answer. My question is not related to dbt, but rather Unity Catalog.

When creating a Connection object in Unity Catalog the default authentication method is Basic Authentication. I would like to know if I can override this setting. Specifically, I would like to know how I can specify Integrated Authentication when creating said Connection object in Unity Catalog?

Note: Nowadays, most corporate on-premises database systems reject connection attempts using Basic Authentication, as it is considered insecure. Integrated Authentication, however, relies on Kerberos technology, with strong credential encryption.

[Kaniz_Fatma]

Hi @MiroFuoli, when creating a Connection object in Unity Catalog, the default authentication method is indeed Basi.... However, it seems that the options for connection objects in the Unity Catalog are currently limited. The supported options are host, port, trustServerCertificate, user, and password.

Unfortunately, it appears that there might not be a straightforward way to specify Integrated Authen....

This could be a limitation of the current version of Unity Catalog.

However, Unity Catalog can be configured to use an Azure managed identity to access storage containe.... This might provide an alternative way to handle authentication.