SQL Server

Search…

SQL Server

This page describes how to configure SQL Server credentials for use by Census and why those permissions are needed.

Required Permissions
These instructions are well tested to connect Census to SQL Server. If you're running into connection issues or missing tables or views, please confirm you've run all of these instructions. 
Census reads data from one or more tables (possibly across different schemata) in your database and publishes it to the corresponding objects in external systems such as Salesforce. 
We recommend you create a dedicated CENSUS user account with a strong, unique password. Census uses this account to connect to your SQL Server database. In order for the Census connection to work correctly, the CENSUS account must have these permissions:
Read-only access to any tables and views in any schemata that you would like Census to publish to your service destinations.
SQL Server permissions are complex and there are many ways to configure access for Census. The script below has been tested with recent SQL Server versions and is known to work correctly:
1
USE <your database>;
2
-- Create census user the ability to sign in with a password
3
CREATE LOGIN CENSUS WITH PASSWORD = '<strong, unique password>';
4
​
5
-- Give the census user the ability to login
6
CREATE USER CENSUS FOR LOGIN CENSUS;
7
​
8
-- Give the census user the ability to connect to database
9
GRANT CONNECT TO CENSUS;
10
​
11
-- Give the census user the ability to read all tables
12
EXEC sp_addrolemember 'db_datareader', CENSUS;
13
​
14
-- Grant census user ability to read schema and data
15
GRANT SELECT, VIEW DEFINITION ON SCHEMA::<your schema> TO CENSUS;
Copied!
💡 Notes
If you have multiple schemata that you would like Census to read from, repeat the steps for "<your schema>" for each of them
All sync behavior will be Read Only, meaning that every sync will be a full sync because there is no way to write or modify the SQL Server schema
We based our connection protocol on SQL Server SQL JDBC driver​
​
🚦Allowed IP Addresses
Census will always connect to your data warehouse from of these static IP addresses located within AWS:
34.216.163.241
54.212.243.205
​

⤵️ Data Sources - Previous

Snowflake

Next - Destinations (app)

ActiveCampaign

Last modified 19d ago

Copy link

Contents

Required Permissions

💡 Notes

🚦Allowed IP Addresses