Microsoft SQL Server

Connecting Polytomic to Microsoft SQL Server

Polytomic connects to Microsoft SQL Server using user credentials. While not required, we recommend you create a user exclusively for Polytomic so that your databases audit logs distinguish all Polytomic activity.

In Polytomic, go to Connections → Add Connection → Microsoft SQL Server.
Enter your server host, user credentials, and database name:

Click Save.

Connecting via SSH

Polytomic supports connecting to Microsoft SQL Server via SSH. See Connecting via an SSH tunnel for more information.

Setting up permissions

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

-- Create login at the server level
CREATE LOGIN polytomic_user WITH PASSWORD = 'StrongPassword123!';

-- Grant user access to the specific database
USE [YourDatabase];
CREATE USER polytomic_user FOR LOGIN polytomic_user;

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

Permissions for reading data

-- Grant SELECT permissions on all schemas/tables 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;

-- Grant ability to view schema metadata
GRANT VIEW DEFINITION TO polytomic_user;

Permissions for writing data

-- All basic source permissions above, plus:

-- Grant schema creation and management
GRANT CREATE SCHEMA TO polytomic_user;
GRANT ALTER ANY SCHEMA TO polytomic_user;

-- Grant full table management permissions
GRANT CREATE TABLE TO polytomic_user;
GRANT ALTER ON SCHEMA::dbo TO polytomic_user;
GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO polytomic_user;

-- Grant permissions for staging table operations
GRANT DROP TABLE TO polytomic_user;

-- Additional permissions for bulk operations
GRANT ALTER ANY TABLE TO polytomic_user;  -- For ALTER TABLE ADD/RENAME COLUMN
GRANT CONTROL ON SCHEMA::dbo TO polytomic_user;  -- For TRUNCATE TABLE

-- Note: CONTROL permission on a schema grants all permissions on that schema
-- If you prefer more granular control, grant specific permissions on target schemas only