Airflow dbt example fails on ODBC driver connect

jawa · ‎11-26-2024

I'm trying out the example https://learn.microsoft.com/en-us/fabric/data-factory/apache-airflow-jobs-dbt-fabric

but I'm stuck at the airflow dbt job failing on

('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')

which according to e.g. https://stackoverflow.com/questions/74708033/error-code-0x2746-10054-when-trying-to-connect-to-sql-s... seems to be related to certificate trust.

Anyone knows how to proceed?

[2024-11-26, 10:23:26 UTC] {subprocess.py:90} INFO - [0m10:23:26  Finished running  in 0 hours 0 minutes and 3.69 seconds (3.69s).
[2024-11-26, 10:23:26 UTC] {subprocess.py:90} INFO - [0m10:23:26  Encountered an error:
[2024-11-26, 10:23:26 UTC] {subprocess.py:90} INFO - Database Error
[2024-11-26, 10:23:26 UTC] {subprocess.py:90} INFO -   ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')
[2024-11-26, 10:23:26 UTC] {subprocess.py:94} INFO - Command exited with return code 2
[2024-11-26, 10:23:26 UTC] {taskinstance.py:1824} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/cosmos/operators/local.py", line 277, in execute
    result = self.build_and_run_cmd(context=context)
  File "/home/airflow/.local/lib/python3.8/site-packages/cosmos/operators/local.py", line 189, in build_and_run_cmd
    return self.run_command(cmd=dbt_cmd, env=env, context=context)
  File "/home/airflow/.local/lib/python3.8/site-packages/cosmos/operators/local.py", line 180, in run_command
    self.exception_handling(result)
  File "/home/airflow/.local/lib/python3.8/site-packages/cosmos/operators/local.py", line 76, in exception_handling
    raise AirflowException(
airflow.exceptions.AirflowException: ('dbt command failed. The command returned a non-zero exit code 2. Details: ', '\x1b[0m10:23:22  Running with dbt=1.5.11', '\x1b[0m10:23:22  Registered adapter: fabric=1.5.0', '\x1b[0m10:23:22  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 347 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups', '\x1b[0m10:23:22', '\x1b[0m10:23:26', '\x1b[0m10:23:26  Finished running  in 0 hours 0 minutes and 3.69 seconds (3.69s).', '\x1b[0m10:23:26  Encountered an error:', 'Database Error', "  ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')")
[2024-11-26, 10:23:26 UTC] {taskinstance.py:1345} INFO - Marking task as FAILED. dag_id=dbt_fabric_dag, task_id=nyc_trip_count.nyc_trip_count_run, execution_date=20241125T000000, start_date=20241126T102313, end_date=20241126T102326
[2024-11-26, 10:23:26 UTC] {standard_task_runner.py:104} ERROR - Failed to execute job 21 for task nyc_trip_count.nyc_trip_count_run (('dbt command failed. The command returned a non-zero exit code 2. Details: ', '\x1b[0m10:23:22  Running with dbt=1.5.11', '\x1b[0m10:23:22  Registered adapter: fabric=1.5.0', '\x1b[0m10:23:22  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 347 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups', '\x1b[0m10:23:22', '\x1b[0m10:23:26', '\x1b[0m10:23:26  Finished running  in 0 hours 0 minutes and 3.69 seconds (3.69s).', '\x1b[0m10:23:26  Encountered an error:', 'Database Error', "  ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)')"); 69)
[2024-11-26, 10:23:26 UTC] {local_task_job_runner.py:225} INFO - Task exited with return code 1
[2024-11-26, 10:23:26 UTC] {taskinstance.py:2653} INFO - 0 downstream tasks scheduled from follow-on schedule check

ssrithar · ‎01-12-2026

Hi @jawa

Since the managed Airflow container uses ODBC Driver 18 by default, and you cannot change the system environment, the only levers you have are:

Adjust the dbt/Fabric connection profile.This tells the driver to trust the server certificate without OS CA validation, which is the only setting you can control.Ensure the connection string includes. TrustServerCertificate=yes.
Modify the connection string parameters
Confirm Which dbt Adapter You Are Using.For Fabric, the dbt adapter typically is:adapter: fabric
Under the hood this uses ODBC to connect to the Fabric SQL endpoint.
Make sure your profiles.yml contains:
adapter: fabric
driver: "ODBC Driver 18 for SQL Server"

That is the only supported way to bypass TLS certificate validation from within the config you control.

If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous · ‎01-03-2025

Stuck on the same issue. Anyone able to resolve and complete the tutorial withour error?

Anonymous · ‎11-26-2024

Hi @jawa

Here are some steps for your reference:

Please update TLS Settings, ensure that both the client and server are using TLS 1.2 or higher. You can update the TLS settings on your client machine to enforce the use of TLS 1.2

Verify that your SQL Server is configured to accept connections using TLS 1.2. You can do this by checking the SQL Server Configuration Manager and ensuring that TLS 1.2 is enabled

Update the ODBC Driver, make sure you have the latest version of the ODBC Driver 18 for SQL Server installed. Sometimes, updating the driver can resolve compatibility issues.

Check for any network issues that might be causing the connection to be forcibly closed. Ensure that there are no firewalls or network policies blocking the connection.

Review your connection string to ensure it includes the correct parameters for TLS. You might need to add `TrustServerCertificate=True` to your connection string if you're using a self-signed certificate.

An existing connection was forcibly closed (OS error 10054) - SQL Server | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jawa · ‎11-27-2024

@AnonymousI can replicate the error using "Entra Service Principal" login option in SSMS which is similar to the way Airflow tries to connect.

So this thread is probably more related to how to access "fabric analytics sql endpoint " using "entra service principal ".

jawa · ‎11-27-2024

Hi @Anonymous

the steps you have propopsed are reasonable. But I think you're missing that both the sending and the recieving end of the ODBC connection is hosted in Fabric and out of my control.

So basically the Linux VM that is hosting the Fabric Airflow job

(see https://learn.microsoft.com/en-us/fabric/data-factory/create-apache-airflow-jobs#create-an-apache-ai... )

probably needs to be configured so that it can handle SQL server ODBC connection with driver version 18, and I can't do that.

I tried to follow the tutorial here https://learn.microsoft.com/en-us/fabric/data-factory/apache-airflow-jobs-dbt-fabric when the error occured