Microsoft SQL Server (opens in a new tab) is a relational database management system developed by Microsoft.
The Microsoft SQL Server Data Connector enables federated/accelerated SQL queries on data stored in MSSQL databases.
:::warning[Limitations]
geography) are not supported, and columns with these types will be ignored.DATETIME2 and DATETIMEOFFSET columns are mapped to Arrow Timestamp(Nanosecond). Timestamps outside the nanosecond range (approximately years 1677–2262) will return an error. This is an inherent limitation of Arrow's nanosecond timestamp representation.:::
fromThe from field takes the form mssql:database.schema.table where database.schema.table is the fully-qualified table name in the SQL server.
:::info
Unquoted identifiers are normalized to lowercase. To reference a table, schema, or database with mixed-case characters, wrap each case-sensitive part in double quotes: mssql:my_database."MySchema"."MyTable". See Identifier Case Sensitivity (opens in a new tab).
:::
nameThe dataset name. This will be used as the table name within Spice.
Example:
The dataset name cannot be a reserved keyword (opens in a new tab) or any of the following keywords that are reserved by Microsoft SQL Server:
OUTERSETQUALIFYWINDOWENDFORparamsThe data connector supports the following params. Use the secret replacement syntax (opens in a new tab) to load the secret from a secret store, e.g. ${secrets:my_mssql_conn_string}.
| Parameter Name | Description |
|---|---|
mssql_connection_string | The ADO connection string to use to connect to the server. This can be used instead of providing individual connection parameters, and is the only way to set ApplicationIntent — see Availability groups and read-only routing (opens in a new tab). |
mssql_host | The hostname or IP address of the Microsoft SQL Server instance. |
mssql_port | (Optional) The port of the Microsoft SQL Server instance. Default value is 1433. |
mssql_database | (Optional) The name of the database to connect to. The default database (master) will be used if not specified. |
mssql_username | The username for the SQL Server authentication. |
mssql_password | The password for the SQL Server authentication. |
mssql_encrypt | (Optional) Specifies whether encryption is required for the connection. true or require: (default) This mode requires an SSL connection. If a secure connection cannot be established, server will not connect. or : This mode will not attempt to use an SSL connection, even if the server supports it. Only the login procedure is encrypted. |
When a dataset connects to an Always On availability group (opens in a new tab) listener with read intent, the listener answers the login by naming the secondary replica the session belongs on instead of completing it. Spice follows that redirect automatically and re-dials the named replica, so a read-intent dataset lands on a readable secondary rather than the primary.
Read intent is requested through the connection string, so it requires mssql_connection_string — the individual connection parameters (mssql_host, mssql_username, and so on) have no equivalent:
With a connection string of the form:
ApplicationIntent is the only value that enables routing, and ReadOnly is matched exactly — other spellings are treated as read-write intent and are not routed.
The redirect carries the settings the dataset supplied: only the host and port are replaced, so the routed replica is reached with the same credentials, database, encryption level and certificate-trust configuration.
:::info Spice follows up to 3 redirects (4 connection attempts) before reporting the chain as broken. A routing list that points back at the listener redirects indefinitely, so exceeding the limit fails the connection with an error naming the last address it was routed to — check the availability group's read-only routing list when this happens. :::
See the dedicated Performance (opens in a new tab) page for query-pushdown tuning — TopK / ORDER BY ... LIMIT pushdown and its NULL-ordering rules.
Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation (opens in a new tab). Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide (opens in a new tab).
falsedisablemssql_trust_server_certificate | (Optional) Specifies whether the server certificate should be trusted without validation when encryption is enabled. true: The server certificate will not be validated and it is accepted as-is.false: (default) Server certificate will be validated against system's certificate storage. |