Connecting Analyst Studio to your database

Getting ready to connect

Analyst Studio can connect to most popular databases that can be queried using SQL or a SQL-like language, including databases hosted on private networks or on private machines, databases hosted in VPCs and VPNs, and databases hosted in the cloud by third parties such as Amazon and Microsoft. By default, each database you connect to your Analyst Studio Workspace can be queried by all members of your Workspace.

Obtain database credentials and connection details

When you set up a new data connection, you will provide Analyst Studio with the credentials for a user account in your database that Analyst Studio will use to execute queries against the database. The permissions granted to this user account will govern which tables and records members of your Workspace can access when they use that database connection in Analyst Studio.

A best practice is to create (or ask your database admin to create) a new database user specifically to use with Analyst Studio. Grant this new user read-only permission for the schemas and tables you want your Analyst Studio users to be able to query.

Choose a connection method

Analyst Studio can connect to your database in two ways:

  • Direct Connect: Analyst Studio will connect directly to your database over the public internet. This is the simplest way to connect, but may require your database or network to be configured to allow Analyst Studio’s servers to connect to it.

  • Bridge: Analyst Studio connects to your database with the assistance of a small helper application installed on a computer inside your network with direct access to your database. This solution lets Analyst Studio connect to your database even if it’s not publicly accessible.

Typically, users are able to connect Analyst Studio to their database directly. There are, however, a few common scenarios in which you will likely need to use Bridge and/or seek the assistance of your network administrator to connect, namely if:

  • You need to be connected to a VPN/VPC or be physically in the office to access your database; or,

  • You need to configure your database to allow Analyst Studio to directly connect (for example, allowlist Analyst Studio’s IP addresses), but you do not have access to the database’s configuration console.

Direct Connect

Overview

Direct Connect is the simplest way to connect your database to Analyst Studio. You provide Analyst Studio with the necessary credentials, and Analyst Studio’s servers connect directly to your database. Most databases hosted by third parties, such as Amazon, Google, and Segment, are publicly accessible and are compatible with Direct Connect. In some cases, databases hosted in private networks can also be publicly accessible and are compatible with Direct Connect.

Requirements

In order to use Direct Connect, your database must:

  • Be publicly accessible via the internet (Not sure? Follow these steps).

  • Have SSL encryption enabled.

  • Be allowed to accept incoming connections from Analyst Studio’s IP addresses.

Your database may require you to allowlist Analyst Studio’s IP addresses before it will accept an incoming connection from our servers. All connections from Analyst Studio will come from one of these eight IP addresses:

54.68.30.98/32
54.68.45.3/32
34.209.23.118/32
54.188.241.100/32
52.43.195.103/32
3.224.146.111/32
54.221.238.89/32
52.20.173.236/32

If your database or network environment does not meet the above requirements, consider connecting with Analyst Studio’s Bridge connector instead.

How to connect

To add a new data connection to your Workspace using Direct Connect:

  1. Click the app switcher icon in the top navigation bar and select Analyst Studio. Navigate to the Workspace to which you’d like to add the connection, and click on your name in the top left corner.

  2. Click on Connect a Database.

  3. Select your database from the list.

  4. Follow the on-screen instructions to connect your database.

FAQs (Direct Connect)

Q: How do I know if my database is publicly accessible?

A database is "publicly accessible" if the server it is hosted on can be reached via any internet connection. Users aren’t required to be logged in to a VPN or on a private network to access the database.

To check if your database is publicly accessible, run the following command on a computer that’s not logged in to your VPN or private network. Replace your_db_host and your_db_port with the host and port of your database:

  • Linux or OSX: nc -v your_db_host your_db_port

  • Windows: telnet your_db_host your_db_port

If the connection succeeds, your database is publicly accessible. If it fails, it likely isn’t.

In some instances, however, the connection can fail because your database doesn’t allow connections from IP addresses that haven’t been added to an allowlist. If this may be the case, find your computer’s IP address and add it to your database’s IP address allowlist. Try the command above again. If it succeeds, your database is publicly accessible and can connect to Analyst Studio directly. Note that you may need to allowlist Analyst Studio’s IP addresses in your database’s configuration.

