Connect DuckDB to Microsoft SQL Server via native TDS (including TLS).
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL mssql FROM community;
LOAD mssql;
Example
INSTALL mssql FROM community;
LOAD mssql;
ATTACH 'Server=localhost,1433;Database=master;User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=yes' AS ms (TYPE mssql);
SELECT * FROM ms.dbo.your_table LIMIT 5;
About mssql
DuckDB connector for Microsoft SQL Server, Azure SQL and Microsoft Fabric over a native TDS 7.4 implementation — no ODBC, JDBC or FreeTDS required. Attach a server as a DuckDB catalog and query, join, and bulk-load it alongside your local data.
Documentation: https://hugr-lab.com/docs/mssql-extension/
Reading. Attached tables come with schema/table catalog integration and a lazily loaded metadata cache. A SELECT the extension can write in T-SQL — joins, aggregates, GROUP BY, window functions, ORDER BY / LIMIT, subqueries, CTEs — runs on SQL Server as one statement (EXPLAIN shows it); the rest streams through projection and filter pushdown and a columnar, batch-decoded result path.
Writing. COPY TO, CTAS and large INSERTs ship over the BCP bulk-load protocol with up to 8 parallel sessions. UPDATE, DELETE, INSERT … SELECT and MERGE run on the server as one statement when they can be written in T-SQL, and through a session staging table otherwise — no primary key required, RETURNING supported. DuckDB transactions map to SQL Server transactions on one pinned connection.
Authentication and security. SQL logins, Azure Entra ID (Service Principal, CLI, Device Code), Kerberos on POSIX, Windows SSPI, DuckDB secrets; TLS with the server certificate verified by default.
Operations. Per-catalog connection pooling, eager ATTACH-time credential validation, named-instance resolution via SQL Server Browser, custom Application Name.
Issues and feature requests: https://github.com/hugr-lab/mssql-extension
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| mssql_azure_auth_test | scalar | Acquires an Azure AD access token with the named azure secret, without connecting to SQL Server, and returns the token abbreviated to its first and last characters and its length, or the error. tenant overrides the secret's tenant for interactive (device code) authentication. | NULL | [mssql_azure_auth_test('my_azure_secret')] |
| mssql_catalog_scan | table | Internal: the scan behind an attached SQL Server table. Registered so DuckDB can copy a plan that holds one; read a table by name instead. | NULL | [SELECT * FROM db.dbo.orders] |
| mssql_dml | table | Internal: the vehicle a pushed UPDATE / DELETE / INSERT … SELECT / MERGE runs through (spec 080). Runs the T-SQL statement once on the attached database – atomically in autocommit, on the transaction's connection inside one – and returns its row count as Count. The remote-pushdown rewriter's target, not an API: its arguments carry no compatibility promise. | NULL | [SELECT * FROM mssql_dml('db', 'UPDATE [dbo].[t] SET [v] = 1 WHERE [id] = @p1', {'p1': 5}, '@p1 int', kind := 'update', target_schema := 'dbo', target_table := 't')] |
| mssql_exec | scalar | Runs a T-SQL statement or batch on an attached SQL Server database and returns the number of rows it affected. | NULL | [mssql_exec('db', 'CREATE TABLE dbo.notes (id int PRIMARY KEY, body nvarchar(200))')] |
| mssql_exec_params | scalar | Runs a parameterised T-SQL statement once per row and returns the rows affected: each field of the params STRUCT becomes an @-parameter of an sp_executesql call, declared from its DuckDB type or by the optional declarations list, so the server compiles the statement once. | NULL | [mssql_exec_params('db', 'INSERT INTO dbo.notes (id, body) VALUES (@id, @body)', {'id': 1, 'body': 'hello'})] |
| mssql_invalidate_cache | scalar | Marks cached metadata of an attached SQL Server database as stale – the whole catalog, one schema, or one table – so it is reloaded from the server on next use; everything else keeps its cache. | NULL | [mssql_invalidate_cache('db', 'dbo', 'orders')] |
| mssql_kerberos_auth_test | scalar | Builds the Kerberos (GSSAPI) token for the SQL Server at host and port (default 1433) without connecting to it, and returns the principal, SPN and token size, or the GSSAPI error. | NULL | [mssql_kerberos_auth_test('sql.example.com', 1433)] |
| mssql_kerberos_auth_test_secret | scalar | mssql_kerberos_auth_test driven by the named MSSQL secret: host, port and the Kerberos options (krb5.conf, keytab, credential cache, realm, SPN override) are read from it, as ATTACH would read them. | NULL | [mssql_kerberos_auth_test_secret('my_mssql_secret')] |
| mssql_pool_stats | table | Returns connection pool statistics – total, idle, active and pinned connections, acquire counts and timeouts, creation failures and the last creation error – for every attached SQL Server database, or for the one named. | NULL | [SELECT * FROM mssql_pool_stats(), SELECT * FROM mssql_pool_stats('db')] |
| mssql_preload_catalog | scalar | Loads the metadata of every schema, table and column of an attached SQL Server database in bulk – or of one schema, given as a second argument – and returns a summary of what it loaded. | NULL | [mssql_preload_catalog('db'), mssql_preload_catalog('db', 'dbo')] |
| mssql_refresh_cache | scalar | Reloads the whole metadata cache – schemas, tables, columns – of an attached SQL Server database from the server, now. | NULL | [mssql_refresh_cache('db')] |
| mssql_scan | table | Runs a T-SQL query on an attached SQL Server database and returns its result set. The shape is described at bind time without running the query; prepared := true compiles it once with sp_prepare instead. | NULL | [SELECT * FROM mssql_scan('db', 'SELECT TOP 10 name, create_date FROM sys.tables')] |
| mssql_scan_params | table | mssql_scan with parameters: each field of the STRUCT becomes an @-parameter of an sp_executesql call, declared from its DuckDB type or by the optional declarations list, so one server plan serves every call. | NULL | [SELECT * FROM mssql_scan_params('db', 'SELECT name FROM sys.objects WHERE object_id > @id', {'id': 100}), SELECT * FROM mssql_scan_params('db', 'SELECT name FROM sys.objects WHERE create_date > @since', {'since': TIMESTAMP '2024-01-01'}, '@since datetime')] |
| mssql_scan_params_unsafe | table | mssql_scan_params with the result's shape given instead of described (columns := {'name': 'TYPE', …}): nothing is asked of the server at bind, and the rows are checked against the shape when the statement runs. | NULL | [SELECT * FROM mssql_scan_params_unsafe('db', 'SELECT name FROM sys.objects WHERE object_id > @id', {'id': 100}, columns := {'name': 'VARCHAR'})] |
| mssql_scan_unsafe | table | mssql_scan with the result's shape given instead of described: columns := {'name': 'TYPE', …} is returned at bind without asking the server (no connection, no describe), and the rows are checked against it when the query runs. The caller vouches for the shape; a stream that does not fit it fails. Types are read as by the catalog: tinyint UTINYINT, datetime2(7) TIMESTAMP_NS, a GEOMETRY column must arrive as WKB (STAsBinary()). | NULL | [SELECT * FROM mssql_scan_unsafe('db', 'SELECT object_id, name FROM sys.objects', columns := {'object_id': 'INTEGER', 'name': 'VARCHAR'})] |
| mssql_version | scalar | Returns the version of the mssql extension. | NULL | [mssql_version()] |
| mssql_winsspi_auth_test | scalar | Windows peer of mssql_kerberos_auth_test: builds the SSPI (Negotiate) token for the SQL Server at host and port (default 1433) without connecting to it, and returns the principal, SPN and token size, or the SSPI error. | NULL | [mssql_winsspi_auth_test('sql.example.com', 1433)] |
| mssql_winsspi_auth_test_spn | scalar | mssql_winsspi_auth_test for an explicit service principal name instead of the MSSQLSvc/ |
NULL | [mssql_winsspi_auth_test_spn('MSSQLSvc/sql.example.com:1433')] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
| type_name | type_size | logical_type | type_category | internal |
|---|---|---|---|---|
| MSSQL_NVARCHAR | 16 | VARCHAR | STRING | true |
| MSSQL_VARCHAR | 16 | VARCHAR | STRING | true |
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| mssql_acquire_timeout | Connection acquire timeout in seconds (0 = fail immediately) | BIGINT | GLOBAL | [] | 30 |
| mssql_attach_validation_timeout | Timeout in seconds for the ATTACH-time credential round trip (0 = inherit mssql_connection_timeout). Spec 047 / US2. | BIGINT | GLOBAL | [] | 0 |
| mssql_browser_timeout_seconds | SQL Server Browser UDP query timeout in seconds for named-instance resolution (default: 3) | BIGINT | GLOBAL | [] | 3 |
| mssql_catalog_cache_ttl | Metadata cache TTL in seconds (0 = manual refresh only) | BIGINT | GLOBAL | [] | 0 |
| mssql_catalog_native_types | Report MSSQL_VARCHAR(n)/MSSQL_NVARCHAR(n) for attached string columns instead of VARCHAR (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_connection_cache | Enable connection pooling and reuse | BOOLEAN | GLOBAL | [] | true |
| mssql_connection_limit | Maximum connections per attached mssql database | BIGINT | GLOBAL | [] | 64 |
| mssql_connection_timeout | TCP connection and login timeout in seconds (0 = the default, 30) | BIGINT | GLOBAL | [] | 30 |
| mssql_convert_varchar_max | Convert VARCHAR(MAX) to NVARCHAR(MAX) in table scans for UTF-8 compatibility (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_copy_flush_rows | Rows per bulk-load batch sent to SQL Server (default: 102400 — SQL Server's threshold for writing compressed columnstore rowgroups directly; 0 = one batch at the end, high memory) | BIGINT | GLOBAL | [] | 102400 |
| mssql_copy_parallel_writers | Concurrent bulk-load connections a single COPY/CTAS may open (default: 0 = derive from DuckDB's thread count, capped at 8). 1 disables parallel loading. Ignored inside an explicit transaction, where the connection is pinned | BIGINT | GLOBAL | [] | 0 |
| mssql_copy_tablock | TABLOCK hint for COPY/BCP: 'auto' (by target shape — heap on, anything clustered off), 'true', or 'false' | VARCHAR | GLOBAL | [] | auto |
| mssql_ctas_drop_on_failure | Drop table if CTAS insert phase fails (default: false, table remains for debugging) | BOOLEAN | GLOBAL | [] | false |
| mssql_ctas_text_type | Type given to an unannotated VARCHAR column by CTAS and COPY: NVARCHAR (Unicode, default) or VARCHAR (single-byte, needs a UTF-8 collation) | VARCHAR | GLOBAL | [] | NVARCHAR |
| mssql_ctas_use_bcp | Use BCP protocol for CTAS data transfer (default: true, 2-10x faster than INSERT) | BOOLEAN | GLOBAL | [] | true |
| mssql_default_string_length | Length for an unannotated VARCHAR column created by CTAS/COPY (0 = MAX, the default) | BIGINT | GLOBAL | [] | 0 |
| mssql_default_table_kind | Shape of a table created by CTAS/COPY: HEAP (default) or COLUMNSTORE | VARCHAR | GLOBAL | [] | HEAP |
| mssql_dml_batch_size | Maximum rows per UPDATE/DELETE batch (default: 500, affects parameter count) | BIGINT | GLOBAL | [] | 500 |
| mssql_dml_max_parameters | Maximum parameters per UPDATE/DELETE statement (SQL Server limit ~2100) | BIGINT | GLOBAL | [] | 2000 |
| mssql_dml_pushdown | Send an UPDATE / DELETE the remote-pushdown rewriter can render to SQL Server as one statement (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_dml_stage_threshold | Rows up to which an UPDATE/DELETE on a keyed table is sent as VALUES-join statements; past it the rows go through a #stage table (0 = always stage, default: 1000) | BIGINT | GLOBAL | [] | 1000 |
| mssql_enable_statistics | Enable statistics collection from SQL Server for query optimizer | BOOLEAN | GLOBAL | [] | true |
| mssql_exec_invalidate_cache | Invalidate the catalog cache after DDL executed via mssql_exec() (CREATE/DROP/ALTER/TRUNCATE/RENAME/EXEC) | BOOLEAN | GLOBAL | [] | false |
| mssql_idle_timeout | Idle connection timeout in seconds (0 = no timeout) | BIGINT | GLOBAL | [] | 300 |
| mssql_insert_batch_size | Maximum rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] | 1000 |
| mssql_insert_bcp_threshold | Rows up to which an INSERT is sent as statements rather than a bulk load | BIGINT | GLOBAL | [] | 1000 |
| mssql_insert_max_rows_per_statement | Hard cap on rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] | 1000 |
| mssql_insert_max_sql_bytes | Maximum SQL statement size in bytes | BIGINT | GLOBAL | [] | 8388608 |
| mssql_insert_use_bcp | Load INSERT rows through the BCP protocol above mssql_insert_bcp_threshold rows (RETURNING and an explicit identity column always use statements) | BOOLEAN | GLOBAL | [] | true |
| mssql_login7_max_packet | TEST-ONLY: max LOGIN7 TDS packet size in bytes for integrated auth (0 = default 4096) | BIGINT | GLOBAL | [] | 0 |
| mssql_metadata_timeout | Metadata query timeout in seconds (default: 300, 0 = no timeout). Increase for very large catalogs | BIGINT | GLOBAL | [] | 300 |
| mssql_min_connections | Minimum connections to maintain per context | BIGINT | GLOBAL | [] | 0 |
| mssql_named_instance_resolution | Enable SQL Server Browser (UDP 1434) resolution of host\instance connection strings | BOOLEAN | GLOBAL | [] | true |
| mssql_order_pushdown | Enable ORDER BY pushdown to SQL Server (default: false) | BOOLEAN | GLOBAL | [] | false |
| mssql_pushdown_join_rows_threshold | Rows (cached count) from which a remote-pushdown join of uncertain gain stays with the catalog scan (0 = always push) | BIGINT | GLOBAL | [] | 1000000 |
| mssql_pushdown_min_rows | Rows (cached counts, summed over the tables a statement reads) below which remote pushdown leaves it to the catalog scans (0 = no floor) | BIGINT | GLOBAL | [] | 0 |
| mssql_query_timeout | Query execution timeout in seconds (0 = no timeout, default: 30) | BIGINT | GLOBAL | [] | 30 |
| mssql_remote_pushdown | Push whole SELECT statements to SQL Server through DuckDB's remote-pushdown rewriter; read at ATTACH (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_reset_connection | Reset session state (temp tables, SET options, open transactions) when a connection returns to the pool; false hands that state to the user | BOOLEAN | GLOBAL | [] | true |
| mssql_scan_parameterize_filters | Send pushed filter constants as sp_executesql parameters (one plan per shape) | BOOLEAN | GLOBAL | [] | true |
| mssql_statistics_cache_ttl_seconds | Statistics cache TTL in seconds | BIGINT | GLOBAL | [] | 300 |
| mssql_statistics_level | Statistics detail level: 0=row count, 1=+histogram min/max, 2=+NDV | BIGINT | GLOBAL | [] | 0 |
| mssql_statistics_use_dbcc | Allow DBCC SHOW_STATISTICS for column statistics (requires permissions) | BOOLEAN | GLOBAL | [] | false |
| mssql_tds_packet_size | TDS frame size in bytes requested at login, clamped to [512, 32767] (default: 16384) | BIGINT | GLOBAL | [] | 16384 |
| mssql_test_dml_platform | TEST ONLY: the platform whose DML forms an ATTACH uses ('' = by host, 'sqlserver', 'fabric', 'synapse'); read at ATTACH | VARCHAR | GLOBAL | [] | |
| mssql_test_fail_metadata_after_rows | TEST ONLY: make a metadata query fail after this many rows (0 = off) | BIGINT | GLOBAL | [] | 0 |
| mssql_test_fail_parse_after_tokens | TEST ONLY: put a DML response parser into Error after this many tokens (0 = off) | BIGINT | GLOBAL | [] | 0 |
| mssql_test_force_intersect_join_form | TEST ONLY: match a keyless UPDATE / DELETE through EXISTS (… INTERSECT …) even where the server has IS NOT DISTINCT FROM | BOOLEAN | GLOBAL | [] | false |
| mssql_utf8_collation | Collation given to VARCHAR columns created by CTAS when the server supports UTF-8 (default: Latin1_General_100_BIN2_UTF8, matching Fabric; empty inherits the database default) | VARCHAR | GLOBAL | [] | Latin1_General_100_BIN2_UTF8 |
| mssql_utf8_support | Advertise the UTF8SUPPORT feature in LOGIN7 so UTF-8 collation columns arrive as UTF-8 instead of UTF-16 (default: true) | BOOLEAN | GLOBAL | [] | true |
| mssql_warn_non_utf8_collation | Log a warning when a CHAR/VARCHAR/TEXT column arrives with a non-UTF-8 collation, since its bytes are returned verbatim into a DuckDB VARCHAR (default: true) | BOOLEAN | GLOBAL | [] | true |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |