Forum Discussion

AKemper's avatar
AKemper
Frequent Visitor
11 months ago

Databricks Connection Fails Only in Fabric Pipeline

Hello all!   I am trying to create a Pipeline to get data from my Azure Databricks environment. I have an existing connection that I have used with Dataflows and Semantic Models before that works w...
Need Help
AKemper's avatar
AKemper
Frequent Visitor
11 months ago

BeaBF I am getting a different error now regarding ODBC drivers.

 

Error message:

An exception occurred: ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

 

I am seeing conflicting comments online about how to resolve this so any thoughts you have regarding this would be appreciated.

 

Thanks!

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
11 months ago

AKemper have you still the problem? 

You’re no longer hitting Databricks-side auth issues; you’re now hitting a driver problem on the ADF/Synapse side.
Use the JDBC connector instead of ODBC

(Recommended, no driver installation needed)

  • In ADF Linked Services, choose JDBC (not ODBC).

  • Connection string:

     
    jdbc:spark://<ServerHostname>:443/default;transportMode=http;ssl=1;httpPath=<HTTPPath>;AuthMech=3

  • Username: token

  • Password: <your Databricks PAT>

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

  • AKemper's avatar
    AKemper
    Frequent Visitor
    11 months ago

    Hi BeaBF,

    I don't have JDBC as a connection option only ODBC.

     

    • BeaBF's avatar
      BeaBF
      Icon for Super User rankSuper User
      11 months ago

      AKemper 

      ODBC in ADF only works if:

      1. You install the Databricks ODBC driver, and

      2. You run the pipeline on a self-hosted IR where that driver is installed.

      Here the steps:

       

      • Set up a self-hosted Integration Runtime (SHIR)

      • Install the Databricks ODBC driver on that VM

      • Create a System DSN (Windows ODBC Data Source Administrator)

        • Configure it with:

          • Host(s) = your Databricks SQL hostname (e.g. adb-12345.6.azuredatabricks.net)

          • Port = 443

          • HTTP Path = your SQL warehouse path (from Databricks)

          • Authentication = Token

          • Token = your PAT

      • Configure your Linked Service in ADF

        • Type = ODBC

        • Runtime = your self-hosted IR (not auto-resolve / default)

        • Connection string = reference the DSN you created, e.g.:

           
          Driver={Simba Spark ODBC Driver}; Host=<your-databricks-sql-hostname>; Port=443; HTTPPath=<your-sql-http-path>; AuthMech=3; UID=token; PWD=<your-pat>; SSL=1; ThriftTransport=2;

          (note: depending on driver version, some params may be optional)

      • Test connection

        • Should succeed now, because the driver is present on the SHIR.

      if you cannot use JDBC and you cannot install a self-hosted IR (so ODBC won’t work, because Microsoft-hosted IR has no Databricks ODBC driver), then unfortunately you’ve hit a real limitation in ADF/Synapse.

      The best workaround is to use Dataflows instead of Copy Data

      • You already said your existing connection works fine in Dataflows.

      • If your main goal is just to move data, you could orchestrate with pipelines but perform the copy with Mapping Dataflows instead of Copy Data.

      • Dataflows do support Databricks SQL directly.

       

      BBF


      💡 Did I answer your question? Mark my post as a solution!

      👍 Kudos are appreciated

      🔥 Proud to be a Super User!