Azure SQL

Connecting Polytomic to Azure SQL

Polytomic connects to Azure SQL using user credentials. While not required, we recommend you create a user exclusively for Polytomic so that the your database's audit logs distinguish all Polytomic activity.

1. Within your Azure SQL Portal obtain the Server credentials by navigating to the database or managed instance you want to query. On the overview page you'll see a Server name.

2. Within your Azure SQL portal determine the Database you plan to connect to.

3. Create a user within your Azure SQL Portal and save the Username and Password for later use.

4. In Polytomic, go to Connections → Add Connection → Azure SQL.

5. Enter your Azure SQL credentials in Polytomic's Server, Username, Password, Database, and Port fields.

6. Click Save.

Connecting via SSH

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

Faster bulk writes

For faster bulk writes into your Azure SQL database, you should turn on the Use Azure blob storage for faster bulk loading option:

This option requires two prerequisites:

1. Set up an Azure Blog Storage bucket and enter its access key credentials (see instructions).
2. Create a master key password on the Azure SQL database you're syncing to. See Microsoft's instructions here .

Once done, click Save. With this option on, Polytomic will take a faster path when syncing data to your Azure SQL database.

Setting up permissions

We recommend creating a dedicated user for Polytomic to ensure proper access control and audit trail visibility.

Creating a contained database user

Azure SQL Database uses contained database users (no server-level login required):

-- Connect to your target database
-- Create a dedicated user for Polytomic
CREATE USER polytomic_user WITH PASSWORD = 'StrongPassword123!';

Permissions for reading data

For using Azure SQL as a data source, grant these minimum permissions:

-- Grant SELECT permissions on target schemas
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

For using Azure SQL as a destination with bulk sync operations:

-- Basic permissions for reading and metadata
GRANT SELECT ON SCHEMA::dbo TO polytomic_user;
GRANT VIEW DEFINITION TO polytomic_user;

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

-- 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;

-- Permissions for staging/temporary table operations
GRANT DROP TABLE TO polytomic_user;

-- Additional permissions for bulk sync operations
GRANT ALTER ANY TABLE TO polytomic_user;  -- For schema migrations
GRANT CONTROL ON SCHEMA::dbo TO polytomic_user;  -- For TRUNCATE operations

-- Required for merge operations
GRANT SELECT ON SCHEMA::dbo TO polytomic_user;

📘

Permissions for bulk syncing to Azure SQL

During bulk syncs, Polytomic creates temporary staging tables, performs schema migrations, and executes merge operations to synchronize data. This requires permission to create and drop schemas and tables.

Limiting permissions to specific schemas

If you want to restrict Polytomic to specific schemas only:

-- Create a custom schema for Polytomic
CREATE SCHEMA polytomic_sync;

-- Grant full control on the specific schema only
GRANT CONTROL ON SCHEMA::polytomic_sync TO polytomic_user;

-- Grant minimal permissions on other required schemas
GRANT SELECT ON SCHEMA::dbo TO polytomic_user;