Add SQL Server as catalog db

[mattiasthalen]

Most of our clients are using SQL Server and asking them to get a Postgres server for DuckLake would probably be met with resistance, even if that's the end goal.

It would be nice however if we could use SQL Server as the catalog backend.

[tintamarre]

The first step would be to develop a DuckDB extension specifically for SQL Server (currently not available).

https://duckdb.org/community_extensions/core_extensions https://duckdb.org/community_extensions/list_of_extensions.html

[Tishj]

Or use a generic protocol, perhaps ODBC which is supported through a community extension (https://duckdb.org/community_extensions/extensions/nanodbc.html) ?

[csubhodeep]

+1 for this or if there is anyway to make it work with the Fabric ecosystem.

[keen85]

nanodbc is already able to read from MS SQL / Azure SQL / SQL Server via ODBC as of today. @Hugoberry, it seems that you are the main Contributor for nanodbc. Are there any plans to support write operations?

[jojayaro]

Nanodbc doesn’t work in Fabric notebooks or systems without odbc.

It would be cool to port the official Go driver that meets TDS specs, not sure if there is a C driver that might work for this.

[keen85]

@jojayaro Fabric Spark Runtime come with MSSQL ODBC driver preinstalled ;) https://github.com/microsoft/synapse-spark-runtime/blob/main/Fabric/Runtime%201.3%20(Spark%203.5)/Components.json#L162C1-L163C28 I'd expect that this is also true for Fabric Python runtime.

But I agree; it would be much nicer if a dedicated MSSQL extension would come "with batteries included" (bring everything that is needed to connect to MSSQL out-of-the-box).

[mattiasthalen]

Can confirm that Python notebooks in Fabric has odbc, I'm using it with SQLMesh.

[Hugoberry]

nanodbc is already able to read from MS SQL / Azure SQL / SQL Server via ODBC as of today. @Hugoberry, it seems that you are the main Contributor for nanodbc. Are there any plans to support write operations?

I didn't plan for DuckLake at the time of writing the extension. There is nothing in the interface to stop ODBC from doing write operations; however, the current version of nanodbc extension isn't built for linux_amd64. I plan to fix this very soon. https://github.com/Hugoberry/duckdb-nanodbc-extension/issues/14

[jojayaro]

Can confirm that Python notebooks in Fabric has odbc, I'm using it with SQLMesh.

Do you mind sharing how, I was not able to get it working.

[mattiasthalen]

Can confirm that Python notebooks in Fabric has odbc, I'm using it with SQLMesh.

Do you mind sharing how, I was not able to get it working.

Sure thing:

import os
import pyodbc

server = os.getenv('SQL_ENDPOINT')
database = os.getenv('WAREHOUSE_NAME')
client_id = os.getenv('CLIENT_ID')
client_secret = os.getenv('CLIENT_SECRET')

connection_string = (
    f"DRIVER={{ODBC Driver 18 for SQL Server}};"
    f"SERVER={server};"
    f"DATABASE={database};"
    f"Authentication=ActiveDirectoryServicePrincipal;"
    f"UID={client_id};"
    f"PWD={client_secret};"
    f"Encrypt=yes;"
    f"TrustServerCertificate=no;"
)

with pyodbc.connect(connection_string) as conn:
    print("✓ Connection successful!")

    cursor = conn.cursor()
    cursor.execute("SELECT @@VERSION as version")
    row = cursor.fetchone()
    
    print(f"✓ Query executed successfully!")
    print(f"  SQL Server Version: {row.version}")

✓ Connection successful!
✓ Query executed successfully!
  SQL Server Version: Microsoft Azure SQL Data Warehouse (RTM) - 12.0.2000.8 
	Jul 10 2025 08:27:10 
	Copyright (C) 2025 Microsoft Corporation