Skip to main content

SQL Server and ODBC setup

The Gateway reads your database with a login you give it, and works out how to detect changes per table (see Change detection). This page is the one-time preparation a DBA does before the Gateway is connected.

SQL Server​

Create a read-only login​

Don't use sa or a privileged login. Create a login for the Gateway and give it read access to the database:

USE master;
GO
CREATE LOGIN cloudconnector WITH PASSWORD = 'use-a-strong-password', CHECK_POLICY = ON;
GO

USE ERP_Production;
GO
CREATE USER cloudconnector FOR LOGIN cloudconnector;
ALTER ROLE db_datareader ADD MEMBER cloudconnector;
GO

For Windows authentication, create the login FROM WINDOWS for the account the Gateway connects as instead. The Gateway service runs as NT SERVICE\CloudConnectorGateway.

Incremental syncs use SQL Server Change Tracking when a table has it. It records which rows changed, including deletions, with little overhead, and it is the only strategy that carries deletes on every sync. Without it, the Gateway falls back to a watermark column or a snapshot, and finds deletions with a periodic key check.

Enable it once on the database, then on every table you want to sync:

ALTER DATABASE ERP_Production
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
GO

USE ERP_Production;
GO
ALTER TABLE dbo.Customers ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
ALTER TABLE dbo.Orders    ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
GO

Change Tracking needs a primary key on the table. Keep the retention comfortably longer than the longest gap you expect between syncs.

The Gateway's login also needs permission to read the change information:

USE ERP_Production;
GO
GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO cloudconnector;
GO

Allow network access​

The Gateway connects over the SQL Server TCP port (default 1433):

  • In SQL Server Configuration Manager, make sure TCP/IP is enabled for the instance, and restart SQL Server after enabling it.
  • Allow the Gateway host through the Windows Firewall on the SQL Server.

Test from the Gateway host before configuring the Gateway:

sqlcmd -S "sql01.contoso.local" -U cloudconnector -P "<password>" -d ERP_Production `
  -Q "SELECT @@VERSION; SELECT CHANGE_TRACKING_CURRENT_VERSION();"

A version banner and a non-null Change Tracking version mean both the login and Change Tracking are in place.

ODBC sources​

Any database with a 64-bit ODBC driver can be read through ODBC.

  • Create a 64-bit System DSN on the Gateway host, in the 64-bit ODBC Data Sources tool.
  • A 32-bit driver cannot be used.
  • A User DSN is invisible to the service: it tests green in the Setup UI and then every sync fails with "data source name not found".
  • In the Gateway's Setup UI, Check driver compatibility reports what the driver can and cannot do.
  • Drivers for some ERP systems allow only one session: set Max concurrent connections to 1 for that instance. See Gateway settings.
  • When the driver reports no primary key, supply the key columns under Manage source table integration in the Data catalogue. For very large tables, Read in windows splits the read by ranges of an indexed watermark column. See On-prem tables.

Next​

Install and connect the Gateway, then discover and assign tables.