Q: Is it possible to connect to a database via SSH tunneling?

Although we don’t currently support connecting databases via SSH tunneling, we do offer our Bridge software to connect to a database located on a private network or behind a VPC. You can read more about this process here.

Troubleshooting (Direct Connect)

1. When connecting to any database, your address is in a reserved range.

Could not verify connection: Address ... in reserved range.

Your database may not be publicly accessible. You can check if your database is publicly accessible by following the steps in this section. If your database isn’t publicly accessible, you should connect using Bridge.

Could not verify connection: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

This is a generic MySQL error that indicates Analyst Studio can’t reach your database. There are a couple of common causes for this error:

  • Your database doesn’t allow SSL connections: Direct Connect only supports SSL connections. If SSL is disabled on your MySQL database, Direct Connect will be unable to connect. To resolve this issue, you can either allow SSL connections to your database or connect using Bridge.

  • Your MySQL database isn’t configured to allow external connections: Check the configuration file at /etc/mysql/my.cnf. If the bind_address is set to 127.0.0.1, your database will only allow connections from your local machine. To allow external connections—​which is required for connecting to Analyst Studio—​change the bind address to 0.0.0.0.

3. When connecting to Redshift, it says we’re having trouble verifying your connection.

Could not verify connection: We're having trouble verifying your connection. Please check that the hostname and port are correct and that your firewall is configured to allow connections from Mode.

This is a generic error that indicates Analyst Studio can’t reach your Redshift database. There are a couple of common causes for this error:

  • Your Redshift security group or VPC security doesn’t allow connections from Analyst Studio’s IP addresses: If your Redshift is in a security group, you must configure that security group to allow connections from Analyst Studio’s IP addresses. Note that if your Redshift is in a VPC, the VPC may also be part of a security group that’s distinct from the Redshift security group. This VPC security group must also allow connections from Analyst Studio. See this article about which IP addresses to allowlist, this article for how to configure security groups in your Redshift instance, and this article for more information on security settings for Redshift.

  • Use a Redshift host name rather than the IP address: Redshift host names are often formatted like this: examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com. Try using a host of this format rather than an IP address. This host can be found in the middle of the JDBC URL, which typically matches the following format: jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev.

4. When connecting to BigQuery, it says we couldn’t verify your connection.

Could not verify connection: com.modeanalytics.jdbc.bigquery.BQSQLException: toDerInputStream rejects tag type 123

This error occurs if you try to connect with a JSON key rather than a P12 key. When completing the last step of setting up your BigQuery connection, double-check that you’re using a P12 key.

5. I get a connection error that says "The server does not support SSL".

This means that your database does not have SSL configured. To ensure your data remains secure, Direct Connect doesn’t support connecting unless your database allows encrypted connections. To enable SSL, please refer to your database’s website for information on how to configure TLS/SSL for your database.

If you don’t want to or can’t enable SSL, you can connect your database to Analyst Studio using Bridge. Though Bridge connections use transport encryption by default, you can disable it by clicking the green "turn off transport encryption" link when setting up Bridge.

Transport encryption

"Publicly accessible" databases aren’t databases that can be queried by anyone in the general public—​nearly all "publicly accessible" databases still require passwords and encrypted connections. Instead, "publicly accessible" means that the database can be reached via any internet connection. Users aren’t required to be logged in to a VPN or on a private network to access the database.

The easiest way to check if your databases is publicly accessible is to run the following command on a computer that’s not logged in to your VPN or private network. Replace host and port with the host and port of your database:

nc -v host port

If the connection succeeds, your database is publicly accessible. If it fails, it likely isn’t.

In some instances, however, the connection can fail because your database doesn’t allow connections from IP addresses that haven’t been added to an allowlist. If this may be the case, find your computer’s IP address and add it to your database’s IP address allowlist. Try the command above again. If it succeeds, your database is publicly accessible and can connect to Analyst Studio directly, provided that the appropriate IP addresses are allowlisted.

Bridge

Overview

