Microsoft SQL Server connector

SQL Server is enabled as a source and as a destination connector. Use mssql for both connector_id and connector_type; its connector category is databases. It reads from and writes to SQL Server, Azure SQL Database, Azure SQL Managed Instance, Amazon RDS for SQL Server and Cloud SQL for SQL Server.

The supported server version is SQL Server 2016 SP1 or later on Windows, Linux or containers, plus the managed services listed above. Azure SQL Database, Azure SQL Managed Instance, Amazon RDS for SQL Server and Cloud SQL for SQL Server are in preview.

Preview means the feature is supported but has not yet been validated on every configuration of that service. Its behavior may still change. Contact support if something does not work as described.

Database settings

Connections from VegaFlow compute follow the network access you select for the connection. Private and loopback addresses are reachable only when that network policy allows them.

Authentication

Set auth.type to exactly one method. A connection carries only the fields of its chosen method, so switching method means replacing the whole auth object. Store credentials in a Secret Vault and select references containing namespace and key. Every method except sql_login is in preview, as described at the top of this page.

SQL Server login

{
  "host": "sql.example.internal",
  "port": 1433,
  "database": "sales",
  "username": "vegaflow_reader",
  "auth": {
    "type": "sql_login",
    "password_ref": {
      "namespace": "production-data",
      "key": "mssql-password"
    }
  },
  "encrypt": "mandatory"
}

Windows account (NTLM)

{
  "host": "sql.corp.example.internal",
  "database": "sales",
  "username": "svc_vegaflow",
  "auth": {
    "type": "windows_ntlm",
    "windows_domain": "CORP",
    "password_ref": {
      "namespace": "production-data",
      "key": "mssql-ad-password"
    }
  }
}

Kerberos

Use Kerberos when the server only accepts Kerberos, or when your security policy forbids NTLM. VegaFlow signs in to your Active Directory domain controllers as the user, then asks for a ticket to the SQL Server's service principal name (SPN) and presents it to SQL Server.

{
  "host": "sql.corp.example.com",
  "port": 1433,
  "database": "sales",
  "username": "svc_vegaflow",
  "auth": {
    "type": "kerberos",
    "realm": "CORP.EXAMPLE.COM",
    "kdc_hosts": ["dc1.corp.example.com", "dc2.corp.example.com:88"],
    "keytab_ref": {
      "namespace": "production-data",
      "key": "mssql-keytab"
    }
  }
}

To set it up:

1. Register the SPN. Kerberos asks for MSSQLSvc/<host>:<port>, where <host> is the fully qualified name you put in host (an IP address cannot be used unless you set service_principal_name). SQL Server registers it itself when its service account may; otherwise register it on the service account yourself and check it:

   setspn -S MSSQLSvc/sql.corp.example.com:1433 CORP\sqlservice
   setspn -L CORP\sqlservice

   A named instance is registered as MSSQLSvc/sql.corp.example.com:INSTANCENAME, which is what VegaFlow requests when you set instance_name. For a listener or alias, set auth.service_principal_name to the SPN you registered.

2. Create the login. Map the Active Directory account to a SQL Server login, then grant it the permissions in Permissions:

   CREATE LOGIN [CORP\svc_vegaflow] FROM WINDOWS;

3. Let VegaFlow reach the domain controllers. kdc_hosts are network destinations like host: they follow the connection's network access, so private domain controllers need a private connection on dedicated compute, and TCP port 88 (or the port you give) must be open to it. If the database is reached through an SSH tunnel, the domain controllers are reached through the same bastion, so the bastion must be able to reach them.

