SQL Server (MSSQL)

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

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

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.

Example Connection

{
  "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

{
  "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

{
  "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" }
  ]
}

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.

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+)

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.

Legacy Certificate Support

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

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

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.

Troubleshooting

ODBC driver not found

Verify the Microsoft ODBC Driver 18 is installed and registered:

# List installed ODBC drivers
odbcinst -q -d

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

Install the driver if missing:

# Debian/Ubuntu
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

# RHEL/CentOS
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
ACCEPT_EULA=Y yum install -y msodbcsql18

Using a different driver version

Override the default driver with an environment variable:

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:

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:

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

Connections

​SQL Server (MSSQL)

​Connection Configuration

​URI Format

​Required Fields

​Optional Fields

​Environment Variables

​Example Connection

​Dataset Configuration (data_args)

​Required Fields

​Optional Fields

​table_options Entry

​Complete Example

​Connection

​Dataset

​Encryption Configuration

​Legacy Certificate Support

​Driver and Dependencies

​Troubleshooting

​ODBC driver not found

​Using a different driver version

​Certificate errors (x509)

​Connection timeout

​Windows Authentication

​Slow queries

SQL Server (MSSQL)

Connection Configuration

URI Format

Required Fields

Optional Fields

Environment Variables

Example Connection

Dataset Configuration (data_args)

Required Fields

Optional Fields

table_options Entry

Complete Example

Connection

Dataset

Encryption Configuration

Legacy Certificate Support

Driver and Dependencies

Troubleshooting

ODBC driver not found

Using a different driver version

Certificate errors (x509)

Connection timeout

Windows Authentication

Slow queries