Typically, users will connect Analyst Studio directly to their database. However, there are many situations in which directly connecting Analyst Studio to your database is not possible, or modifying the configuration of your VPN/firewall is not practical or desirable. For these cases, Analyst Studio offers an application ("the Bridge connector") to coordinate communication between Analyst Studio and your databases. The Bridge connector is easy to install, configure, and maintain.

The Bridge connector can run on a computer or virtual machine within your organization’s VPN or firewall. Analyst Studio also supports running the Bridge connector inside a Docker container.

When you write and run a query in Analyst Studio, the Bridge facilitates the connection to your database. The query is executed by your database using the user credentials you provide when setting up Analyst Studio. Once complete, Bridge sends data back to Analyst Studio, so you can visualize and share the results.

Requirements

To install and configure Bridge, you must have sufficient (typically local administrative) privileges on the host computer. You must also have access within Analyst Studio to connect data sources for your Workspace.

Bridge connects to Analyst Studio by making outbound TCP connections on the following ports:

  • HTTPS/443

  • TCP/8444

You can run Analyst Studio’s Bridge connector as a container on any Linux host running Docker engine.

You can also install the Bridge connector on any computer running any of the following supported operating systems:

  • macOS 10.11 or later.

  • Windows 10 or Windows Server 2012 R2 or later.

  • Most 64-bit Linux distributions, including:

    • Ubuntu 14.04 16.04 18.04 20.04 LTS.

    • Fedora 27, 28, 29.

    • CentOS 6, 7.

    • Debian 8.0, 9.0.

    • ThoughtSpot Support can provide a generic tarball for many other 64-bit Linux systems.

When connecting to a database in AWS, most customers will run Bridge in an EC2 virtual machine. In these cases, you should set up a 64-bit EC2 instance running Linux that can both connect to your Redshift/RDS cluster and can connect to modeanalytics.com on ports 443 and 8444.
If you are installing via the non-Docker approach, please reach out to ThoughtSpot Support to help complete the Bridge setup.

Install Bridge with Analyst Studio

These instructions assume that you meet the requirements and would like to install Analyst Studio’s Bridge connector on a host computer you administer.

Our system packages will install Bridge and configure the system to run Bridge via the system’s service manager. Bridge’s configuration file 'bridge.json' contains a credential and should be kept secret when incorporating it into configuration management systems.

  1. In the upper left corner of your Workspace’s Analyst Studio home page, expand the menu under your name, and select Connect a Database from the dropdown.

    Settings Dropdown
  2. Click on the type of database you want to connect to Analyst Studio.

    Select a Database
  3. Click on the 'Bridge connector' link above the connections details form.

    Enter Credentials
  4. To install Bridge for the first time, click on the 'connect a new bridge' link.

    Connect a New Bridge
  5. Select the operating system of your host computer and follow the onscreen prompts to copy and execute statements in the command line.

    Choose your OS
  6. Once Bridge is installed and running, click Next.

  7. You will be returned to the database credentials page, where you should see that the database will be connecting through your newly-installed Bridge connector.

    Back to Credentials
  8. Fill out your database credentials and click 'Connect.'

Run Bridge in a Docker container

These instructions assume you meet the requirements and would like to run Analyst Studio’s Bridge connector in a Docker container.

Generate Bridge credentials

  1. In the upper left corner of your Workspace’s Analyst Studio home page, expand the menu under your name, and select Workspace Settings from the dropdown.

    Organization Settings
  2. In the left navigation, under Data, select Bridge Connectors.

    Under Bridge Connectors
  3. Enter a descriptive name for your Bridge connector and press Create bridge. You will be able to edit this in the future.

    Create New Bridge
  4. A panel will display a confirmation that the named Bridge was created and the credentials for that Bridge are displayed.

    Bridge Display Panel
  5. Copy the configuration with ENV variables. Save these to create a new configuration file for the Bridge connector. We recommend naming the file /etc/mode-bridge.env on the host where you want to install Bridge.

