Connecting queries

Owned by Mariia Romanova
Last updated: Nov 18, 2022
3 min read

There are two ways of connecting your queries with third-party tools:

  1. JDBC driver with Java applications

  2. ODATA protocol for the tool of your choice (like MS Power BI, Tableau, etc.)

Configuration

SQL Cloud is a 100% JavaScript database. Therefore an HTML page is always required to run it.

The page to run the database and which is able to accept third-party connections is the SQL Cloud - Query Server page:

 

Some steps are required in order to get this working:

Security Level 1: Log into Jira

The Query Server page can be viewed only if you are logged in to Jira. In other words, none can connect to your data until you log into Jira before.

Security Level 2: SQL Cloud Global permission

You cannot see the SQL Cloud - Query Server action menu nor the page until your Jira administrator grants you explicitly the SQL Cloud Connect global permission.

Without the Jira administrator’s explicit grant no connections are allowed.

Security Level 3: Enable the Query Server feature

By default, this feature is disabled for all the users and every user must explicitly enable it after they have been granted by the Jira administrator and before to start accepting any third-party connection.

Without the Jira user’s explicit grant no connections are allowed.

Security level 4: Set a Query Server password

The Query Server will authenticate the remote user’s credentials before accepting any connection.

These credentials are not the same as the Jira user’s credentials!! One is for you to connect to Jira and the other is for anyone else to connect to your SQL Cloud database instance.

In this way, only the remote users that know this password (created by YOU) can connect.

By default, a random password is created. But you can edit and change it.

The username is the same name as your Jira Cloud instance name and it cannot be changed (read-only).

 

Connections life cycle

The Query Server page displays the database connection status in real-time always:

  • DISABLED This is the initial state and must be manually changed by the user as described above. The server will reject any connection attempt.

  • Disconnected Once the service has been enabled, this is the default state. The server is ready and waiting for connections. Please, keep in mind that you may have more than one Query Server page opened, but one only can be connected to the same tool at a time. The rest will be in this disconnected status.

  • CONNECTING Once the page has been loaded, the Query Server page is connecting to the third-party tool.

  • CONNECTED The remote user has been authenticated and the server is connected and listening to incoming users' queries. After closing the connection the page status will automatically change to Disconnected.

 

How does it work?

You may see how a Query Server page is automatically opened and connected to the tool.

This will happen each time , that the third-party tool tries to fetch data from Jira and there is no any SQL Cloud database connection opened yet.

Why? Since SQL Cloud is a 100% JavaScript database running on an HTML page, some restrictions are inherited from the browser security sandbox (as example: opening a TCP/IP port that is listening to connections- is forbidden). This introduces some behavior, different from the rest of the database servers.

You, as a User, do not have to care about all these details, as they are automatically managed in some way behind the scene.

When a Query Server page is automatically opened it receives the full context to:

  1. Start a new instance of the SQL Cloud Database

  2. Authenticate the remote user

  3. Connect to the third-party tool and start serving Jira data (answering the remote user’s queries).

 

Â