SQL Server (MSSQL)
The SQL Server connector reads tables, views and stored procedure results, and writes rows to tables and views, in Microsoft SQL Server on premises or in Azure. It signs in with SQL Server authentication.
When to use it
- You read from or write to Microsoft SQL Server.
- You read a large table with a primary key, which is read by seeking the key.
- You need Upsert, Update or Delete loads applied as one statement per batch.
Use the Generic REST API connector instead when the only access you have is an HTTP endpoint in front of the database.
What you need
- The server name and port the runtime can reach.
- A SQL Server login and password. Windows authentication is not supported.
- The login needs read access for Sources and write access for loads.
Connect
The connection form lists its steps on the left. Open any step from that list. The Environments step holds the values that differ per environment.
Test connection is in the form footer. Beside it, Test against picks Base, the connection as saved, or an environment, whose values are applied first.
| Field | Step | What to enter |
|---|---|---|
| Username | Authentication | The SQL Server login. Required. |
| Password | Authentication | The login's password. Required. |
| Server | Connection | Host name or IP address. |
| Instance (Optional) | Connection | A named instance, such as SQLEXPRESS. |
| Port | Connection | Default 1433. |
| Database (Optional) | Connection | Blank uses master. An action can name its own database. |
| Encrypt connection | Connection | Uses TLS. On by default. |
| Trust server certificate | Connection | Accepts a self-signed or private-CA certificate. On by default. |
| Streaming Mode | Options | Reads rows in chunks. Off by default. |
Supported operations
| Action | Node | Operations |
|---|---|---|
| Table Query (Source) | Source | Query, in No-code or SQL mode |
| Table Load (Target) | Load | Insert, Update, Upsert, Delete |
| View Insert (Target) | Load | Insert |
| Stored Procedure (Source) | Source | Execute |
| Stored Procedure (Target) | Load | Execute |
| DDL Script | DB Script | Create, Alter, Drop, Truncate, Rename |
Refresh Schema on the Source node reads the action's current columns and parameters from the database.
Limits
- A table with a single-column primary key is read by seeking that key. Other objects, and any read with a row limit, are read in a fixed order with an offset, which is slower on a large table.
- Upsert, Update and Delete loads stage rows in a table, then run one statement.
- Staged rows are sent in bulk loads sized by bytes.
- An Insert load is sent as one bulk load, so it applies in full or not at all.
- A bulk load the driver refuses is retried with every column as text. That is slower, and the run log records it.
Troubleshooting
- Test connection fails with correct credentials. The database must accept connections from the runtime that runs the data pipeline, not from your computer. For a database on a private network, run a Private Agent inside that network or allow-list the runtime's address.
- The test passes but Configure Action lists no objects. The user can sign in but cannot see the catalog. Grant it read access on the schema, then press Get Metadata again.
- A column is missing from the field list. The list is read when metadata is loaded. Press Get Metadata again to read the current columns.
- A load fails on one batch. The run log names the statement and the batch. Check the rows in that batch against the column types and lengths.
- A certificate error with Trust server certificate off. The server's certificate is not signed by a trusted authority. Turn Trust server certificate on, or install a certificate from a trusted authority on the server.
Related
- Source node, Load node and DB Script node.
- Connections for environments and sharing.
- PostgreSQL, MySQL, Oracle and Snowflake.