Third-Party Connection to Intelligence Central
A Toad Intelligence Central user can create a MySQL connection to Toad Intelligence Central through a third-party tool, such as one of the following:
- Tableau
- MySQL command line interface
- MySQL Workbench
This connection can be used to query tables, views, snapshots and datasets stored in Toad Intelligence Central.
Requirements / Limitations
| Third party tool | Ensure the authentication method you use to connect to Toad Intelligence Central is supported by the third party tool. This is straightforward if you login to Toad Intelligence Central using credentials local to Toad Intelligence Central. If you login to Toad Intelligence Central using Windows credentials then the third party tool must also support MySQL connection via Windows credentials. |
|
Computer where the third party tool is installed |
Ensure a MySQL driver is installed appropriate to the third party tool and Toad Intelligence Central. Ensure the default character set is UTF8. |
| Toad Intelligence Central Share / Manage rights | To see an object that you did not create yourself you must have Share or Manage rights to that object. These rights can be granted by any Toad Intelligence Central user with Manage rights to the object using the Web interface or an application such as Toad Data Point. |
| Data source authentication | Ensure the data source authentication you use to access the result set of a table or view is defined in Toad Intelligence Central. This can be done using the Web interface (Web server) or Toad Data Point. It cannot be done using the third party tool. |
Connection
Create the MySQL connection.
| Parameter | Description |
|---|---|
| Host name |
The name of the Toad Intelligence Central server host. |
| Port |
The connection port to the Toad Intelligence Central server. This is the Port number set during installation of the Toad Intelligence Central server, by default 3566. |
| Username and Password |
Your username and password to Toad Intelligence Central. Alternatively, if you use Windows credentials to connect to Toad Intelligence Central then select the option to connect via Windows credentials. |
MySQL database
Select the MySQL database where the object is stored. This is dependent on the application used to create the object.
| Application | Description | ||
|---|---|---|---|
| Toad Data Point |
The database is defined when the object is published to Toad Intelligence Central. | ||
| Admin Console |
For a table, view or snapshot the MySQL database is the name of the data source. For a dataset the database is of the form data_owner. The owner is the user that created the dataset.
|
|
|
Note: Hidden, temporary and internal Toad Intelligence Central databases and MySQL databases may be visible. |
Queries
Query tables, views, snapshots and datasets.
|
|
Note: When querying tables and views, execution of SQL may fail under some circumstances. If the SQL used by the third party tool has a MySQL dialect, then that SQL may fail if the query is shipped to a non-MySQL relational database for remote execution. As a workaround, you could use Toad Data Point to save the table or view as a snapshot and then access the snapshot via the third party tool. |
MySQL Views
You can use the following MySQL commands to verify the third party tool.
- SHOW [FULL] TABLES command
- DESC[RIBE] command
- EXPLAIN command
- SHOW [FULL] COLUMNS command
- SELECT query against information_schema.views
- SELECT query against information_schema.tables
Connecting to Toad Intelligence Central from Excel (2019 and later) file
To create the connection:
-
Create a self-signed certificate or obtain a certificate from Certification Authority for Toad Intelligence Central (TIC) URL or IP address
-
Install the certificate in Trusted Root Certification Authorities store on the server where Toad Intelligence Central (TIC) is installed
-
Bind the certificate to port 8066 by running one of the following commands in elevated
- If connecting to TIC via IP address use the following PowerShell command:
netsh http add sslcert ipport={tic IP}:8066 certhash={certificate thumbprint} appid={00112233-4455-6677-8899-AABBCCDDEEFF}- If connecting to TIC via the domain name use the following PowerShell command:
http add sslcert hostnameport={tic URL}:8066 certhash={certificate thumbprint} appid={00112233-4455-6677-8899-AABBCCDDEEFF} certstorename=MY
Note:
{tic IP}replace with IP address e.g., 127.0.0.1{tic URL}replace with TIC URL e.g., ticserver.mydomain.com{certificate thumbprint}replace with certificate thumbprint, which can be viewed in Certificate Manager in Windows by opening the certificate, switching to the details tab, and copying the value for the Thumbprint property
-
If using a self-signed certificate, it needs to be installed in Trusted Root Certification Authorities store on every client that needs to connect to TIC from an Excel file; alternative is to set up Domain Controller to distribute the certificate to all clients
-
From TIC, select the desired object and note the name and database properties in the details panel
-
From Excel, go to the Data tab and select Get Data | From Other Sources | From Web
-
Type the URL as follows (replace text in bold with appropriate values) and press OK:
https://{tic URL/IP}:8066/tic/v1/artifacts/`{database}`.`{name}`/content -
In the next dialog, select Windows or Basic authentication, depending on what authentication method is used to connect to TIC
-
After this is done, data can be imported and refreshed on demand
Add New Users and Groups
New users and groups can be added to Toad Intelligence Central. Which user role is required before adding new users is dependent on how Intelligence Central is configured.
Use the following icons to identify the type of user or group wherever users/groups are displayed, for example the
Types of Users
| Icon | Description |
|---|---|
 |
