---
title: SQL Server Driver
---

# SQL Server Driver

`dc3-driver-sqlserver` onboards a Microsoft SQL Server database into IoT DC3 as a data source: acting as a database client, it runs a `SELECT` on each polling cycle and uses the queried value as the reading, and supports writing values into the database via the `UPDATE`/`INSERT` write query configured on the point. After reading this you can set the connection parameters (including encryption options) on a [Device](../introduction/concepts/device), the read/write SQL on each [Point](../introduction/concepts/point), and pinpoint common "can't connect / TLS handshake fails / no value / write fails" problems.

> You are here: a driver that onboards an existing database as a data source. Not all data comes from a fieldbus device—much business data, historical data, and third-party results simply live in a SQL Server table.

## Protocol background

SQL Server is Microsoft's enterprise relational database, shipping since 1989, using T-SQL as its query language and organizing data into tables/rows/columns; it is heavily used in Windows and enterprise IT environments. In IoT scenarios it is often not a "field device" but a hub where data converges: MES/ERP, SCADA front-ends, third-party platforms, and historical archives all tend to drop their results into a SQL Server table for downstream consumption. Onboard such a table as a data source, and the platform can poll its columns into [PointValues](../introduction/concepts/point-value) just like it polls a real device.

Seen through the four-layer IoT reference architecture, a database driver talks to the database over TDS on TCP/IP (default port `1433`); it is the entry point for moving external data into the platform, and its transport sits at the **network layer**—see the [IoT network-layer chapter](../foundations/data-pipeline) for how data enters the platform pipeline. This driver acts as a database client ([Driver](../introduction/concepts/driver) type `DRIVER_CLIENT`), connecting to a SQL Server instance over JDBC (driver class `com.microsoft.sqlserver.jdbc.SQLServerDriver`) and reading/writing values by the SQL configured on each [Point](../introduction/concepts/point). Its communication model is classic **request-response**—the driver, as a client, actively issues queries; the database never pushes, so collection is driven by cron polling. The shared logic for JDBC connections, connection pooling, and SQL execution lives in the abstract base class `AbstractJdbcDriverCustomService` (`dc3-common-sql` module), reused by all four database drivers (MySQL, PostgreSQL, Oracle, SQL Server), each of which only supplies JDBC URL construction and the driver class name.

Two driver-specific concepts that the configuration tables below rely on:

```mermaid
flowchart LR
  Drv["dc3-driver-sqlserver<br/>(JDBC client)"] -->|"readQuery (SELECT)"| DB[("SQL Server instance<br/>business / history tables")]
  Drv -.->|"writeQuery (UPDATE/INSERT, ? placeholder)"| DB
  DB -->|"first row, first column"| PV["PointValue"]
```

- **Read Query**: a `SELECT` configured on the point; the driver runs it on each polling cycle and takes the **first column of the first row** of the result as the point's value.
- **Write Query**: an `UPDATE`/`INSERT` configured on the point, using a single `?` placeholder for the value to write—when a write command fires, the command parameter is bound via prepared-statement parameter binding.

## Attribute configuration

Onboarding a SQL Server database requires filling in [attributes](../introduction/concepts/attribute-config) at three levels: device-level connection parameters (`driver-attribute`), each polled point's read/write SQL (`point-attribute`), and one reserved attribute on the write command (`command-attribute`). The attributes, types, and defaults below are taken from the driver's `application.yml` (`dc3-driver-sqlserver` module).

### Driver attributes (device-level `driver-attribute`)

Driver attributes answer "which database to connect to, which account to use, the query timeout, and whether the connection is encrypted". Fill in one set per SQL Server database on the [Device](../introduction/concepts/device):

| Attribute | code | Type | Default | Remark |
|---|---|---|---|---|
| Host | `host` | STRING | `localhost` | SQL Server host IP or hostname |
| Port | `port` | INT | `1433` | SQL Server port (standard 1433) |
| Database | `database` | STRING | (empty) | SQL Server database name |
| Username | `username` | STRING | `root` | SQL Server username |
| Password | `password` | STRING | (empty) | SQL Server password |
| Query Timeout | `queryTimeout` | INT | `30` | SQL query timeout in seconds |
| Encrypt | `encrypt` | STRING | `false` | Whether to encrypt the connection (TLS) |
| Trust Server Certificate | `trustServerCertificate` | STRING | `true` | Whether to trust the server certificate (skip chain validation) |