4. Provide the credential. Use a password secret, or a keytab. To create a keytab on a domain controller with ktpass (this sets the account's password and key version, so use a dedicated service account):

   ktpass /princ svc_vegaflow@CORP.EXAMPLE.COM /mapuser CORP\svc_vegaflow /crypto AES256-SHA1 /ptype KRB5_NT_PRINCIPAL /pass * /out svc_vegaflow.keytab

   Or with ktutil on Linux, where the key version number after -k must be the account's current one (msDS-KeyVersionNumber):

   ktutil
   addent -password -p svc_vegaflow@CORP.EXAMPLE.COM -k 3 -e aes256-cts-hmac-sha1-96
   wkt svc_vegaflow.keytab

   Then store the base64 text of the file in your Secret Vault, for example base64 -w0 svc_vegaflow.keytab, and reference it with keytab_ref. Create a new keytab whenever the account's password changes.

5. Keep clocks in step. Kerberos rejects requests when the clocks of VegaFlow and the domain controller differ by more than a few minutes (five by default). Keep the domain controllers on network time.

If a connection fails, the error tells you which step to check:

Microsoft Entra ID service principal

{
  "host": "myserver.database.windows.net",
  "database": "sales",
  "auth": {
    "type": "entra_service_principal",
    "client_id": "00000000-0000-0000-0000-000000000000",
    "tenant_id": "11111111-1111-1111-1111-111111111111",
    "client_secret_ref": {
      "namespace": "production-data",
      "key": "mssql-client-secret"
    }
  }
}

For Entra methods, create the database user from the identity, for example CREATE USER [vegaflow-app] FROM EXTERNAL PROVIDER;, and grant it the permissions in Permissions.

There is no managed-identity method. The compute that runs a connection is shared, so the identity of the machine it runs on is never available to a connection. Use an Entra service principal, or a token you issue, to give VegaFlow access to Azure SQL.

TLS

Keep encrypt at mandatory or strict and trust_server_certificate at false for production connections.

SSH tunnel

Use an SSH tunnel when the database is reachable only through a bastion. ssh_tunnel is independent of auth.type: the database login is configured as usual, and every connection VegaFlow makes to the database, including discovery, sync, metadata, actions and triggers, goes through the tunnel.

{
  "host": "sql.private.internal",
  "port": 1433,
  "database": "sales",
  "username": "vegaflow_reader",
  "auth": {
    "type": "sql_login",
    "password_ref": {
      "namespace": "production-data",
      "key": "mssql-password"
    }
  },
  "ssh_tunnel": {
    "host": "bastion.example.internal",
    "port": 22,
    "user": "connector",
    "host_key_fingerprint": "SHA256:replace-with-verified-fingerprint",
    "private_key_ref": {
      "namespace": "production-data",
      "key": "ssh-private-key"
    }
  }
}

host is the database address as seen from the bastion. Both the bastion and the database address must be allowed by the connection's network access.

Some settings need more than a single forwarded TCP connection, so a connection that sets ssh_tunnel is rejected if it also sets:

With an Azure SQL Database server, use the Proxy connection policy when connecting through a tunnel; the Redirect policy sends clients to node addresses that the tunnel does not reach.

When encrypt is on and trust_server_certificate is false, the certificate is verified against host, or against host_name_in_certificate if you set it, exactly as without a tunnel.

Permissions

Create a dedicated login or user for VegaFlow and grant only what the features you use need.

A minimal read-only user for snapshot and incremental sync on SQL Server:

CREATE LOGIN vegaflow_reader WITH PASSWORD = N'<strong password>';

USE [sales];
CREATE USER vegaflow_reader FOR LOGIN vegaflow_reader;
GRANT SELECT ON SCHEMA::dbo TO vegaflow_reader;   -- or grant on individual tables
GRANT VIEW DEFINITION TO vegaflow_reader;

On Azure SQL Database, create a contained user instead: CREATE USER vegaflow_reader WITH PASSWORD = N'<strong password>';.

Streams and sync modes

Catalog discovery returns one stream per table. Each stream supports snapshot, incremental_cursor and cdc. CDC is offered for every table so that you can select it; if the table is not ready, the run fails with a message that contains the SQL to run. Streams also report whether the table currently has a CDC capture instance and whether it is change-tracked. Those flags describe setup only, not whether your login may read the change data.

Tables without a primary key use the first unique non-null index for ordering, or the first column when there is none; in that last case rows that share the first column's value can repeat across snapshot pages. Binary key columns are not supported for keyset reads.

Change data capture

A CDC stream reads changes in one of two modes, set with change_mode:

• native_cdc is the default. It uses SQL Server change data capture and delivers both the before and after image of each change.
• change_tracking uses SQL Server Change Tracking. It delivers the current state of each changed row.

VegaFlow never falls back from one mode to the other, and it never enables either feature for you. You set it up in the source database. When a stream starts, VegaFlow checks the prerequisites and, if one is missing, fails with the exact SQL for the server variant it detected, with your database, schema and table names filled in.

A stream is bound to the mode it started in. Resuming it in the other mode fails with POSITION_REBASE_REQUIRED. To change mode, restart the stream as described in Retention and lost positions.

Each CDC stream first takes a snapshot, then streams changes. Changes that the snapshot already contains are replayed after it, so apply them to the destination as keyed upserts and deletes.

Set up native CDC

Run these statements in the source database. The first one depends on where the database runs.

Notes by variant:

• Azure SQL Database. A DTU-based database needs tier S3 or higher. Basic, S0, S1 and S2 are not supported. Every vCore tier works. The capture and cleanup schedule cannot be changed.
• Azure SQL Managed Instance. It uses the same procedures and the Agent as SQL Server. VegaFlow does not check the Agent state there.
• Amazon RDS. After the database is enabled, any db_owner of the database can enable tables. A database restore disables CDC again, so enable it again afterwards.
• Cloud SQL. Supported on SQL Server 2017, 2019 and 2022 Standard and Enterprise editions.

VegaFlow detects the variant when the stream is prepared. A login that cannot see msdb is treated as a plain SQL Server, and the setup message is the generic one.

Then enable each table, on every variant. A db_owner can do this:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'orders',
    @role_name     = NULL;

@role_name = NULL means no gating role. With a gating role, the VegaFlow login must be a member of that role or db_owner.

When the change tables hold LOB values larger than the max text repl size server option (65536 bytes by default), SQL Server itself refuses to capture them. Raise the option with sp_configure if your rows hold larger nvarchar(max) values.

Set up Change Tracking

Change Tracking uses the same statements on every variant. Microsoft documents it for SQL Server, Azure SQL Database and Azure SQL Managed Instance. For Amazon RDS and Cloud SQL, confirm in your provider's documentation that Change Tracking is available for your edition.

ALTER DATABASE [sales] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [sales] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE [dbo].[orders] ENABLE CHANGE_TRACKING;   -- the table needs a primary key
GRANT VIEW CHANGE TRACKING ON OBJECT::[dbo].[orders] TO [vegaflow_reader];
GRANT SELECT ON OBJECT::[dbo].[orders] TO [vegaflow_reader];

Set change_mode to change_tracking on the stream. VegaFlow checks, in order, that snapshot isolation is on, that Change Tracking is on for the database and the table, that the table has a primary key, that the login has VIEW CHANGE TRACKING, and that the key columns are not binary.

Change Tracking trade-offs

Change Tracking records that a row changed, with its key, a version and the operation. It does not record the row's old values.

• Intermediate states are not delivered. Many updates to a row between two reads arrive as one event.
• A primary key change arrives as a delete of the old key and an insert of the new one.
• TRUNCATE TABLE is not reported as row deletes. The rows remain in the destination, and a stream that was behind the truncation stops with POSITION_REBASE_REQUIRED.
• Per-column change detail is not collected.
• A long-lived snapshot transaction elsewhere on the instance can delay Change Tracking cleanup.

Choose native CDC when you need before images or one event per change. Choose Change Tracking when the current state per key is enough.

Retention and lost positions

A CDC stream resumes from a saved position. If the source no longer holds the changes after that position, the stream cannot continue without gaps.

Native CDC. Capture instances keep three days (4320 minutes) by default. To keep more, run this in the database as db_owner, then restart the cleanup job so the change applies:

EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;  -- minutes
EXEC sys.sp_cdc_stop_job  @job_type = N'cleanup';
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';

This applies to SQL Server, Managed Instance, RDS and Cloud SQL. On Azure SQL Database the cleanup schedule is managed by the platform and cannot be changed. Check Microsoft's Azure SQL Database documentation for whether a retention value can be set there.

Change Tracking. Set CHANGE_RETENTION on the database, for example ALTER DATABASE [sales] SET CHANGE_TRACKING (CHANGE_RETENTION = 14 DAYS);.

What happens when the position is lost. The stream stops and the run fails with the error code POSITION_REBASE_REQUIRED. This happens when:

• cleanup removed changes that the saved position still needs, or the stream's column is no longer captured;
• for Change Tracking, the position is older than the table's minimum valid version, the table was truncated, or Change Tracking was disabled and enabled again;
• Change Tracking was disabled on the table or database after the stream started;
• the server was restored to an earlier state;
• the stream's mode or capture instance changed on an existing position.

VegaFlow does not re-snapshot automatically, because a new snapshot rewrites the destination table. To recover, fix the cause, then remove the stream from the QuickFlow, let a run complete, and add the stream again. The next run starts it from a fresh snapshot, which also covers the rows missed in the gap, and then continues with changes. This works when the QuickFlow selects streams with all or exclude; a stream removed from an include list keeps its saved position. A VegaDB table keeps the history it was loaded from, so add the stream back with a new destination table.

A stream that is not run for longer than the retention window loses its place in the same way. Streams that run on a schedule move their position forward even when the table is quiet, so a quiet table does not fall behind.

Metadata

Metadata extraction creates VegaGraph entities for the server, database, schema, table, view, column, stored procedure, function and trigger. It adds edges for foreign keys, view and routine reads, writes and references, and the table or view a trigger belongs to.

Routines and triggers carry their definition, cut at 64 KiB with definition_truncated set. When a module is WITH ENCRYPTION or the login lacks VIEW DEFINITION, the definition is not available but the object is still listed. Lineage comes from SQL Server's dependency views, so statements built as dynamic SQL inside a procedure have no lineage. Dependencies on other databases and linked servers are recorded as unresolved and produce no edge.

Actions

SQL Server has no read-only transactions. sql.query checks the statement and always rolls back, but a function with an external effect cannot be detected, so give the login only the permissions it needs.

Arrow actions

sql.query_arrow and table.create_from_arrow move tabular data between SQL Server and pipelines without turning it into JSON.

sql.query_arrow takes sql, optional parameters, timeout_seconds and max_bytes. The query result is written in bounded batches, so memory does not grow with the result size, and the output is a file reference with the column names, SQL Server types, Arrow types and the row count. max_bytes caps the whole file (default 8 GiB); a result that exceeds it, or a single row too large for a batch, fails instead of being cut short. Give every column a unique name and alias unnamed expressions.

Other types, such as xml, sql_variant, geography, geometry, hierarchyid and CLR types, fail and report the column; cast them in the query. A datetime2(7) value outside the years 1677 to 2262 does not fit a nanosecond timestamp and fails; cast the column to datetime2(6) for such data.

table.create_from_arrow takes schema (an existing schema), table, body (the Arrow file) and optional timeout_seconds. It always creates a new table: if a table with that name exists, the action fails and leaves the existing table unchanged. The table is created and loaded in one transaction, so a bad file, an invalid value, a cancellation or an error leaves nothing behind. On success it returns the table's object_id, the row count and the type chosen for each column.

A nullable Arrow field becomes a nullable column; otherwise the column is NOT NULL. Column names must be unique ignoring case, up to 128 characters, and a table can have at most 1,024 columns. Nanosecond values must be whole multiples of 100 nanoseconds, and dates must fall in the years 1 to 9999. A value that cannot be stored exactly fails and reports the column rather than being rounded.

If SQL Server cannot confirm that the commit succeeded, the action reports an unknown outcome. Check whether the table exists before retrying, and never reuse a name you are unsure about.

Triggers

All SQL Server triggers poll the source.

These triggers deliver events to automations. A stale Change Tracking position or a recreated table fails the trigger with POSITION_REBASE_REQUIRED.

Use SQL Server as a destination

Select a SQL Server connection as the destination of a QuickFlow stream to write a source's data into a table. The same connection settings, authentication methods, TLS settings and SSH tunnel apply. The destination table is a table name or schema.table; the schema defaults to dbo. VegaFlow creates the table, and the schema if it is missing, when the table does not exist.

How data is written

Each batch is written in one transaction: rows are bulk-loaded into a staging table, applied to the destination table, and recorded in a batch ledger in the staging schema, then committed together. If a batch is delivered again, for example after a lost response, it is not applied twice. If a write fails, nothing of that batch is left in the table.

• append: inserts the rows.
• overwrite: empties the table and inserts the new rows in the same transaction, so a reader that queries during the run sees either the complete old contents or the complete new contents, never an empty or partial table. A reader waits while the change commits. The table is emptied with TRUNCATE TABLE, or with DELETE when a foreign key references the table. The table itself, with its indexes, constraints, permissions and triggers, is kept.
• upsert and scd1: updates matching keys and inserts new ones. The write does not use MERGE; it updates and then inserts under locks that keep concurrent writers from creating duplicates. A batch that contains the same key twice is rejected.
• scd2: when a tracked column changes, the current row is closed (is_current becomes false and effective_to is set) and a new version is inserted with the next version. The table gets the columns effective_from, effective_to, is_current and version. Unchanged rows get no new version.

Writes wait at most one minute for a lock held by another session. After that the run fails with a retryable error and nothing is changed, and a deadlock that SQL Server resolves against the write is also retried.

Column types

The types depend on where the data comes from.

From a SQL Server source, each column keeps its exact type, for example datetime2(7), money, nvarchar(50), uniqueidentifier, decimal(18,4), char(10), xml, geography and hierarchyid. Three types are replaced because they cannot be created or written as they were: text, ntext and image become varchar(max), nvarchar(max) and varbinary(max), and timestamp (rowversion) becomes binary(8) holding the copied value. Column collations are not copied; the destination database default applies. geography and geometry values are copied through their text form, in reference system 4326 and 0 respectively. sql_variant columns are not supported.

From any other source (databases such as PostgreSQL, and applications), the type follows the kind of the source column:

These column kinds have no SQL Server equivalent, and a stream that includes one fails before anything is written, naming the column: time with time zone, interval, range, network address, bit string and the spatial format of other databases. Exclude those columns from the stream.

Values are never rounded or truncated. A decimal that does not fit its column, NaN or infinity in a floating-point column, a date outside the years 1 to 9999, a time finer than 100 nanoseconds, or text longer than a bounded column fails the batch without writing any of it. SQL Server before 2025 has no JSON type, so JSON is stored as text; a JSON null value is stored as SQL NULL.

Keys. SQL Server cannot index max columns. A text key from a source other than SQL Server is created as nvarchar(450) with a case- and accent-sensitive binary collation, so keys such as a and A stay distinct, as in most source systems; a key value longer than 450 characters fails the batch. A binary key is varbinary(900). JSON, array and XML columns cannot be keys. Trailing spaces never make two keys different in SQL Server.

Existing tables and schema changes

If the destination table already exists, VegaFlow writes into it and keeps its column types, collations and constraints. A column it computes, or a rowversion column, cannot be written. Identity columns accept the source's values.

With schema evolution on, a new source column is added to the table as a nullable column, and a column widens when no value can be lost: an integer to a larger integer, a string or binary column to a longer one, a decimal to one that holds every old value, and a date or time to higher precision. A required column the source drops stays in the table and becomes nullable. A key column is never altered. With schema evolution off, any of these changes fails the run. A change that could lose data, such as narrowing, a different type family or changing a text column to a number, fails the run in both cases.

Change data capture into SQL Server

A stream read with change data capture or Change Tracking can be written with upsert or scd1. After the first snapshot, each change is applied inside the same transaction that records how far the source was read, so a change is not applied again after a retry. When the source gives every change an ordered position (SQL Server change data capture), changes at or before the recorded position are skipped. Change Tracking and sources such as PostgreSQL logical replication do not give ordered positions, so only a batch delivered again with the same identity is recognized; a different batch that repeats older changes would be applied again.

Destination permissions

Grant the destination login only what it needs:

Creating the schemas first and granting on them is the least-privileged setup:

CREATE SCHEMA vegaflow_stage AUTHORIZATION dbo;
GRANT CREATE TABLE TO [vegaflow_writer];
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON SCHEMA::vegaflow_stage TO [vegaflow_writer];
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [vegaflow_writer];

If the login lacks a permission, the run fails with a permission error that lists what the write needs, and nothing is changed.

Destination limits

• Streams that include a column kind listed above as unsupported fail until the column is excluded.
• Column collations of other databases are not carried over, and geography and geometry columns are written in reference systems 4326 and 0.
• Changes from change data capture are applied one at a time, so a large catch-up is slower than a snapshot.

Validate and save

1. Create the login or user, the CDC or Change Tracking setup you need, and the secret references.
2. Open VegaFlow → Connections → New connection and choose Microsoft SQL Server.
3. Enter the database settings, choose an authentication method and select its secret references.
4. Validate connectivity, authentication and the permissions for the features you use.
5. Save and grant the QuickFlow operator permission to use the connection. To write to SQL Server, select the connection as a destination and choose the write mode and key fields.

Revalidate after changes to credentials, certificates, database settings or network access. Use catalog discovery to select streams and sync modes for a flow.

Troubleshooting