Who can use this feature?
- Global admins or users with advanced permissions
- Available on all plans
A connection is an authentication mechanism to allow Totango to integrate with another system. Each connector has different requirements for connection. You can reuse connections to support multiple integration jobs.
Data warehouse integrations are a closed system; you cannot reuse data warehouse connections for other integration families.
A MS SQL Server connector is a pure database connection; it is built as a connector and an SQL query powers the connection.
1. Create a Microsoft SQL Server user
We recommend that you create a specific user for fetching data with proper database access permissions (e.g. read-only access to a specific schema or a table). MS SQL doesn’t support a read-only user by default, so you should explicitly create it.
- Connect to your MS SQL Server database.
- Create a user with authentication within database (learn more).
CREATE USER ‘totango_ro_user’ WITH PASSWORD = ‘password’;
- Create a role under your database with read-only authorization (learn more).
CREATE ROLE ‘role_name’ AUTHORIZATION 'totango_ro_user';
- Give the user access to the database from any host (learn 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';
2. Create a Microsoft SQL Server connection in Totango
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.
In case your company uses VPN to allow access to your MS SQL Server database, the Totango system will not be able to access it. If you want the Totango system to retrieve data from an MS SQL Server database, please create an accessible MS SQL Server database outside your network and push only the relevant information to this MS SQL Server database in a secure manner.
- In Totango, click Settings from the left nav.
- Expand Data Management > Customer Data Hub.
- From the list of active connectors (left), choose Data Warehouses.
- From the list of available connectors, hover over Microsoft SQL Server, and click Connect (or View Integrations).
- Enter a name and description for the connection. Because you can have multiple connections with a connector (using different authentication profiles), a name and description helps you identify the connection later.
- Add the remaining details for the connection:
- Host name: Host name of your MSSQL Server instance
- Port: Port of the MSSQL Server instance
- Username: Username of the user you plan to give access to your database for Totango
- Password: Password of the user
- Schema: Schema where this connector data should be fetched from
- Database: Database where this connector data should be fetched from
- Create SSH Tunnel: Learn more
- Click Connect.
You can now set up an integration using the connection.
All data warehouse connectors support SSL encryption. By default, Totango uses SSL encryption during connecting. If the data warehouse server does not support SSL encryption, the connection will use an unencrypted connection to stream data from the data warehouse.
FAQs
Question: How does Totango connect to the MSSQL Server database?
Answer: Totango connects via JDBC (Java Database Connectivity) using JDBC 8.2 driver (learn more).
Question: JDBC supports a read-only connection mode, is that being used for the MSSQL Server connector?
Answer: 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 database.
Question: Which MS SQL Server version are supported?
Answer: 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.