SQL Server Source

Connect Microsoft SQL Server or Azure SQL Database as a source to extract data from your relational database.

Prerequisites

Before you begin, ensure you have:

An active SQL Server instance (SQL Server 2016 or higher, or Azure SQL Database)
Network connectivity between Supaflow and your SQL Server
A database user with SELECT privileges on target schemas
Firewall configured to allow connections from Supaflow IP

Whitelist Supaflow IP

Add the Supaflow IP to your firewall rules:

18.214.240.61

Azure SQL Database: This is a mandatory step. In the Azure portal, go to your SQL server > Networking > Firewall rules and add the IP above. Without this, connections will be refused.

On-premises SQL Server: Add the IP to your Windows Firewall or network security group if inbound connections are restricted.

Create Database User

Create a dedicated read-only user for Supaflow.

On-premises SQL Server:

USE [master];
CREATE LOGIN supaflow_reader WITH PASSWORD = 'your_secure_password';

USE [your_database];
CREATE USER supaflow_reader FOR LOGIN supaflow_reader;

Azure SQL Database (contained database user):

USE [your_database];
CREATE USER supaflow_reader WITH PASSWORD = 'your_secure_password';

Grant SELECT permission on the entire database:

GRANT SELECT ON DATABASE::[your_database] TO [supaflow_reader];

Or grant SELECT on a specific schema:

GRANT SELECT ON SCHEMA::[dbo] TO [supaflow_reader];

Or grant SELECT on a specific table:

GRANT SELECT ON [dbo].[your_table] TO [supaflow_reader];

Replace your_database, dbo, and your_table with your actual names.

Enable Change Tracking (Optional)

If you plan to use Change Tracking mode for incremental syncs, enable it on the database and each table you want to track.

Enable on the database:

ALTER DATABASE [your_database]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

Enable on each table (table must have a primary key):

ALTER TABLE [dbo].[your_table] ENABLE CHANGE_TRACKING;

The Supaflow user also needs VIEW CHANGE TRACKING permission:

GRANT VIEW CHANGE TRACKING ON SCHEMA::[dbo] TO [supaflow_reader];

Configuration

Step 1: Connection

Database Host*

SQL Server hostname or IP address
Example: myserver.database.windows.net (Azure), db.example.com, 192.168.1.100

Database Port*

Port on which SQL Server is listening
Default: 1433

Database Name*

Name of the database to connect to
Example: production_db

Database Username*

Username for authentication
Example: supaflow_reader

Database Password*

Password for the database user
Stored encrypted

Query Mode

How incremental changes are detected
Options: STANDARD (default), CHANGE_TRACKING

Mode	Description	Requirements
STANDARD	Uses a cursor column (e.g., `LastModifiedDate`) to detect new and changed rows. Best when tables have a reliable timestamp column.	None
CHANGE_TRACKING	Uses SQL Server Change Tracking to detect inserts, updates, and deletes. Captures the full change history including deletions.	Change Tracking enabled on database and tables. Primary key on every synced table. Minimum 7-day retention recommended.

Encrypt

Whether to encrypt the connection to SQL Server
Options: true (default), false, strict
Recommended: true for Azure SQL, strict for TDS 8.0

Trust Server Certificate

Trust the server certificate without validation
Default: unchecked (false)
Enable only for self-signed certificates or development environments

Step 2: Advanced Settings (Optional)

Noop Query

Query to test connectivity without returning data
Default: SELECT 1
Used for connection validation

JDBC Connection Properties

Additional JDBC connection parameters
Format: key=value pairs separated by semicolons
Example: loginTimeout=30;queryTimeout=60

Chunked Read Enabled

Enable keyset-based chunked reads for large tables
Default: true
When Window Limit is enabled (default), splits full-table reads into bounded batches using the primary key. When disabled, uses unbounded keyset read with resume semantics. Each chunk is resumable on failure. Only applies to initial/full-refresh reads on tables with a primary key -- incremental cursor-based reads are not chunked.

