In modern technology, there is a clear need to reuse objects and let the user configure each object once and repeatedly use it over and over again.
This is the basic assumption of the “Connection” configuration in Totango Customer Data Hub.
Connection to a data source is usually done by Ops or Data teams, and the main idea is that it will be configured once and you can reuse the same connection for several integrations.
Important note, the Data Warehouse integration family is a closed system, therefore, the data source connectors which are created can be re-used for only Data Warehouse integrations and not for other integration families.
Topics covered in this article:
- Creating MS SQL Server Connection
- Configuring MS SQL Server Connection
- Customer Data Hub Whitelisting
- SSL Encryption
Creating MS SQL Server Connection
- Go to your Totango instance and click on Global Settings > Data Management > Customer Data Hub > Click 'Data Warehouse' and select MS SQL Server
(note: you will need Totango admin privileges).
- Create a new connection (or review your existing connections)
Configure MSSQL Server Connection
The MSSQL Server connector is a pure DB connection. It means that it is built as a connector and an SQL query which powers this connection.
Note: It is recommended to create a specific user for fetching data from DB with proper DB access permissions (e.g. user with only-read access to a specific schema or a table).
Creating MSSQL Server connector includes 2 steps:
- Create a read-only user with access to the desired schema
- Create connector in Totango
Create an MSSQL Server User
MS SQL doesn’t support a read-only user by default, so we should explicitly create it.
Follow these steps to create a read-only user:
- Connect to your MSSQL Server DB
- Create a user with authentication within Database (read more)
CREATE USER ‘totango_ro_user’ WITH PASSWORD = ‘password’;
- Create a role under your database with read-only authorization (read more):
CREATE ROLE ‘role_name’ AUTHORIZATION 'totango_ro_user';
- Give the user access to the database from any host (read more):
GRANT SELECT ON your_database.table_name TO 'totango_ro_user';
If you know the hostname or IP address of the host that the collector is will be installed on, type the following command:
GRANT SELECT ON your_database.table_name to 'totango_ro_user'@'hostname or IP_address';More information about user creation can be found here.
Create Connector in Totango
- Name: a meaningful connection name to the MSSQL Server connection, For example, “Company DWH - Customer data”
- Description: a meaningful description to help you and others understand the essence of this connection.
- Hostname: Hostname of your MSSQL Server instance.
- Username: username of the user you plan to give access to your DB for Totango App
- Password: password of your user
- Schema: The schema where this connector data should be fetched from
- Database: The database where this connector data should be fetched from
In case your company uses VPN to allow access to your MSSQL Server database, the Totango system will not be able to access it. If you want the Totango system to retrieve data from an MSSQL Server database, please create an accessible MSSQL Server database outside your network and push only the relevant information to this MSSQL Server database in a secure manner.
MSSQL Server Connection Q & A
- Q: How does Totango connect to the MSSQL Server database?
A: Totango connects via JDBC (Java Database Connectivity) using JDBC 8.2 driver (read more).
- Q: JDBC supports a read-only connection mode, is that being used for the MSSQL Server connector?
A: Yes. Totango Customer Data Hub is using a read-only mode on the connector level (we do not allow any queries with delete, insert, update, ...). We advise setting a read-only role for the user used to access the DB.
- Q: Can I change the MSSQL Server database port?
A: Yes. you can define any port number.
- Q: Which MSSQL Server version are supported?
A: Totango is connected to MSSQL Server via JDBC 8.2 driver. JDBC 8.2 driver supports these MSSQL Server versions: SQL Server 2012, Azure SQL Database, PDW 2008R2 AU3, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019.
Customer Data Hub Whitelisting
In the event that your network is behind a firewall, you will need to whitelist our servers so that we may retrieve information from your data warehouse.
Please follow the instructions in this Customer Data Hub IP Whitelisting article to configure your IP whitelist.
All data warehouse connectors support SSL encryption out-of-the-box (no need to configure anything to enable SSL encryption).
Totango connectors will use an SSL encrypted connection in case it is activated and use a non-SSL encrypted connection otherwise.
The implementation logic is as follows:
by default, SSL encryption is used during connecting, if the data warehouse server does not support SSL encryption, then the connection will using an unencrypted connection to stream data from the data warehouse.
Read more on how to configure SSH tunnel for MSSQL