SQL Access

In addition to API access, the Common Data Model (CDM) can be read from a database connection via SQL to support bulk data transfer use cases such as feeding data in to machine learning models for further analysis. The SQL access is supported via a MySQL compatible database protocol accessed via an SSH tunnel. This connection type is generally well supported by various programming language database connection libraries as well as popular business intelligence tools.

Before gaining access to your database you will need to exchange an SSH key with your administrator.

Before you Start

There are a number of steps that need to be completed to access data via SQL, they are:

An SSH key needs to be created and shared with Boss Insights to gain access to a secure tunnel to access data
Your administrator needs to enable SQL Access for your user account in the admin portal
You need to obtain your access identifier from your profile

Exchange of SSH Keys

Send your public key to Boss Insights and take note of location of keys, particularly your <private key file>. To send the key file and request access create a support request for SQL access.

If you already have existing SSH key pairs on your local machine for accessing other resources, please ensure you do not overwrite them when creating the required keys for your project. This can be avoided by assigning a unique name to the key pair file that is specific to your Boss Insights project (e.g Users/userid/.ssh/boss).

Generate a pair of SSH keys

Generate your SSH keys on Windows

1. Download Cygwin from https://cygwin.com/install.html

2. Install Cygwin on Windows (reference https://www.youtube.com/watch?v=hh-V6el8Oxk ), On the 'Select Packages' page, enter 'openssh' in the search box, expand 'Net' and select to install.

3. Generate a pair of ssh keys on Cygwin terminal with the command:

ssh-keygen -t rsa -b 4096

The default location of the pair of keys is: C:\cygwin64\home<userid>.ssh

Or if the key file name is changed during key pair generation without pointing to an absolute path, the location of the pair of keys: C:\cygwin64\home<userid>Where <userid> is your local computer's username

Generate your SSH keys on macOS, Linux or Unix-like system

xx

1. Open a terminal on your computer

2. Generate a pair of ssh keys in the terminal window with the command:

ssh-keygen -t rsa -b 4096

3. Choose a location to put the generated key files, being careful not to overwrite existing keys.

4. Then enter a passphrase and confirm the passphrase. The key pair is generated.

Granting Access

An administrator of your account will need to edit your user account from the administration portal and choose Show Advanced Options and then choose to enable SQL Data Access. It can take up to one hour after this access is enabled for our backend database systems to synchronize.

Connecting

Exact instructions will differ depending on the tool being used to connect to the database but generally you will need the following connection information:

SSH Tunnel

The SSH host name you connect to is region dependant:

Host Canada: US:	db.myintranetapps.ca db.myintranetapps.com
Port	22
User	Within the main portal click on the user icon in the top right corner and choose “View Profile” and then click the “Show Access Identifier” button.
Password	N/A - Use a key

To test connectivity to the SSH Tunnel you will need to connect using a MySQL client that supports SSH tunnels such as MySQL Workbench. ( https://www.mysql.com/products/workbench/ ) or a compatible database library if connecting from a specific programming language.

You cannot connect to the bastion host with a regular SSH client as shell access is disabled. The bastion only allows MySQL traffic to be tunnelled.

Database Connection

Host	db
Port	3306
User / Password	Available within your user profile, click the “Show Access Identifier” button

Once connected you can access each CDM by the schema name “database-KEY” where KEY is the unique alphanumeric account key. If multiple accounts share data with you they will be shown as individual schemas.

Python Help

Python is a popular programming language choice for our users, the following package and code example has been a helpful starter: https://github.com/pahaz/sshtunnel/

PY

# pip install pymysql sshtunnel

import pymysql
from sshtunnel import SSHTunnelForwarder

tunnel = SSHTunnelForwarder(
    ("db.myintranetapps.com", 22),
    ssh_host_key=None,
    ssh_username="<INSERT USER>",
    ssh_password=None,
    ssh_private_key="<INSERT PRIVATE KEY>",
    ssh_private_key_password=None,
    remote_bind_address=("db", 3306))

tunnel.start()

connection = pymysql.connect(user='<INSERT USER>', passwd='<INSERT PASSWORD>',
                                 host="127.0.0.1", port=tunnel.local_bind_port
                                 database='database-<INSERT ACCOUNT KEY>')
                                 
cursor = connection.cursor()

Other languages and tools

If you are using a language or tool that doesn't support SSH tunnelling natively you can use SSH itself to forward the remote connection to a local network port that you can then connect to:

BASH

ssh -fNg -L 3307:db:3306 <INSERT USER>@db.myintranetapps.com

This will open the local network port 3307 so you can connect to localhost:3307 using your mysql client.