Microsoft SQL Server

This page describes how to sync data into your Microsoft SQL Server instance.

๐Ÿƒโ€โ™€๏ธ Getting Started

This guide will walk you through connecting to Microsoft SQL Server as a destination.

If you are trying to use Microsoft SQL Service as a data source (to query data from SQL Server and sync to elsewhere), that process is documented separately here: Microsoft SQL Server Data Source

  1. Visit the Destinations page and click + New Destination.

  2. Select Microsoft SQL Server from the menu.

  3. Enter the requested database credentials:

CredentialDescription

Hostname

Host name or IP address of database

Port

Port of database (1433 by default for SQL Server)

Database Name

Name of database within SQL Server to connect to

Username

Username Census will use to connect

Password

Password Census will use to connect

Number of Client Connections

Value between 1 and 8 (default is 1). This is the maximum number of concurrent connections Census will use to connect to database. The default should be fine in most cases, but increasing this value can increase throughput on very large syncs.

Use SSH Tunnel

Default: Off - Toggle on to indicate that Census should connect via an SSH Tunnel. For more information, see Network Access Controls

SSH Hostname

Hostname of the Census accessible SSH Tunnel bastion.

SSH Port

Port of SSH Tunnel bastion.

SSH Username

Username Census will use to connect to bastion.

๐Ÿ”‘ Permissions

To use SQL Server as a destination, Census requires permission to write to the desired destination tables, as well as read metadata about the table and database structures.

-- Note that creating a user may be redundant if you're already configured
-- SQL Server as a source.

-- Create census user the ability to sign in with a password
CREATE USER CENSUS WITH PASSWORD = '<strong, unique password>';

-- Give the census user the ability to connect to database
GRANT CONNECT TO CENSUS;

-- Give the census user the ability to read table within the database
-- Note: census user just will have the ability to gain explicit permissions
-- in the following command
EXEC sp_addrolemember 'db_datareader', CENSUS;

-- Grant census user ability to read data from within a schema
-- Note: this can also be granted to specific tables as well
GRANT SELECT, VIEW DEFINITION ON SCHEMA::<schema> TO CENSUS;

-- Grant census user ability to write data to tables within a schema
-- Note: this can also be granted to specific tables as well
GRANT INSERT, UPDATE ON SCHEMA::<schema> TO CENSUS;

๐Ÿ”€ Supported Objects and Behaviors

Object Name

Supported?

Sync Keys

Behaviors

Table

โœ…

Primary keys or columns with uniqueness constraints

Update or Create, Update Only

Contact us if you want Census to support more sync behaviors for SQL Server.

๐ŸšฆAdvanced Network Configuration

Census can successfully connect to SQL Server instances that are using advanced networking controls including region constraints, IP address allow lists, or SSH Tunneling. For more information, see our Network Access Controls documentation.

๐Ÿš‘ Need help connecting to SQL Server?

Contact us via support@getcensus.com or start a conversation with us via the in-app chat.

Last updated