How to connect to database directly from Sisense server using SQLLine

[nataliia_kh]

Symptoms

You need to connect to database, however connection is not going through. You have already check the hostname, username, password and everything seems to be correct. 

You have whitelisted the database address, hostname, etc. however not sure if the the rules are working properly.

Diagnosis

In order to check if the necessary database can be reached from Sisense server you may connect to it using jdbc .jar file directly from Sisense server (omitting UI). This was you will be able to make sure the whitelisting works properly and the issue is with connector itself.

Solution

1)Download jdbc .jar file for database in question from https://www.cdata.com/solutions/bi/sisense/ (we will use Redshift in this example)

2)Put the .jar file into folder in File Manager. We have create a new 'redshift' folder inside 'data' folder

3)Login to Sisense server

4)Make sure java and SQLLine are installed on Sisense server. If you are doing this for the first time you will need to install them. You may use 'wget' commands for this

5)Connect to SQLLine by using the following command:

java -cp <sqlline-jar-file>:<jdbc-driver-jar-file> sqlline.SqlLine

Replace <sqlline-jar-file> with the path to the SQLLine JAR file and <jdbc-driver-jar-file> with the path to your JDBC driver JAR file.

In our example, we have downloaded SQLLine to the folder we were in. The path to the File manager in Sisense is '/opt/sisense/storage'. Therefore, the connection string for us looked like this:

java -cp sqlline.jar:/opt/sisense/storage/data/redshift/cdata.jdbc.redshift.jar sqlline.SqlLine

6)Once SQLLine is running, connect to your database using the !connect command.

General syntax is the following:

!connect jdbc:<database-url> <username> <password>

Following CDATA redshift connection string requirements, in our example the syntax looked like:

!connect jdbc:redshift://database_url:port_number/database_name user_name user_password

When you are downloading the CDATA jdbc connector, please open the docs section for that connector to check the right connection string format.

If connection is established you will be able to use SQL queries to return the data.

If connection is refused, please check the database address whitelisting again and make sure all the necessary outbound/inbound rules are setup.