# 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
```