Microsoft SQL Server (MSSQL)

Microsoft SQL Server or MSSQL, is a popular and widely used relational database management system (RDBMS). Developed by Microsoft, SQL Server offers robust data storage, management, and retrieval capabilities. It uses structured query language (SQL) as its primary language for interacting with the database.

You can use SQL Server as a destination in Weld to load data from your sources, as the data warehouse behind your Weld Models, and as a source for Reverse-ETL syncs.

Supported flavours

Connector	Destination & data warehouse
Microsoft SQL Server	Yes
SQL Server on RDS	Yes
Azure SQL Database	Yes
Microsoft Dynamics 365 via SQL Server	ELT source only

Weld tests every release against SQL Server 2017 through 2025, and against Azure SQL Database.

Why Dynamics 365 is source-only

A Dynamics 365 connection resolves either to the Dataverse TDS endpoint, which is read-only and rejects everything a destination has to write, or to a live Dynamics application database, which is no place for Weld to create schemas. Use it as an ELT source and pick a separate SQL Server as your destination.

Setup Guide

1. Let Weld reach your server

Requests from Weld always come from the following IP pool:

3.64.84.139
3.65.119.169
35.156.133.78

Allow all three in your network policies, SSH gateway or the database itself.

Azure SQL Database blocks everything by default. In the Azure portal, open your SQL server → Networking → Public access and add firewall rules for the three IPs above. Without them every connection attempt times out rather than failing with a useful error.

2. Fill in the connection fields

Server: The hostname of your SQL Server instance. For Azure SQL Database this looks like your-server.database.windows.net.
Port: The port your instance listens on. Defaults to 1433.
Database: The database Weld should write to.
User: The login Weld connects as.
Password: The password for that login.

For self-hosted SQL Server there is also Implicitly Trust Connection Certificates, which skips validation of the server certificate. Turn it on only for instances serving a self-signed certificate. Azure SQL Database serves a publicly trusted certificate, so the option isn't offered there.

Azure SQL Database: Microsoft Entra ID

Azure SQL Database connections can authenticate either with a SQL login or with a Microsoft Entra ID service principal. The service principal path is required for servers configured with Entra-only authentication, and needs three extra fields:

Directory (tenant) ID — from the app registration overview page in Microsoft Entra ID.
Application (client) ID
Client secret

The app registration stays in your own tenant, so rotating the secret is something you control — update it on the Weld connection when you do. The service principal also needs a matching database user, which the next step creates.

3. Grant the right permissions

The user Weld connects as needs to, within the database you selected:

Create schemas
Create, alter, truncate and drop tables in those schemas
Bulk insert into them
Create and alter views (for models and the raw layer)
Select from everything Weld manages

Three database roles cover exactly that: db_ddladmin for the objects, db_datareader and db_datawriter for the rows in them. Pick the tab matching your setup, replace the placeholder password, and run it:

Create the Weld user

-- On the master database: the login Weld signs in with.
CREATE LOGIN weld WITH PASSWORD = 'a-strong-password';
GO

-- On the database you named on the connection.
USE your_database;
GO

CREATE USER weld FOR LOGIN weld;

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

FROM EXTERNAL PROVIDER has to be run by an Entra identity. Azure SQL Database resolves the name against your directory using the credentials of whoever is executing the statement, so running it as a SQL login fails with "Principal … could not be resolved". Sign in to the database as the server's Entra admin (or any Entra-authenticated user) first.

If your organisation doesn't allow role membership, the equivalent explicit grants are:

GRANT CREATE SCHEMA TO weld;
GRANT CREATE TABLE TO weld;
GRANT CREATE VIEW TO weld;
GRANT ALTER ANY SCHEMA TO weld; -- to alter, truncate and drop within its own schemas
GRANT SELECT, INSERT, UPDATE, DELETE TO weld;

db_owner also works if you'd rather not enumerate anything, but it is more than Weld uses.

A read-only login is enough for SQL Server as an ELT source, but not as a destination — Weld creates and maintains tables on your behalf here.

Bulk loading needs nothing beyond INSERT on the target table: Weld streams rows over the TDS bulk load protocol rather than issuing BULK INSERT, so ADMINISTER BULK OPERATIONS is not required.

4. Optional settings

Compress the tables Weld creates — creates synced tables with SQL Server page compression. See Page compression below.
Connect through an SSH tunnel — for instances not reachable from the internet. You'll need to provide the SSH host, user and port, and authorise the public key Weld shows you.

How Weld organises your data

Everything lives inside the single database you named on the connection, separated by schema:

Schema	What Weld puts there
One per source	Landing tables written by ELT syncs, named after the sync's destination schema
`WELD_RAW`	Passthrough views over each landing table — these are what you reference in models
`WELD_MODELS`	Your published models: views, plus real tables for materialised models

So a deals table synced from a HubSpot connection lands as hubspot.deals, and Weld creates the view WELD_RAW.hubspot__deals over it. Reference the view in your models rather than the landing table directly.

WELD_RAW and WELD_MODELS are only defaults — you can choose different names under Advanced while setting up the data warehouse. Weld creates whichever schemas it needs the first time it writes to them.

How values are stored

Weld maps your sources' types onto SQL Server as follows:

