> ## Documentation Index
> Fetch the complete documentation index at: https://knowledge.cloudquant.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server (MSSQL)

> Configure Microsoft SQL Server datasources with ODBC support

# SQL Server (MSSQL)

SQL Server datasources use the ODBC driver and Microsoft ODBC Driver 18 for SQL Server.

<Note>
  See [Supported Data Formats](/datasource-config/supported-formats) for database source categories and schema column types supported on SQL Server connections.
</Note>

## Connection configuration

### URI format

```
mssql://user:password@host:1433/database
```

### Required fields

| Field                 | Type   | Description                  |
| --------------------- | ------ | ---------------------------- |
| `connection_type`     | string | Must be `"mssql"`            |
| `connection_sub_type` | string | Must be `"database"`         |
| `uri`                 | string | SQL Server connection string |
| `server`              | string | Database host address        |
| `database`            | string | Database name                |
| `username`            | string | Database user                |
| `password`            | string | Database password            |

### Optional fields

| Field        | Type   | Default | Description                                             |
| ------------ | ------ | ------- | ------------------------------------------------------- |
| `encryption` | string |         | TLS encryption mode (e.g., `"yes"`, `"no"`, `"strict"`) |

### Environment variables

| Variable            | Description                                                                                     |
| ------------------- | ----------------------------------------------------------------------------------------------- |
| `ODBC_MSSQL_DRIVER` | Override the ODBC driver name (default: `"ODBC Driver 18 for SQL Server"`)                      |
| `GODEBUG`           | Set to `x509negativeserial=1` for servers using older certificates with negative serial numbers |

<Note>
  The `ODBC_MSSQL_DRIVER` environment variable allows you to use a different version of the Microsoft ODBC driver (e.g., Driver 17) without modifying the connection configuration.
</Note>

### Example connection

```json theme={null}
{
  "name": "mssql-market-data",
  "connection_type": "mssql",
  "connection_sub_type": "database",
  "uri": "mssql://libuser:s3cureP@ss@db.example.com:1433/marketdata",
  "server": "db.example.com",
  "database": "marketdata",
  "username": "libuser",
  "password": "s3cureP@ss",
  "encryption": "yes"
}
```

## Dataset configuration (data\_args)

### Required fields

| Field              | Type                   | Description                                                |
| ------------------ | ---------------------- | ---------------------------------------------------------- |
| `table_options`    | list                   | Tables to query (see below)                                |
| `database`         | string                 | Connection reference name                                  |
| `data_dt_column`   | string or list         | Column(s) containing the datetime value                    |
| `data_dt_format`   | bool or string or list | `true` for native datetime auto-detect, or strptime format |
| `data_dt_timezone` | string                 | Timezone of the source data                                |
| `data_key_column`  | string or list         | Column(s) used as the symbol/key for query filtering       |

### Optional fields

| Field           | Type | Default | Description                              |
| --------------- | ---- | ------- | ---------------------------------------- |
| `batch_size`    | int  | `10000` | Number of rows per query batch           |
| `data_dt_nudge` | int  | `0`     | Microsecond offset applied to timestamps |

### table\_options entry

Each entry in `table_options` describes a table to query:

| Field          | Type   | Description                                         |
| -------------- | ------ | --------------------------------------------------- |
| `db`           | string | Database name                                       |
| `table_like`   | string | Table name or pattern                               |
| `token`        | string | Table identifier token                              |
| `zone`         | string | Timezone, default `"America/New_York"`              |
| `dt_mode`      | string | *(Optional)* Datetime mode                          |
| `dt_regex`     | string | *(Optional)* Regex for partitioned tables           |
| `dt_format`    | string | *(Optional)* Datetime format for partitioned tables |
| `nudge_micros` | int    | *(Optional)* Microsecond nudge                      |

## Complete example

### Connection

```json theme={null}
{
  "name": "mssql-trades",
  "connection_type": "mssql",
  "connection_sub_type": "database",
  "uri": "mssql://libuser:s3cureP@ss@sqlserver.example.com:1433/trades_db",
  "server": "sqlserver.example.com",
  "database": "trades_db",
  "username": "libuser",
  "password": "s3cureP@ss",
  "encryption": "yes"
}
```

### Dataset