The driver builds the JDBC URL from these attributes, in the form `jdbc:sqlserver://host:port;databaseName=...;encrypt=...;trustServerCertificate=...;` (semicolon-delimited, unlike MySQL's `?key=value` form). All five of `host`, `port`, `database`, `username`, and `password` are required—configuration validation (`validate()`) checks each one, and any missing field fails. The driver caches one HikariCP connection pool per device ID (one pool per device, max 5 connections), with the connection timeout set to `queryTimeout × 1000` milliseconds.

::: tip queryTimeout applies to both connecting and the query pace
`queryTimeout` (default 30 seconds) is used as the pool's `connectionTimeout`: failing to acquire a connection, or a connection that stalls beyond this duration, fails. It is separate from the polling interval—if a slow SQL consistently approaches or exceeds it, optimize the SQL or add an index rather than just raising the timeout.
:::

::: warning encrypt and trustServerCertificate are STRING and must be set together
Both attributes are STRING type—fill the string `"true"`/`"false"`, not a boolean; they are spliced verbatim into the JDBC URL. The SQL Server JDBC driver performs a TLS handshake and validates the server certificate when `encrypt=true`; if the server uses a self-signed certificate, validation fails and the connection errors out. When enabling encryption against a self-signed instance, you must also set `trustServerCertificate=true` to skip certificate-chain validation. For plaintext testing on a trusted network, just keep the default `encrypt=false`.
:::

### Point attributes (`point-attribute`)

Point attributes answer "which value to query from this database, and where to write". Fill in the read/write SQL on each polled [Point](../introduction/concepts/point):

| Attribute | code | Type | Default | Remark |
|---|---|---|---|---|
| Read Query | `readQuery` | STRING | (empty) | `SELECT` query for reading the point value |
| Write Query | `writeQuery` | STRING | (empty) | `UPDATE`/`INSERT` using a single `?` placeholder for the written value (bound as a parameter) |

::: tip Read Query takes the first column of the first row
`readQuery` is a plain `SELECT`, and the driver takes the **first column of the first row** of its result (`rs.getObject(1)`) as the point's value—so a single-row, single-column query like `SELECT temperature FROM sensor WHERE id = 1` is the safest form. An empty result set yields `null`. The point's data type ([Point](../introduction/concepts/point) `pointTypeFlag`) decides how that value is parsed. `readQuery` is required on a point (enforced by `validatePoint()`); without it, point validation fails. `writeQuery` is required only when that point is to be written.
:::

### Write command attributes (`command-attribute`)

This attribute can be configured on the write command, but is not consumed by the implementation:

| Attribute | code | Type | Default | Remark |
|---|---|---|---|---|
| Execute Query | `executeQuery` | STRING | (empty) | SQL query to execute for the command |

::: warning executeQuery is currently not consumed by the implementation
Writing a value goes through the **point's `writeQuery`**: `write()` reads the `point-attribute` `writeQuery`, binds the command parameter with `setString(1, value)` into the single `?` placeholder, and executes the `UPDATE`/`INSERT`. The `command-attribute` `executeQuery` is kept only as a configuration item—nothing in the current driver code reads or executes it. There is no separate "run a SQL statement directly by command" path; writing always goes through `writeQuery`. Code is the source of truth.
:::

## Troubleshooting

SQL Server onboarding failures mostly cluster around connection, TLS handshake, account permissions, query targeting, and field types. Work through them in order:

1. **Can't connect (device stays offline)**. First confirm `host:port` is reachable: `telnet <host> 1433` or `nc -vz <host> 1433`. The health check decides online via `conn.isValid(5)` (whether a valid connection can be obtained within 5 seconds); a failed connect or heartbeat reports offline. Common root causes: SQL Server's TCP/IP protocol not enabled, the instance listening only on named pipes, a firewall blocking 1433, or a dynamic port not pinned to 1433.

2. **Encryption / certificate handshake fails**. When `encrypt=true`, the driver performs a TLS handshake and validates the server certificate; against a self-signed instance without `trustServerCertificate=true`, the connect phase reports certificate-chain validation failure. Either set `trustServerCertificate=true` to skip validation, or install a certificate issued by a trusted CA on the server. For plaintext testing on a trusted network, keep `encrypt=false`. Note both are filled as the strings `"true"`/`"false"`.

3. **Connects but is rejected (account / permission)**. Confirm `username`/`password` are correct and the account has read (or write) permission on the target tables. SQL Server supports both SQL authentication and Windows authentication; this driver uses SQL authentication with `username`/`password`—if the instance only allows Windows (integrated) authentication, the SQL account is rejected. A failed connect throws `ConnectorException` and invalidates that device's pool, which is rebuilt on the next cycle.

4. **No value / wrong row returned**. The driver only takes the first column of the first row, so `readQuery` must reliably pinpoint the target row. An empty result set yields `null`; when multiple rows return, only the first is used and may not be the row you meant. Write the `WHERE` primary-key condition fully so you don't pick the wrong row as the table grows.

5. **Value / type mismatch**. The point's `pointTypeFlag` decides how the returned string is parsed. Configuring a text column as a `FLOAT` point, or treating a `datetime`/`bit` column as numeric, can fail parsing. Use `CAST`/`CONVERT` in `readQuery`, or select only the target numeric column, so the returned value matches the point's type.

6. **Write command returns failure**. Writing requires exactly one `?` placeholder in `writeQuery` and a statement targeting a writable table and row. `write()` treats "affected rows > 0" as success—if the `WHERE` condition matches no rows, `executeUpdate()` returns 0 and the write is judged failed. Run the same `UPDATE` by hand in the database first to confirm it hits a row. A failed write throws `WritePointException` and invalidates the pool.

::: warning Write Query uses a `?` placeholder, not `${value}`
When writing, `writeQuery` uses a **single** `?` placeholder for the value (e.g. `UPDATE sensor SET temperature = ? WHERE id = 1`), bound by the driver via `PreparedStatement.setString(1, value)`—this is prepared-statement parameter binding, not string concatenation, so a malicious value cannot alter the statement structure (no SQL injection). Do not concatenate the value into the SQL by hand, and do not use template syntax like `${value}`: it would neither be substituted nor give you injection protection.
:::

## How it lands in IoT DC3

- **`dc3.driver.code`**: `SqlserverDriver` (type `DRIVER_CLIENT`, actively connects to the database and issues queries). This is a stable routing identifier—do not change it casually.
- **Read capability**: ✓ implemented. `read()` executes the point's `readQuery` and takes the first column of the first row as the point value.
- **Write capability**: ✓ implemented. `write()` executes the point's `writeQuery`, binding the written value as a `?` prepared-statement parameter; affected rows > 0 means success.
- **Subscribe/report**: — not supported. SQL Server is request-response; the driver only actively queries/writes and never passively receives pushes. This matches the `✓ / ✓ / —` for SQL Server in the [driver capability matrix](./matrix).
- **Polling interval**: default cron `0/30 * * * * ?` (read once every 30 seconds), configured under `schedule.read` in the driver's `application.yml`; there is also a `custom` schedule with default cron `0/5 * * * * ?` (every 5 seconds), but the base class `schedule()` is an empty implementation and database drivers do not use it.
- **Health/online**: device health check defaults to cron `0/15 * * * * ?` with a lease timeout of `45 seconds`; the verdict relies on `conn.isValid(5)`. See [Device](../introduction/concepts/device) for the online-state mechanism.

::: info Implementation status: available
This driver is a **complete implementation** (not a skeleton). Reading, writing, the health check, the per-device cached HikariCP pool, and pool invalidation-and-rebuild on failure are all in place, reusing the tested `AbstractJdbcDriverCustomService` base class; the SQL Server subclass only customizes JDBC URL construction (including `encrypt`/`trustServerCertificate`), the driver class name, and the default port. The only thing to note is that the `command-attribute` `executeQuery` is reserved but not consumed by the code—writing always goes through the point's `writeQuery` (see the warning above).
:::

### Minimal onboarding example

Onboard the `temperature` column of the `id=1` row in a `sensor` table as a temperature point:

1. Create a [Device](../introduction/concepts/device) with `SQL Server Driver`, and set the driver attributes `host=192.168.1.10`, `port=1433`, `database=iot`, `username=sa`, `password=******` (keep the default `encrypt=false` while testing over a trusted network).
2. Add a temperature [Point](../introduction/concepts/point) (`pointTypeFlag=FLOAT`, `READ_ONLY`) to the [Profile](../introduction/concepts/profile) bound to the device, and set the point attribute `readQuery=SELECT temperature FROM sensor WHERE id = 1`.
3. Start the driver, and within 30 seconds the queried temperature shows up in the [PointValue](../introduction/concepts/point-value).
4. If the point should be writable, add `writeQuery=UPDATE sensor SET temperature = ? WHERE id = 1` to its point attributes and configure a write [Command](../introduction/concepts/command) for it.

::: tip One driver instance can serve multiple databases
A single SQL Server driver process can serve multiple devices: each device connects to its own database per its driver attributes and holds its own connection pool (cached by device ID). When device metadata is deleted/updated, the corresponding pool is closed and rebuilt on demand.
:::

## Further reading

- [Drivers overview](./index) — entry point and categories for all drivers
- [Driver capability matrix](./matrix) — read/write/subscribe at a glance, including the SQL Server row
- [Device Onboarding](../operation/device-onboarding) — a complete onboarding walkthrough
- [Time-Series Data & Stream Processing](../foundations/data-pipeline) — how PointValues are stored, computed, and queried after entering the platform
- [MySQL Driver](./mysql) — another database data source on the same JDBC base class, with the same configuration structure