Active Directory® Users To self register from a web browser, click Register as a new user at the login prompt. The computer you use to login to Toad Intelligence Central must be accessible to your Windows® domain. This option is visible only if your local configuration allows self registration via a web browser. |
 |
Intelligence Central Users To self register from a web browser, click Register as a new user at the login prompt. Deselect the option to Use my Active Directory details. Fill in your user details. The options in this procedure may not be visible or selectable, depending on your local configuration. Your local configuration may not allow self registration from a web browser or registration of new Intelligence Central users. Following login to Intelligence Central from a web browser, to add users, select Administration | Users | Add User | Create Intelligence Central user. The availability of these steps depends on your Intelligence Central configuration. |
Types of Groups
| Icon | Description |
|---|---|
 |
Active Directory Groups Following login to Intelligence Central from a web browser, to add a group from Active Directory, select Administration | Groups | Add Group | Add Active Directory Group. Your Intelligence Central configuration may require login with an Administrator role to add Active Directory groups. |
 |
Intelligence Central Groups Any logged in user can create an Intelligence Central group. For more information, see Create Intelligence Central Groups. |
Add Active Directory Users/Groups
Users with certain privileges can add new users and groups to Toad Intelligence Central by importing users/groups from Active Directory®. Which type of user (role) is allowed to add new users is dependent on how Intelligence Central is configured.
To add Active Directory User or Group using the Web interface
- Use a web browser to log in to Intelligence Central. For more information, see Open the Web Console.
- Click Administration and then select either Users or Groups.
- Then select Add User | Add Active Directory User or Add Group | Add Active Directory Group.
- If the Authenticate to Active Directory dialog opens, enter the credentials for your Active Directory account (including the domain name) in order to access a list of domains and users. See Authenticate to Active Directory for more information.
- In the Add Users and Groups from Active Directory dialog, select the users/groups to add to Intelligence Central. See Add from Active Directory for more information.
Requirements
- The Toad Intelligence Central server host must be in a valid Windows® domain.
- If the Windows domain of the Toad Intelligence Central host and the Windows domain of the user are different then a two-way trust relationship must exist between the Windows domains.
Known issues and Limitations
Users and groups imported from Active Directory are given the same name in Intelligence Central.
Review the following known issues and limitations.
| Issue | More information |
|---|---|
|
Duplicate names |
If an Active Directory name already exists on Toad Intelligence Central then that Active Directory name is not imported. You will be notified of any users and groups who cannot be imported. |
|
Long Active Directory usernames |
For Active Directory usernames longer than 16 characters, the Intelligence Central username will be a truncated form of the Active Directory name. If the Active Directory username has a numeric suffix then up to three digits of that numeric suffix is preserved. For example, Active Directory username very_long_username12 transforms to Intelligence Central username very_long_user12. |
|
Active Directory names containing characters not allowed by Toad Intelligence Central |
Active Directory names containing the following characters cannot be imported to Intelligence Central. You will be notified of any users and groups who cannot be imported.
|
|
Active Directory supports sub groups (groups within groups). Intelligence Central does not. |
When importing an Active Directory group with sub groups the sub groups and users in sub groups are not imported to Intelligence Central. |
Authenticate to Active Directory
Use the Authenticate to Active Directory dialog to enter your Active Directory credentials in order to access a list of domains and users when attempting to add Active Directory users (or groups) to Intelligence Central.
To authenticate to Active Directory
-
In the Authenticate dialog, enter the following information:
Field Description Domain A domain in the Active Directory forest. Username Your username to that domain. Password Your password to that domain. -
Remember these credentials on server for future Active Directory access and synchronization—Select this option to instruct Intelligence Central to do the following:
-
Automatically and regularly synchronize imported Active Directory users and groups. For more information, see Synchronize Active Directory.
- Cache Active Directory user and group data on Toad Intelligence Central. This has the potential to improve performance of Toad Intelligence Central when looking up Active Directory users and groups.
If you do not select this option, the list of imported users and groups might not be kept in sync with Active Directory.
To view a list of domains configured for Active Directory synchronization, in the Web interface go to Administration | Server | Settings. Log in with Administrator role is required to view this information. You can also change the credentials used for synchronization from this page. See Synchronize Active Directory for more information.
-
- Schedule AD synchronization. If you selected Remember these credentials..., you can schedule the Active Directory synchronization. Enter the scheduling details in the fields provided. After scheduling a synchronization, the scheduling information and status are displayed in Administration | Server | Settings.