```json theme={null}
{
  "name": "us-equity-trades",
  "connection": "mssql-trades",
  "data_args": {
    "table_options": [
      {
        "db": "trades_db",
        "table_like": "daily_trades",
        "token": "daily_trades",
        "zone": "America/New_York"
      }
    ],
    "database": "mssql-trades",
    "batch_size": 10000,
    "data_dt_column": "trade_time",
    "data_dt_format": true,
    "data_dt_timezone": "America/New_York",
    "data_dt_nudge": 0,
    "data_key_column": "symbol"
  },
  "schema": [
    { "name": "symbol", "type": "string", "group": "key", "description": "Ticker symbol" },
    { "name": "trade_time", "type": "string", "group": "time", "description": "Trade timestamp" },
    { "name": "price", "type": "double", "group": "value", "description": "Trade price" },
    { "name": "volume", "type": "int64", "group": "value", "description": "Trade volume" },
    { "name": "exchange", "type": "string", "group": "value", "description": "Exchange code" }
  ]
}
```

<Tip>
  Set `data_dt_format` to `true` when using native SQL Server `datetime2` or `datetimeoffset` columns. CloudQuant Data Liberator will auto-detect the datetime type and convert directly without string parsing.
</Tip>

## Encryption configuration

SQL Server connections support several TLS encryption modes via the `encryption` field:

| Value      | Description                                   |
| ---------- | --------------------------------------------- |
| `"yes"`    | Require encrypted connection (recommended)    |
| `"no"`     | Do not encrypt the connection                 |
| `"strict"` | Strict TLS 1.2+ encryption (SQL Server 2022+) |

<Warning>
  When using `encryption: "yes"` with self-signed certificates, you may need to add `TrustServerCertificate=yes` to the connection. For production, always use properly signed certificates.
</Warning>

## Legacy certificate support

For SQL Server instances using older certificates with negative serial numbers:

```bash theme={null}
export GODEBUG=x509negativeserial=1
```

This is common with older SQL Server installations that have not rotated their TLS certificates.

## Driver and dependencies

| Component        | Details                                          |
| ---------------- | ------------------------------------------------ |
| Driver           | `ODBC driver` with ODBC Driver 18 for SQL Server |
| Protocol         | ODBC / TDS                                       |
| Arrow conversion | High-performance ODBC driver                     |
| ODBC required    | Yes (Microsoft ODBC Driver 18)                   |
| Default port     | 1433                                             |

<Note>
  The Microsoft ODBC Driver 18 must be installed on the CloudQuant Data Liberator host. To use a different driver version, set the `ODBC_MSSQL_DRIVER` environment variable.
</Note>

## Troubleshooting

### ODBC driver not found

Verify the Microsoft ODBC Driver 18 is installed and registered:

```bash theme={null}
# List installed ODBC drivers
odbcinst -q -d

# Expected output should include:
# [ODBC Driver 18 for SQL Server]
```

Install the driver if missing:

<CodeGroup>
  ```bash Debian/Ubuntu theme={null}
  curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
  curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
  apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql18
  ```

  ```bash RHEL/CentOS theme={null}
  curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
  ACCEPT_EULA=Y yum install -y msodbcsql18
  ```
</CodeGroup>

### Using a different driver version

Override the default driver with an environment variable:

```bash theme={null}
export ODBC_MSSQL_DRIVER="ODBC Driver 17 for SQL Server"
```

### Certificate errors (x509)

If you see `x509: certificate signed by unknown authority` or negative serial number errors:

```bash theme={null}
export GODEBUG=x509negativeserial=1
```

### Connection timeout

SQL Server may be configured to listen on a non-default port or require a named instance. Verify connectivity:

```bash theme={null}
# Test port connectivity
nc -zv sqlserver.example.com 1433

# For named instances, use the SQL Browser port (1434/UDP) or specify the port directly
```

### Windows authentication

CloudQuant Data Liberator uses SQL Server authentication (username/password). Windows/Kerberos authentication is not supported through this connection type.

### Slow queries

* Increase `batch_size` for large result sets (up to `100000`).
* Ensure the datetime and key columns are indexed.
* For partitioned tables, use `dt_regex` to scope queries to relevant partitions.
* Consider using `datetime2` over `datetime` for better precision and performance.