Run Bridge in Docker

  1. Pull the latest Docker image from DockerHub using the command docker pull modeanalytics/bridge-client from a terminal.

  2. Run the following command to download, create, and start your Bridge connector: docker run --env-file /etc/mode-bridge.env --name mode-bridge modeanalytics/bridge-client:[Dockerfile Version].

  3. Verify that your Bridge connector has been configured correctly.
    Inspect the logs for any error messages by running the following command: docker logs --tail 30 mode-bridge. If you discover you need to make any corrections to your configuration file, edit the file and then restart your container to apply the changes by running the following command: docker restart mode-bridge.

Connect your database via Bridge

  1. In the upper left corner of your Workspace’s Analyst Studio home page, expand the menu under your name, and select Connect a Database from the dropdown.

    Connect a Database
  2. Click on the type of database you want to connect to Analyst Studio.

    Select a database
  3. Click on the 'Bridge connector' link above the connections details form.

    Enter Credentials
  4. Select the name of the Bridge connector running in Docker.

    Choose a Bridge
  5. You will be returned to the database credentials page, where you should see that the database will be connecting through your Bridge connector.

    Return to Credentials

FAQs (Bridge)

Q: Do I need to run several Bridge connectors to connect Analyst Studio to several databases?

No. A single Bridge connector can support multiple databases.

Q: What data does the Bridge connector have access to?

The Bridge connector provides a tunnel through which Analyst Studio’s platform can connect to your database(s). The connection between the Bridge and Analyst Studio is fully encrypted. The Bridge connector stores only its own configuration. There is no caching or database driver in the Bridge. No database credentials, queries, or query results are stored within the connector.

Q: What happens if I disable transport encryption?

Some older databases either do not support or have not been configured to support transport encryption. To enable customers to connect Analyst Studio to these databases, we support disabling transport encryption, but only when connecting via the Bridge.

If you disable transport encryption when configuring the Bridge connector, this will disable the encrypted connections between the computer running the Bridge connector and your database. However, all communication between the computer running Bridge and Analyst Studio will still be fully encrypted.

Q: How can I replace an existing Bridge connection?

To replace an existing Bridge connection, first remove the existing Bridge from Analyst Studio.

  1. Click on your avatar in the upper left.

  2. Select Settings from the dropdown.

  3. Click on Bridges under the appropriate Workspace name.

  4. Remove the Bridge from that page.

Next, add the new database.

  1. Click on your avatar in the upper left.

  2. Select Connect a database from the dropdown.

  3. Click on the Database on a Private Network tab.

  4. Select the appropriate database type.

The next page should prompt you to install Bridge. Because you already have Bridge installed, skip to the final step that asks you to run mode-bridge setup. On the machine where you’re running Bridge, add -replace to the command listed and run the entire command. The new command should look like this:

 mode-bridge-setup -replace -init BIG_BLOG

Q: Where does the Bridge Connector install?

Bridge will install in one of the following locations, depending on which operating system the host computer is using. To install and configure Bridge, you must have sufficient (typically local administrative) privileges on the host computer.

OS Install Directory Log File

Linux

/opt/mode/bridge<

/opt/mode/bridge/bridge.log

OS X

/usr/local/mode/bin<

~/.modeanalytics/bridge.log

Windows

C:\Program Files\Mode Analytics\Bridge Connector\

Q: Where is the Bridge configuration file saved?

You can locate the configuration file using the following OS-specific paths:

  • Linux: /opt/mode/Bridge/conf/Bridge.json

  • Mac: $HOME/.modeanalytics/Bridge.json

  • Windows: C:\Program Files\Mode Analytics\Bridge Connector\Bridge.json

Q: How do I stop or start the Bridge connector?

The commands to start or stop Bridge vary across operating systems. If you don’t see commands listed for your system below, please contact ThoughtSpot Support.

OS Stop Bridge Start Bridge

OSX

launchctl stop com.modeanalytics.bridge

launchctl start com.modeanalytics.bridge

Ubuntu

sudo stop mode-bridge

sudo start mode-bridge

Ubuntu 16.04

sudo systemctl stop mode-bridge

sudo systemctl start mode-bridge

UCentOS

sudo /etc/init.d/mode-bridge stop

sudo /etc/init.d/mode-bridge start

CentOS 7+

sudo systemctl stop mode-bridge

sudo systemctl start mode-bridge

Linux

