--- title: Microsoft SQL Server slug: litmusedge/product-features/integration/integration-connectors/sql/microsoft-sql-server description: Review parameters for the Microsoft SQL Server connector. docTags: createdAt: 2026-10-15T16:17:12.270Z --- The Microsoft SQL Server connector writes Litmus Edge (LE) DeviceHub messages into a table in a Microsoft SQL Server database, including Azure SQL. The connector writes rows in batches, either as multi-row `INSERT` statements or with TDS bulk copy. The connector also supports Execute Command for ad-hoc SQL queries. ## Supported communication - Outbound (write data from LE to the database): Yes - Inbound (read data from the database to LE): No - Execute Command (ad-hoc SQL query): Yes ## Compatibility The connector supports the following products. Other products and versions might work. | Product | Version | | -------------------------- | --------------------------------- | | Microsoft SQL Server | 2017 and later. Validated on 2019 | | Azure SQL Database | `-` | | Azure SQL Managed Instance | `-` | ## Connector configuration | Field | Type | Description | Required | Default | | ---------------------- | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | -------- | | Name | String | Name of this connector instance. | Yes | `-` | | Hostname | String | DNS name or IP address of the SQL Server host. The host must be reachable from the LE system. Length is `3` to `255` characters. | Yes | `-` | | Port | Integer | Port of the SQL Server host. Range is `1` to `65535`. | Yes | `1433` | | Encrypt | Dropdown | TLS encryption mode: `disable`, `false`, `true`, or `strict`. | No | `false` | | CA Certificate | String (Secret) | CA certificate that the connector uses to validate the server. You can also add it to the system-wide certificate list. Applies only when **Encrypt** is `true` or `strict`. | No | `-` | | Username | String | Database user that the connector authenticates as. | Yes | `-` | | Password | String (Secret) | Password for the database user. | No | `-` | | Database | String | Name of the target database. | Yes | `-` | | Schema | String | Schema that holds the target table. | No | `dbo` | | Table | String | Target table that the connector writes into. The table must exist unless you enable **Create table**. | Yes | `-` | | Mapping | Object | Key-value pairs that map payload data to the columns of your table. The key is a column in your table. The value is a payload field name such as `deviceId`, the matching default-schema column name such as `device_id`, or static text with an `@` prefix, such as `@plant-a`. Leave it empty to use the default table schema. Not available with **Bulk insert mode** set to `bulkcopy`. | No | `-` | | Enable JSONPath | Boolean | Read **Mapping** values that start with `$` as JSONPath expressions, such as `$.factory.line.machine.sensors.temperature`. Values that don't start with `$` keep their usual meaning, and an expression that matches nothing writes `NULL`. Not available with **Bulk insert mode** set to `bulkcopy`. | No | Disabled | | Create table | Boolean | Create the target table with the default table schema if it doesn't exist. The database user needs permission to create a table. | Yes | Disabled | | Commit timeout | Integer | Maximum time in milliseconds that one batch insert transaction can take before the connector treats it as failed and retries it. Range is `10` to `65535`. | No | `30000` | | Batch timeout | Integer | Maximum time in milliseconds after the previous commit attempt before the connector writes a batch that isn't full. Range is `10` to `65535`. | No | `500` | | Max transaction size | Integer | Maximum number of messages in one batch insert. The connector writes a full batch without waiting for **Batch timeout**. Range is `1` to `65535`. | No | `500` | | Bulk insert count | Integer | Number of messages the connector buffers before it writes a batch. A value of `0` uses **Max transaction size**. Range is `0` to `65535`. | No | `0` | | Bulk insert mode | Dropdown | How the connector inserts rows. `values` uses a multi-row `INSERT` statement. `bulkcopy` uses TDS bulk copy (BCP) for higher throughput, and requires the default table schema with no **Mapping**. | No | `values` | | Write mode | Dropdown | When a message counts as delivered: `sync` or `async`. | No | `sync` | | Parallel transactions | Integer | Maximum number of batch inserts that run at the same time, each on its own database connection. Values above `1` increase throughput, but the connector can then write rows out of publish order. Range is `1` to `64`. | No | `1` | | Parallel Publish Count | Integer | Number of messages the connector publishes at the same time. In `sync` write mode, this value limits how many rows a batch can collect. Range is `1` to `4000`. | No | `1000` | ## Additional attributes | Field | Type | Description | Required | Default | | ----------------------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ----------- | -------- | | Throttling limit | Integer | Maximum outbound messages per second. A value of `0` disables throttling. Minimum is `0`. | No | `0` | | Persistent storage | Boolean | Store outbound queue messages on disk. If the connection drops or the system loses power, the messages are retained and sent once the connection is restored. | No | Enabled | | Persistent storage size limit | Integer | Maximum disk space in GB used for persisted data. Applies only when **Persistent storage** is enabled. Minimum is `1`. | Conditional | `1` | | Queue Mode | Dropdown | Order in which the connector sends buffered messages. `fifo` sends the earliest message first. `lifo` sends the latest message first. Applies only when **Persistent storage** is enabled. | No | `lifo` | | Enable Logging | Boolean | Write connection and publish error messages to the connector log. Logging can increase CPU usage, so enable it only for debugging. | No | Disabled | ## Encrypt modes | Value | Behavior | | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `disable` | No TLS. The connector sends the login and data in cleartext. The server must allow unencrypted connections. | | `false` | The connector uses TLS for the login handshake only, to protect credentials. Data after the login is unencrypted. This is the historical SQL Server default. | | `true` | The connector uses TLS for the login and all data, and validates the server certificate. If the server uses a CA that isn't public, enter it in **CA Certificate**. | | `strict` | TDS 8.0 strict encryption, for SQL Server 2022 and later. The connector negotiates TLS before the TDS handshake, and the server certificate must validate, with no fallback. | :::hint{type="warning"} **Caution:** With **Encrypt** set to `disable` or the default `false`, the connector sends data in cleartext. Use `true` or `strict` in production. ::: ## Default table schema When you enable **Create table** and the target table doesn't exist, the connector creates it with the following schema. ```sql IF OBJECT_ID(N'[SCHEMA_NAME].[TABLE_NAME]', N'U') IS NULL BEGIN CREATE TABLE [SCHEMA_NAME].[TABLE_NAME] ( id BIGINT IDENTITY(1,1) NOT NULL, record_uuid UNIQUEIDENTIFIER NOT NULL, arrived_at DATETIME2(3) NOT NULL, device_id NVARCHAR(64) NULL, register_id NVARCHAR(64) NULL, tag_name NVARCHAR(64) NULL, datatype NVARCHAR(32) NULL, value NVARCHAR(MAX) NULL, success BIT NULL, PRIMARY KEY (id) ); END ``` To write into a table with different columns, create the table yourself, use **Mapping**, and leave **Bulk insert mode** set to `values`. For mapping and JSONPath examples, see the integration guide. ## Write modes | Write mode | Behavior | | ---------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `sync` | The connector acknowledges a message only after it commits the batch that holds the message. If the database is unreachable, the message stays in persistent storage, and the connector writes it after it reconnects. | | `async` | The connector acknowledges a message once it buffers it. If a batch fails to write, the connector drops it and counts the messages as dropped. | :::hint{type="warning"} **Caution:** In `async` write mode, or with **Persistent storage** disabled, you can lose data during a database outage. Use `async` only when throughput matters more than delivery. ::: ## IT Hub Use the [IT Hub Processor](docId\:d34bVo1TMgjw01Ga7K3yj) to run an ad-hoc SQL query through the connector. The connector streams the result back one message per row, on the response topic supplied in the request. ## Related topics - [Add a Connector](docId:4YVfCEP7GYScJHMKYFCuM) - [Manage Connectors](docId\:tQoP7QtquSQqDq9yhbJ34) - [IT Hub Processor](docId\:d34bVo1TMgjw01Ga7K3yj)