SQL Server Destination

Connect Microsoft SQL Server or Azure SQL Database as a destination to load data from your Supaflow sources. Supaflow creates and manages its own schemas and tables in the target database, writes rows, and adds columns as your source schema evolves -- so the destination user needs privileges to create and write objects, not the read-only access a SQL Server source uses.

Prerequisites

Before you begin, ensure you have:

• A SQL Server instance (SQL Server 2016 or higher, or Azure SQL Database)
• Network connectivity between Supaflow and your SQL Server
• A database user that can create schemas and tables and write data (see below) -- a read-only login is not sufficient for a destination
• 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 Destination User

Create a dedicated user for Supaflow with permission to create schemas and tables and write data. The simplest setup grants db_owner on the target database; for least privilege, grant the three roles shown in the last example.

On-premises SQL Server (db_owner):

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

USE [your_database];
CREATE USER supaflow_writer FOR LOGIN supaflow_writer;
ALTER ROLE db_owner ADD MEMBER supaflow_writer;

Azure SQL Database (contained user, db_owner):

USE [your_database];
CREATE USER supaflow_writer WITH PASSWORD = 'your_secure_password';
ALTER ROLE db_owner ADD MEMBER supaflow_writer;

Least privilege (instead of db_owner): grant the roles that let Supaflow create objects, write rows, and read for incremental loads:

ALTER ROLE db_ddladmin ADD MEMBER supaflow_writer;    -- create and alter schemas/tables
ALTER ROLE db_datawriter ADD MEMBER supaflow_writer;  -- insert and update rows
ALTER ROLE db_datareader ADD MEMBER supaflow_writer;  -- read for incremental loads

Replace your_database and the password with your actual values.

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 target database to load into
Example: analytics_db

Database Username*

Username for authentication
Example: supaflow_writer

Database Password*

Password for the database user
Stored encrypted

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

JDBC Connection Properties

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

---

Step 3: Test & Save

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

Schema Evolution

Supaflow creates the target schema and tables on the first load and keeps them in step with your source:

• New columns in the source are added to the destination table
• New tables are created when you add objects to the pipeline
• Type widening is applied when a source column's type grows
• Removed columns are kept in the destination; existing data is not dropped

Keeping the destination in step requires the user to create and alter its own tables, which is why the destination user needs the privileges listed in Prerequisites.

Troubleshooting

Common issues and their solutions:

Insufficient permissions

Problem:

• "CREATE SCHEMA permission denied" or "CREATE TABLE permission denied"
• The connection test passes but loads fail with a permission error

Solutions:

1. Confirm the user can create and write objects. A read-only login (only db_datareader or SELECT) cannot create schemas or tables. Grant db_owner, or db_ddladmin + db_datawriter + db_datareader:

   ALTER ROLE db_ddladmin ADD MEMBER supaflow_writer;
   ALTER ROLE db_datawriter ADD MEMBER supaflow_writer;
   ALTER ROLE db_datareader ADD MEMBER supaflow_writer;

2. Verify role membership:

   SELECT r.name AS role_name
   FROM sys.database_role_members m
   JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
   JOIN sys.database_principals u ON m.member_principal_id = u.principal_id
   WHERE u.name = 'supaflow_writer';

3. Azure SQL: make sure you created the user in the target database, not master.

---

Connection refused

Problem:

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

Solutions:

1. Verify firewall rules:
   • Add Supaflow IP (18.214.240.61) to allowed IPs
   • For Azure SQL: add it under Networking > Firewall rules in the Azure portal
   • For on-premises: check Windows Firewall and network security groups
2. Check SQL Server is reachable on the configured host and port (default 1433), and that TCP/IP is enabled.

---

Authentication failed

Problem:

• "Login failed for user"
• Error 18456

Solutions:

1. Verify the username and password.
2. On-premises: SQL Server must allow SQL Server authentication (mixed mode).
3. Azure SQL: confirm the user exists in the target database:

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

---

TLS / encryption errors

Problem:

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

Solutions:

1. Azure SQL requires encryption -- keep Encrypt set to true.
2. On-premises with a self-signed certificate: enable Trust Server Certificate, or set Encrypt to false for testing only.
3. Strict TLS (TDS 8.0): set Encrypt to strict (requires SQL Server 2022 or Azure SQL with a properly signed certificate).

Support

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