Chunked Read Chunk Size

Maximum number of rows per chunk
Default: 1,000,000 (Range: 1,000 to 5,000,000)

Chunked Read Window Limit Enabled

Use SQL TOP/LIMIT to bound each chunk query
Default: true

Chunked Read Memory Aware Limit Enabled

Dynamically reduce chunk size for wide rows based on available memory
Default: false

Chunked Read Target Memory MB

Target memory budget per chunk when memory-aware limiting is enabled
Default: 50 MB (Range: 1 to 4,096)

Schema Refresh Interval

Interval in minutes for schema metadata refresh
0 = refresh before every pipeline execution
-1 = disable schema refresh
Default: 30 (Range: -1 to 10080)

Step 3: Test & Save

After configuring all required properties, click Test & Save to verify your connection and save the source.

Query Modes

Standard Mode

Standard mode works like any JDBC source connector. During incremental syncs, it uses a cursor column (typically a timestamp like LastModifiedDate or UpdatedAt) to fetch only rows that changed since the last sync.

Best for tables with reliable modification timestamps
Cannot detect deletes (only inserts and updates)
Works with any SQL Server version

Change Tracking Mode

Change Tracking mode uses SQL Server's built-in Change Tracking feature to detect all changes including inserts, updates, and deletes.

How it works:

Bootstrap (first sync): Captures a snapshot of the current CT version, then reads all rows from the table. Each row is tagged with the snapshot version and __ct_is_deleted = 0.
Incremental syncs: Queries CHANGETABLE(CHANGES ...) to get only rows that changed since the last CT version. Changed rows are joined back to the source table for current values. Deleted rows appear with __ct_is_deleted = 1.

Synthetic columns added to every table:

Column	Type	Description
`__ct_version`	LONG	The Change Tracking version when this row was captured
`__ct_is_deleted`	INT	`0` for active rows, `1` for deleted rows

Requirements:

SQL Server 2016 or higher (or Azure SQL Database)
Change Tracking enabled on the database with at least 7-day retention
Change Tracking enabled on each table
Every synced table must have a primary key

Tables without Change Tracking: If a table does not have Change Tracking enabled, it will be automatically skipped during schema discovery with the reason CHANGE_TRACKING_NOT_ENABLED.

Troubleshooting

Common issues and their solutions:

Connection refused

Problem:

Cannot connect to SQL Server
"Connection refused" or network timeout error

Solutions:

Verify firewall rules:
- Add Supaflow IP (18.214.240.61) to allowed IPs
- For Azure SQL: Add to Networking > Firewall rules in the Azure portal
- For on-premises: Check Windows Firewall and network security groups
Check SQL Server is running:
```
# Windows
Get-Service MSSQLSERVER
```
Verify port is open:
- Default port is 1433
- Check SQL Server Configuration Manager for TCP/IP settings
- Ensure TCP/IP protocol is enabled
Azure SQL specific:
- Ensure "Allow Azure services" is enabled if connecting from Azure
- Check that the server firewall (not just database firewall) allows the IP

Authentication failed

Problem:

"Login failed for user"
Error 18456

Solutions:

Verify username and password:

Test login manually:

sqlcmd -S your_host -U supaflow_reader -P your_password -d your_database

On-premises: Check authentication mode:
- SQL Server must be in "SQL Server and Windows Authentication" mode
- Change in SQL Server Management Studio > Server Properties > Security
- Restart SQL Server after changing

Azure SQL: Verify user exists in the correct database:

SELECT name FROM sys.database_principals WHERE name = 'supaflow_reader';

Reset password if needed:

-- On-premises
ALTER LOGIN supaflow_reader WITH PASSWORD = 'new_secure_password';

-- Azure SQL (contained user)
ALTER USER supaflow_reader WITH PASSWORD = 'new_secure_password';

Permission denied on tables

Problem:

"SELECT permission denied"
Can connect but cannot read data
Some tables visible, others not

Solutions:

Grant SELECT on all tables in a schema:

GRANT SELECT ON SCHEMA::[dbo] TO [supaflow_reader];

Grant SELECT on the entire database:

GRANT SELECT ON DATABASE::[your_database] TO [supaflow_reader];

Check current permissions:

SELECT
    dp.name AS user_name,
    o.name AS object_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE dp.name = 'supaflow_reader';

Add to db_datareader role (grants SELECT on all tables):

ALTER ROLE db_datareader ADD MEMBER [supaflow_reader];

TLS / Encryption errors

Problem:

"SSL connection failed"
"The driver could not establish a secure connection"
Certificate verification errors

Solutions:

For Azure SQL:
- Set Encrypt to true (default) -- Azure SQL requires encryption
- If using a corporate proxy, you may need to enable Trust Server Certificate
For on-premises with self-signed certificates:
- Enable Trust Server Certificate checkbox
- Or set Encrypt to false for testing (not recommended for production)
For strict TLS (TDS 8.0):
- Set Encrypt to strict
- Requires SQL Server 2022 or Azure SQL
- Server certificate must be properly signed

Change Tracking errors

Problem:

Tables skipped with CHANGE_TRACKING_NOT_ENABLED
"CT incremental read requires custom_state" error
CT version mismatch

Solutions:

Verify CT is enabled on the database:

SELECT is_change_tracking_enabled
FROM sys.databases
WHERE name = DB_NAME();

Verify CT is enabled on the table:

SELECT t.name, ct.is_track_columns_updated_on
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON ct.object_id = t.object_id;

Enable CT on a missing table:

ALTER TABLE [dbo].[your_table] ENABLE CHANGE_TRACKING;

Check CT retention period:

SELECT change_tracking_retention, change_tracking_retention_period_units
FROM sys.change_tracking_databases;

If the retention period expired between syncs, trigger a full resync.

Grant VIEW CHANGE TRACKING:

GRANT VIEW CHANGE TRACKING ON SCHEMA::[dbo] TO [supaflow_reader];

Tables not appearing in schema

Problem:

Connected successfully but tables missing
Some tables visible, others not
Empty schema list

Solutions:

Verify SELECT permission:

GRANT SELECT ON SCHEMA::[dbo] TO [supaflow_reader];

List tables the user can access:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Refresh schema in Supaflow:
- Set Schema Refresh Interval to 0
- Save and test connection again
Check for schema filtering:
- Supaflow discovers all schemas the user can read
- Verify the user has USAGE on the desired schemas

Connection timeout

Problem:

Connection times out during setup
Slow query response
Test connection takes too long

Solutions:

Increase timeout in JDBC properties:
- loginTimeout=60 (60 seconds)
- queryTimeout=60
- Format: loginTimeout=60;queryTimeout=60
Check network latency:
```
ping your_database_host
```

Verify SQL Server performance:

-- Check for long-running queries
SELECT session_id, start_time, status, command, wait_type
FROM sys.dm_exec_requests
WHERE status = 'running'
ORDER BY start_time;

Azure SQL specific:
- Check DTU/vCore utilization in the Azure portal
- Scale up the database tier if consistently hitting resource limits

Support

Need help? Contact us at support@supa-flow.io

Prerequisites​

Configuration​

Step 1: Connection​

Step 2: Advanced Settings (Optional)​

Step 3: Test & Save​

Query Modes​

Standard Mode​

Change Tracking Mode​

Troubleshooting​

Connection refused​

Authentication failed​

Permission denied on tables​

TLS / Encryption errors​

Change Tracking errors​

Tables not appearing in schema​

Connection timeout​

Support​

Prerequisites

Configuration

Step 1: Connection

Step 2: Advanced Settings (Optional)

Step 3: Test & Save

Query Modes

Standard Mode

Change Tracking Mode

Troubleshooting

Connection refused

Authentication failed

Permission denied on tables

TLS / Encryption errors

Change Tracking errors

Tables not appearing in schema

Connection timeout

Support