Connect DuckDB to Microsoft SQL Server via native TDS (including TLS).
Maintainer(s):
VGSML
Installing and Loading
INSTALL mssql FROM community;
LOAD mssql;
Example
INSTALL mssql FROM community;
LOAD mssql;
ATTACH 'mssql://sa:YourStrong!Passw0rd@localhost:1433?database=master&use_encrypt=true' AS ms;
SELECT * FROM ms.dbo.table LIMIT 5;
About mssql
The mssql extension provides a DuckDB connector for Microsoft SQL Server using the native TDS protocol.
The extension is experimental and may not cover all edge cases. Please report any issues on the GitHub repository.
Features:
- Native TDS protocol (no ODBC/JDBC required)
- Azure Entra ID authentication (Service Principal, CLI, Device Code, Environment Variables)
- Kerberos integrated authentication on POSIX (kinit / keytab / raw credentials) and Windows SSPI (current logon session via secur32.dll)
- Azure SQL Database and Microsoft Fabric support
- Lazy metadata loading for fast connections
- Schema, table, and view catalog integration
- Projection, filter, and ORDER BY pushdown
- TLS/SSL encrypted connections (encrypt parameter)
- INSERT support with RETURNING clause
- UPDATE and DELETE support (requires primary key)
- Per-catalog connection pooling with configurable limits
- Eager ATTACH-time credential validation (opt-out via lazy_validation true)
- Custom Application Name propagated to LOGIN7 program_name
- DuckDB secret management for credentials
- ANSI-compliant connections for DDL commands
- COPY TO for bulk data transfer via BCP protocol (SIMD-accelerated UTF-16 transcoding via simdutf)
- CREATE TABLE AS SELECT (CTAS) support
- Named SQL Server instance resolution via SQL Server Browser (UDP 1434)
- XML data type read/write support
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| mssql_azure_auth_test | scalar | NULL | NULL | |
| mssql_close | scalar | NULL | NULL | |
| mssql_close_all | scalar | NULL | NULL | |
| mssql_exec | scalar | NULL | NULL | |
| mssql_kerberos_auth_test | scalar | NULL | NULL | |
| mssql_kerberos_auth_test_secret | scalar | NULL | NULL | |
| mssql_open | scalar | NULL | NULL | |
| mssql_ping | scalar | NULL | NULL | |
| mssql_pool_stats | table | NULL | NULL | |
| mssql_preload_catalog | scalar | NULL | NULL | |
| mssql_refresh_cache | scalar | NULL | NULL | |
| mssql_scan | table | NULL | NULL | |
| mssql_version | scalar | NULL | NULL | |
| mssql_winsspi_auth_test | scalar | NULL | NULL | |
| mssql_winsspi_auth_test_spn | scalar | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| mssql_acquire_timeout | Connection acquire timeout in seconds (0 = fail immediately) | BIGINT | GLOBAL | [] |
| mssql_attach_validation_timeout | Timeout in seconds for the ATTACH-time credential round trip (0 = inherit mssql_connection_timeout). Spec 047 / US2. | BIGINT | GLOBAL | [] |
| mssql_browser_timeout_seconds | SQL Server Browser UDP query timeout in seconds for named-instance resolution (default: 3) | BIGINT | GLOBAL | [] |
| mssql_catalog_cache_ttl | Metadata cache TTL in seconds (0 = manual refresh only) | BIGINT | GLOBAL | [] |
| mssql_connection_cache | Enable connection pooling and reuse | BOOLEAN | GLOBAL | [] |
| mssql_connection_limit | Maximum connections per attached mssql database | BIGINT | GLOBAL | [] |
| mssql_connection_timeout | TCP connection timeout in seconds | BIGINT | GLOBAL | [] |
| mssql_convert_varchar_max | Convert VARCHAR(MAX) to NVARCHAR(MAX) in table scans for UTF-8 compatibility (default: true) | BOOLEAN | GLOBAL | [] |
| mssql_copy_flush_rows | Rows before flushing to SQL Server during COPY (default: 100000, 0=no flush until end - high memory) | BIGINT | GLOBAL | [] |
| mssql_copy_tablock | Use TABLOCK hint for COPY/BCP operations (default: false, set true for 15-30% performance) | BOOLEAN | GLOBAL | [] |
| mssql_ctas_drop_on_failure | Drop table if CTAS insert phase fails (default: false, table remains for debugging) | BOOLEAN | GLOBAL | [] |
| mssql_ctas_text_type | Text column type for CTAS: NVARCHAR (Unicode, default) or VARCHAR (collation-dependent) | VARCHAR | GLOBAL | [] |
| mssql_ctas_use_bcp | Use BCP protocol for CTAS data transfer (default: true, 2-10x faster than INSERT) | BOOLEAN | GLOBAL | [] |
| mssql_dml_batch_size | Maximum rows per UPDATE/DELETE batch (default: 500, affects parameter count) | BIGINT | GLOBAL | [] |
| mssql_dml_max_parameters | Maximum parameters per UPDATE/DELETE statement (SQL Server limit ~2100) | BIGINT | GLOBAL | [] |
| mssql_dml_use_prepared | Use prepared statements for UPDATE/DELETE operations | BOOLEAN | GLOBAL | [] |
| mssql_enable_statistics | Enable statistics collection from SQL Server for query optimizer | BOOLEAN | GLOBAL | [] |
| mssql_idle_timeout | Idle connection timeout in seconds (0 = no timeout) | BIGINT | GLOBAL | [] |
| mssql_insert_batch_size | Maximum rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] |
| mssql_insert_max_rows_per_statement | Hard cap on rows per INSERT statement (SQL Server limit: 1000) | BIGINT | GLOBAL | [] |
| mssql_insert_max_sql_bytes | Maximum SQL statement size in bytes | BIGINT | GLOBAL | [] |
| mssql_insert_use_returning_output | Use OUTPUT INSERTED for RETURNING clause | BOOLEAN | GLOBAL | [] |
| mssql_login7_max_packet | TEST-ONLY: max LOGIN7 TDS packet size in bytes for integrated auth (0 = default 4096) | BIGINT | GLOBAL | [] |
| mssql_metadata_timeout | Metadata query timeout in seconds (default: 300, 0 = no timeout). Increase for very large catalogs | BIGINT | GLOBAL | [] |
| mssql_min_connections | Minimum connections to maintain per context | BIGINT | GLOBAL | [] |
| mssql_named_instance_resolution | Enable SQL Server Browser (UDP 1434) resolution of host\instance connection strings | BOOLEAN | GLOBAL | [] |
| mssql_order_pushdown | Enable ORDER BY pushdown to SQL Server (default: false) | BOOLEAN | GLOBAL | [] |
| mssql_query_timeout | Query execution timeout in seconds (0 = no timeout, default: 30) | BIGINT | GLOBAL | [] |
| mssql_statistics_cache_ttl_seconds | Statistics cache TTL in seconds | BIGINT | GLOBAL | [] |
| mssql_statistics_level | Statistics detail level: 0=row count, 1=+histogram min/max, 2=+NDV | BIGINT | GLOBAL | [] |
| mssql_statistics_use_dbcc | Allow DBCC SHOW_STATISTICS for column statistics (requires permissions) | BOOLEAN | GLOBAL | [] |