Connect SPSync to SQL Server

This guide covers SQL Server you run yourself (on-premises, on an Azure virtual machine, or Azure Arc-enabled SQL Server) and Amazon RDS for SQL Server. For Azure SQL Database and Azure SQL Managed Instance, read Azure SQL.

Before you start

SQL Server with SQL authentication turned on (mixed mode). SPSync signs in with a login and password.
ODBC Driver 17 or 18 for SQL Server on the machine that runs SPSync. The desktop app and the Azure managed application include it. On a machine where you run the CLI, install Microsoft’s driver first.
A database that SPSync can own tables in. A dedicated database is the easy way to keep SPSync’s tables separate from everything else.

Create the database and a least-privilege user

The script below creates a database, a login, and a user that can create and use tables in two schemas and nothing else. sync is the schema SPSync loads into (the dataset_name) and sync_staging is the schema SPSync uses while it merges a round into the main tables. Run it as a member of sysadmin or equivalent. It comes from the SQL Server documentation and has not been run here against a test instance.

CREATE DATABASE spsync;
GO
CREATE LOGIN spsync_loader WITH PASSWORD = 'use-a-long-random-password', CHECK_POLICY = ON;
GO
USE spsync;
GO
CREATE USER spsync_loader FOR LOGIN spsync_loader;
GO
CREATE SCHEMA sync AUTHORIZATION spsync_loader;
GO
CREATE SCHEMA sync_staging AUTHORIZATION spsync_loader;
GO
GRANT CREATE TABLE, CREATE VIEW TO spsync_loader;
GO

Owning both schemas lets the login create, alter and drop tables in them. CREATE VIEW is only used if you switch on reporting views. Leave it out if you do not.

Allow SPSync to reach it

SPSync opens an outbound connection to the server on TCP port 1433, or the port you configured. Where SPSync runs decides what you open.

Desktop app or a self-hosted server: the connection starts from that machine, so a private address, a VPN or a local firewall rule for that machine is enough.
On-premises or an Azure virtual machine: allow inbound TCP 1433 from the machine that runs SPSync only. On an Azure VM, that is a rule on the network security group attached to the VM’s network interface or subnet.
Azure Arc-enabled SQL Server: the instance sits on your own network, so the on-premises rules apply.
Amazon RDS for SQL Server: add an inbound rule for port 1433 to the instance’s security group. Use the source address of the machine that runs SPSync, written as /32.
Azure managed application: it runs in your own Azure subscription. Allow the address it connects out from instead of opening the server to the internet, or put the server on the same virtual network.

Encrypt the connection

Add Encrypt=yes to the connection string. A self-hosted server usually has a self-signed certificate. Either install a certificate from a CA the SPSync machine already trusts, or add TrustServerCertificate=yes and accept that SPSync then encrypts the connection without checking who answered.

Amazon RDS for SQL Server presents a certificate that does not chain to a public root. Use Encrypt=yes and either install the RDS CA bundle in the SPSync machine’s trust store or add TrustServerCertificate=yes.

Authentication SPSync supports today

SQL authentication: a login and password in the connection string. That is the only method SPSync can use for this destination today. Windows integrated authentication and Microsoft Entra ID sign-in are not supported, and there is no passwordless option.

Connect from SPSync

The connection string has this shape, where HOST is your server’s name:

mssql://spsync_loader:PASSWORD@HOST:1433/spsync?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes

Desktop app: open the Destination screen, choose SQL Server / Azure SQL, paste the string into Connection string and choose Test connection. Table prefix is optional.

CLI: put the string in an environment variable and name the variable when you write the config:

export SPSYNC_DSN='mssql://spsync_loader:PASSWORD@HOST:1433/spsync?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes'
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind mssql --connection-string-env SPSYNC_DSN --dataset-name sync \
  --client-id <entra-app-client-id>

--dataset-name is the schema you created above.

AI assistant: with the admin MCP server connected, ask the assistant for the destination requirements, then let it create the destination. You type the connection string into the secure entry page, never into the chat.

What Test connection and doctor check

Both open a real connection with the string you gave and confirm the server accepts the login. That proves the host is reachable, the driver is installed, TLS negotiated and the password is right. It does not prove the login can create tables. A missing grant shows up on the first sync, as a permission error naming the object.

spsync doctor runs this check as destination-connectivity alongside its SharePoint checks.

Common errors

What you see	What it means and the fix
`Can't open lib 'ODBC Driver 18 for SQL Server'`	The driver is not installed on the machine running SPSync. Install ODBC Driver 18, or change `driver=` to the version you have.
`Login failed for user`	Wrong password, the login is disabled, or the server only allows Windows authentication. Turn on mixed mode.
`SSL Provider: certificate verify failed`	The server certificate is self-signed or from a CA the machine does not trust. Install the CA, or add `TrustServerCertificate=yes`.
`TCP Provider: Timeout error` or `Named Pipes Provider`	A firewall or security group is blocking port 1433, or the host name is wrong.
`The CREATE TABLE permission was denied`	The user is missing the grants above. Run the `GRANT` and `CREATE SCHEMA ... AUTHORIZATION` lines again.