Microsoft SQL Server

📘

Whitelist IPs

If your database's network-access policy requires you to allowlist Polytomic's IP addresses, see Whitelist IPs.

Connecting Polytomic to Microsoft SQL Server

Polytomic connects to Microsoft SQL Server using SQL Server authentication (a login with a username and password). Windows authentication and Microsoft Entra ID authentication aren't supported. While not required, we recommend you create a login exclusively for Polytomic so that your database's audit logs distinguish all Polytomic activity. See Setting up permissions below.

This connection type works with self-managed SQL Server, Amazon RDS for SQL Server, and Azure SQL Managed Instance. For Azure SQL Database, use the Azure SQL connection type, unless you need CDC (see CDC replication).

1. In Polytomic, go to Connections → Add connection → Microsoft SQL Server.

2. Enter your server host, port (default 1433), user credentials, and database name:

3. Configure encryption (see Encryption and certificates below).

4. Optionally, check Use change data capture for bulk syncs to read changes from SQL Server's change data capture (CDC) tables instead of scanning your tables. See CDC replication for the required database setup.

5. Click Save.

Encryption and certificates

With Trust server certificate off, Polytomic verifies that a trusted certificate authority signed your server's certificate and that the certificate matches the hostname you entered. Polytomic trusts public certificate authorities and the Amazon RDS certificate authorities. Amazon RDS for SQL Server and Azure SQL Managed Instance connect without additional configuration as long as you use the endpoint hostname.

If SQL Server isn't configured with a certificate, it generates a self-signed certificate at startup that Polytomic can't verify. In that case, or if a private certificate authority issued your certificate, Polytomic reports an error saying the server certificate could not be verified. To fix it, do one of the following:

• Upload your certificate authority's certificate in the CA certificate field.
• Install a certificate from a trusted certificate authority on SQL Server.
• Check Trust server certificate.

🚧

Strict encryption

Polytomic connects using TDS 7.x encryption. If you've enabled Force Strict Encryption (TDS 8.0) on SQL Server 2022 or later, Polytomic can't connect.

Connecting via SSH

Polytomic supports connecting to Microsoft SQL Server via SSH. See Connecting via an SSH tunnel for more information. When connecting through an SSH tunnel, Polytomic still verifies the server certificate against the hostname you entered.

Setting up permissions

We recommend creating a dedicated SQL Server login and database user for Polytomic to maintain clear audit trails and follow security best practices.

-- Create a login at the server level
CREATE LOGIN polytomic_user WITH PASSWORD = '<strong password>';

-- Create a user for the login in the database Polytomic will connect to
USE [YourDatabase];
CREATE USER polytomic_user FOR LOGIN polytomic_user;

The permissions required depend on whether you're using Microsoft SQL Server as a source (reading data), a destination (writing data), or both.

Permissions for reading data

-- Grant SELECT on each schema to be synced
GRANT SELECT ON SCHEMA::dbo TO polytomic_user;

-- Or grant SELECT on specific tables:
-- GRANT SELECT ON dbo.TableName TO polytomic_user;

-- Allow Polytomic to read table and column metadata
GRANT VIEW DEFINITION TO polytomic_user;

If you're using CDC, see CDC replication for the additional setup it requires.

Permissions for writing data

When writing, Polytomic creates schemas and tables, and adds and renames columns. It loads data through staging tables that it creates, renames, and drops. It truncates tables when you request a full resync. In SQL Server, ALTER permission on a schema covers altering, renaming, truncating, and dropping the tables in that schema.

-- All read permissions above, plus:

-- Allow Polytomic to create new schemas. Polytomic owns schemas it creates.
GRANT CREATE SCHEMA TO polytomic_user;

-- Allow Polytomic to create tables (also requires ALTER on the target schema)
GRANT CREATE TABLE TO polytomic_user;

-- For each existing schema Polytomic writes to:
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO polytomic_user;

If you'd rather confine Polytomic to its own schema, create the schema with Polytomic as its owner and grant CREATE TABLE:

CREATE SCHEMA polytomic AUTHORIZATION polytomic_user;
GRANT CREATE TABLE TO polytomic_user;