# MSSQL

[Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server) 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.

{% hint style="warning" %}
**Limitations**

1. The connector supports SQL Server authentication (SQL Login and Password) only.
2. Spatial types (`geography`) are not supported, and columns with these types will be ignored.
   {% endhint %}

```yaml
datasets:
  - from: mssql:path.to.my_dataset
    name: my_dataset
    params:
      mssql_connection_string: ${secrets:mssql_connection_string}
```

## Configuration

### `from`

The `from` field takes the form `mssql:database.schema.table` where `database.schema.table` is the fully-qualified table name in the SQL server.

{% hint style="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](/building-blocks/data-connectors.md#identifier-case-sensitivity-and-quoting).
{% endhint %}

### `name`

The dataset name. This will be used as the table name within Spice.

Example:

```yaml
datasets:
  - from: mssql:path.to.my_dataset
    name: cool_dataset
    params: ...
```

```sql
SELECT COUNT(*) FROM cool_dataset;
```

```shell
+----------+
| count(*) |
+----------+
| 6001215  |
+----------+
```

### `params`

The data connector supports the following `params`. Use the [secret replacement syntax](https://github.com/spicehq/docs/blob/trunk/building-blocks/secret-stores/index.md) 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.                                                                                                                                                                                                                                                                    |
| `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`                  | <p>(Optional) Specifies whether encryption is required for the connection.<br></p><ul><li><code>true</code>: (default) This mode requires an SSL connection. If a secure connection cannot be established, server will not connect.</li><li><code>false</code>: This mode will not attempt to use an SSL connection, even if the server supports it. Only the login procedure is encrypted.</li></ul> |
| `mssql_trust_server_certificate` | <p>(Optional) Specifies whether the server certificate should be trusted without validation when encryption is enabled.<br></p><ul><li><code>true</code>: The server certificate will not be validated and it is accepted as-is.</li><li><code>false</code>: (default) Server certificate will be validated against system's certificate storage.</li></ul>                                           |

### Example

```yaml
datasets:
  - from: mssql:SalesLT.Customer
    name: customer
    params:
      mssql_host: mssql-host.database.windows.net
      mssql_database: my_catalog
      mssql_username: my_user
      mssql_password: ${secrets:mssql_pass}
      mssql_encrypt: true
      mssql_trust_server_certificate: true
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.spice.ai/building-blocks/data-connectors/mssql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.