Source type	SQL Server type
boolean	`bit`
int	`int`
long	`bigint`
float / double	`float`
bytes	`varbinary(max)`
string	`nvarchar(max)`
timestamp	`datetime2`

Anything Weld can't map falls back to nvarchar(max), which is also where nested values — JSON objects and arrays — are stored, as JSON text.

Timestamps are stored in UTC. datetime2 carries no time zone of its own, so a source value that had one — a SQL Server datetimeoffset column, or any source that reports a zone — is converted to the equivalent UTC instant and the original offset isn't kept. The point in time is preserved; the local wall-clock reading it was written as is not.

To present a local time, convert in a model rather than expecting the column to carry one:

SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS created_at_local
FROM WELD_RAW.hubspot__deals

Every synced row carries a _weld_synced timestamp. For database sources with CDC enabled, rows deleted at the source are flagged rather than removed: Weld sets _weld_deleted_at on the matching row so you keep the history. Filter on _weld_deleted_at IS NULL in your models to see only live rows.

Collation

Weld creates every character column with the Latin1_General_100_BIN2 collation, whatever your database's default is. It compares and sorts by code point, which makes it case- and accent-sensitive and matches how BigQuery and Snowflake treat text — so a model keeps its results if you move between warehouses. This applies to synced tables and materialised models alike.

The default matters because a sync merges into an existing table by matching rows on their primary key. SQL Server's usual default, SQL_Latin1_General_CP1_CI_AS, is case-insensitive: under it two source records whose ids differ only in case — cus_ABC and cus_abc — resolve to the same row, and one silently overwrites the other.

You can still compare or sort differently in your own queries by naming a collation on the column:

SELECT name
FROM WELD_RAW.hubspot__deals
ORDER BY name COLLATE SQL_Latin1_General_CP1_CI_AS

Overriding the collation has consequences worth knowing:

It can slow queries down. A COLLATE in a WHERE or JOIN stops SQL Server using an index on that column, so it scans instead of seeking. On a large table that is the difference between a fast query and a slow one.
Mixing collations in one comparison fails. Comparing a column you have overridden against one you haven't raises Cannot resolve the collation conflict (error 468). You have to name the same collation on both sides.
Case-insensitive matching merges rows Weld keeps apart. A GROUP BY or DISTINCT under a _CI_ collation folds cus_ABC and cus_abc into one group, even though both exist as separate rows.

Prefer overriding per query, as above, rather than altering the columns in Weld's tables — Weld restates a column's full definition, collation included, whenever a schema change touches it.

Primary keys and the 900 byte key limit

SQL Server won't put a MAX-length column in an index key, so Weld narrows primary key columns when it creates a table: nvarchar(max) becomes nvarchar(450) and varbinary(max) becomes varbinary(900). On top of that, the clustered index a primary key creates caps the combined key values of a row at 900 bytes.

Both limits are about the values, not the schema, so a table is created and syncs happily until a row with wide enough key values arrives — the failure surfaces mid-sync, not at setup. If a sync starts failing this way, the fix is a narrower primary key on that stream. Reach out and we'll help you pick one.

Page compression

Enabling Compress the tables Weld creates adds DATA_COMPRESSION = PAGE to every table Weld creates from that point on. Expect roughly 3–5x less disk and fewer reads for queries over those tables, in exchange for around 50% more CPU per sync.

Two things to know:

It only applies to newly created tables. Tables that already exist keep their current setting, so turning it on won't rewrite what you already have.
Not every SQL Server edition can compress data. Weld asks your server directly when the connection is set up, and silently skips compression if the answer is no rather than failing your syncs.

Loading data into SQL Server

Weld loads each batch into a temporary table with a bulk copy, then applies it to the destination table inside a single transaction — so a failed load leaves the destination table as it was.

Three strategies are used, depending on how the sync is configured:

Merge — incremental syncs match on the stream's primary key and update rows in place, inserting the ones that are new.
Append — every row is inserted, without matching against what's already there. Useful for event and log style tables.
Full copy — the destination table is replaced with the current contents of the source.

Destination tables are created automatically and evolve with your source schemas: new columns are added, and a column that disappears from the source is made nullable rather than dropped, so existing rows keep their history.

Using SQL Server as a data warehouse

Set SQL Server as the warehouse behind your Weld Models and your SQL transformations run directly against it. Published models become views in WELD_MODELS; materialised models become real tables.

Grants survive a rebuild. Materialising a model recreates its table, and dropping a table in SQL Server takes its permissions with it. Weld reads the object-level grants first and re-issues them in the same transaction, so anyone you granted access to keeps it. Column-level grants are not reinstated — a materialisation can change the column set underneath them.

Limits

Ad-hoc queries run from Weld's query builder are cancelled after 10 minutes.
Model materialisations are given up to 2 hours 15 minutes before Weld cancels them and marks the run as failed.
A model can be built on at most 20 levels of dependent views.

Using SQL Server with Reverse-ETL

Once connected, SQL Server can be used as the source for Reverse-ETL syncs — build a SQL model against your data and sync the results out to any of Weld's destinations on a schedule.

Something we overlooked?

We are the happiest when you reach out with any comments or questions. It helps us stay in the loop and make the documentation and the product better. Don't hesitate to reach out!