https://community.databricks.com/t5/get-started-discussions/unity-catalog-limited-options-for-connection-objects/m-p/53732#M1862 <P>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.</P><P>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.</P><P>Unfortunately, only a limited number of connection options are available according to the documentation, supported options are:</P><UL><LI>host</LI><LI>port</LI><LI>trustServerCertificate</LI><LI>user</LI><LI>password</LI></UL><P>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.</P><P>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?</P><P>Ultimately, I would like to have my on-premises SQL Server database accessible within Unity Catalog so I can leverage federated query execution.</P> Fri, 24 Nov 2023 08:35:41 GMT MiroFuoli 2023-11-24T08:35:41Z https://community.databricks.com/t5/get-started-discussions/unity-catalog-limited-options-for-connection-objects/m-p/53732#M1862 <P>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.</P><P>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.</P><P>Unfortunately, only a limited number of connection options are available according to the documentation, supported options are:</P><UL><LI>host</LI><LI>port</LI><LI>trustServerCertificate</LI><LI>user</LI><LI>password</LI></UL><P>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.</P><P>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?</P><P>Ultimately, I would like to have my on-premises SQL Server database accessible within Unity Catalog so I can leverage federated query execution.</P> Fri, 24 Nov 2023 08:35:41 GMT https://community.databricks.com/t5/get-started-discussions/unity-catalog-limited-options-for-connection-objects/m-p/53732#M1862 MiroFuoli 2023-11-24T08:35:41Z