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, the read/write SQL on each 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 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 for how data enters the platform pipeline. This driver acts as a database client (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. 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:

• 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 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:

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.

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.

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:

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)

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

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.

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.
• 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 for the online-state mechanism.

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 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 (pointTypeFlag=FLOAT, READ_ONLY) to the 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.
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 for it.

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 — entry point and categories for all drivers
• Driver capability matrix — read/write/subscribe at a glance, including the SQL Server row
• Device Onboarding — a complete onboarding walkthrough
• Time-Series Data & Stream Processing — how PointValues are stored, computed, and queried after entering the platform
• MySQL Driver — another database data source on the same JDBC base class, with the same configuration structure