Connect SPSync to Azure SQL

This guide covers Azure SQL Database and Azure SQL Managed Instance. For SQL Server you run yourself, or Amazon RDS for SQL Server, read SQL Server. SPSync treats all of them as the same destination kind, mssql.

Before you start

An Azure SQL database, or a Managed Instance with a database in it.
SQL authentication allowed on the server. If the server is set to Microsoft Entra-only authentication, SPSync cannot sign in; see “Authentication SPSync supports today”.
ODBC Driver 17 or 18 for SQL Server on the machine that runs SPSync. The desktop app and the Azure managed application include it.

Create the database and a least-privilege user

Connect to the database (not master) as the server admin. On Azure SQL Database a contained user needs no server login. The script below is from the Azure SQL documentation and has not been run here against a test database.

CREATE USER spsync_loader WITH PASSWORD = 'use-a-long-random-password';
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

sync is the schema SPSync loads into. sync_staging is where SPSync stages a round before merging it. CREATE VIEW is only needed for reporting views.

On a Managed Instance you can use the same contained user in a database, or create a login in master and a user for it, as on SQL Server.

Allow SPSync to reach it

Azure SQL Database: on the logical server, open Networking, then Public access and add a firewall rule with the address SPSync connects from. A database-level rule works too. For the Azure managed application, allow the address it connects out from. Tick “Allow Azure services and resources to access this server” only if you accept every Azure customer’s traffic reaching the firewall.
Private endpoint: if the server has public access off, SPSync must run where the private endpoint resolves, for example the desktop app on a machine in your network or on a VPN.
Managed Instance: the instance lives in your virtual network. Open the public endpoint (port 3342) and allow it in the subnet’s network security group from SPSync’s address, or connect over the private endpoint from inside the network.

Encrypt the connection

Azure SQL requires an encrypted connection. Put Encrypt=yes in the string anyway, so it does not depend on a driver default. The server’s certificate chains to a public CA, so you do not need TrustServerCertificate=yes.

Authentication SPSync supports today

SQL authentication: a user name and password in the connection string. Microsoft Entra ID authentication, including a service principal or a managed identity, is not supported for this destination today, and neither is a passwordless connection. Using it would need a change to how SPSync builds the connection, which is not scheduled.

Connect from SPSync

Replace HOST with <yourserver>.database.windows.net.

mssql://spsync_loader:PASSWORD@HOST:1433/yourdatabase?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.

CLI:

export SPSYNC_DSN='mssql://spsync_loader:PASSWORD@HOST:1433/yourdatabase?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>

AI assistant: with the admin MCP server connected, the assistant reads the requirements and creates the destination. You enter the string on the secure entry page.

What Test connection and doctor check

They open a real connection with your string. A pass means the firewall lets SPSync in, the password is right and TLS worked. It does not prove the user can create tables. spsync doctor reports the same probe as destination-connectivity.

An Azure SQL Database in a paused serverless tier takes a while to resume. The first attempt can fail with a timeout while the database wakes up. Try again after a minute.

Common errors

What you see	What it means and the fix
`Cannot open server ... requested by the login. Client with IP address ... is not allowed to access the server`	The firewall rule is missing. Add the address in the message to the server’s firewall rules.
`Login failed for user`	Wrong password, or the user is not in this database. Connect to the database you named, not `master`.
`Login failed for user '<token-identified principal>'`	The server is Entra-only. Turn SQL authentication on, or use a different destination.
`Database ... is not currently available`	A serverless database is resuming. Wait and test again.
`The CREATE TABLE permission was denied`	Run the `GRANT` and `CREATE SCHEMA` lines above in that database.