/etc/init.d/mode-bridge stop

/etc/init.d/mode-bridge start

Windows

Q: Where in Analyst Studio can I view the password (token secret) for the Bridge connector that I created?

The token’s secret is a protected string and is not visible in the Analyst Studio UI after initial creation. If the configuration files displayed were not copied or saved somewhere by the user, they should be considered lost forever. We recommend deleting this bridge and creating a new one.

Q: How do I know the Bridge connector is running correctly in Docker?

To verify that your Bridge connector has been configured correctly, you can inspect the logs for any error messages by running the following command:

docker logs --tail 30 mode-bridge

If you discover you need to make any corrections to your configuration file, edit the file and then restart your container to apply the changes by running the following command:

docker restart mode-bridge

Troubleshooting (Bridge)

1. When connecting to any database, Bridge says it could not verify the connection.

You may see several error messages that say Bridge could not verify the connection.

Could not verify connection: please try again.
Could not verify connection: We're having trouble verifying your connection. Please check that the hostname and port are correct and that your firewall is configured to allow connections from Mode.

This error indicates that Analyst Studio could reach your Bridge connector, but Bridge couldn’t reach your database.

You may see this error if your database credentials are invalid. As a first step, check the credentials and try again. Note that if your database doesn’t have a password, entering any password should fix this error.

If you’re sure the credentials are correct, these errors can also occur when Bridge is installed on a machine that can’t access your database. Importantly, this error occurs from the perspective of the machine running Bridge, not necessarily from your computer or from Analyst Studio’s servers. Most commonly, this error occurs when your database is accessible only to computers inside a VPN. If you installed Bridge on a computer outside your VPN, or if the computer is not currently logged in to the VPN, Bridge won’t be able to connect to your database.

You can test if the machine running Bridge has access to the database by running this command on the machine running Bridge:

  • Linux or OSX: nc -v your_db_host your_db_port

  • Windows: telnet your_db_host your_db_port

If the command succeeds, the computer has access to the database. You can contact ThoughtSpot Support for further assistance.

If the command fails, the computer running Bridge doesn’t have access to the database. To fix this, you should either install Bridge on a machine that has access or adjust your network settings so that the machine running Bridge has access to your database.

2. When connecting to any database, Bridge says it could not verify the connection and that there was a problem connecting to your Bridge Connector.

Could not verify connection: There was a problem connecting to your Bridge Connector.

This error indicates that Analyst Studio couldn’t reach your Bridge connector.

First, check if Bridge is running with the command listed below.

  • Linux or OSX: ps aux | grep mode-bridge

  • Windows: Look in Service Manager to see if Bridge is running.

If Bridge is not running, restart the Bridge application.

If Bridge is running, Analyst Studio may not be able to reach Bridge because the ports that Bridge connects over are closed. Bridge connects to Analyst Studio over outbound port 8444. You can confirm that the port is open for Bridge by running these commands on the computer running Bridge.

  • For Linux or OSX:

      nc -v cat-gin-flower.bridge.modeanalytics.com 8444
  • Windows:

      telnet cat-gin-flower.bridge.modeanalytics.com 8444

If these commands fail, you must open the outbound port on your network and on the computer running Bridge.

If the port is open and you still see this error, your Bridge config file may not match what Bridge is looking for. If you have made manual changes to the config file, either revert your custom changes and try connecting again, or contact ThoughtSpot Support for additional help.

3. When installing Bridge on Windows, Bridge is unable to write to config.json.

Unable to write to ...\config.json

This error typically occurs when you attempt to install Bridge without the proper permissions. To resolve, open an instance of PowerShell with elevated privileges (Run as Administrator) and use that instance to install Bridge.

Security

Analyst Studio supports TLS/SSL (Transport Layer Security/Secure Socket Layer) for encrypting communication with your database. This type of security, which encrypts data while it’s in transit, is commonly referred to as transport encryption.

For additional auditing, Analyst Studio tags each query with additional metadata which will appear in the database system logs:

  • The Analyst Studio username and email address of the user running the query.

  • A link to that query in Analyst Studio, which includes the time the query was run and the exact Dataset returned.


Was this page helpful?