Connect To JIRA Server Using CData JDBC Driver
Summary: intapiuser provides a detailed guide on connecting to JIRA Server using the CData JDBC Driver. The instructions include prerequisites like configuring JIRA Server for OAuth connections and obtaining necessary credentials such as OAuth Client Id, Secret, and Private Key file. The process involves installing the CData JIRA JDBC Driver, creating a JDBC connection string, and configuring the connection within Sisense. The post emphasizes that these instructions are not applicable for JIRA Cloud.
Please note, these instructions are to connect with the JDBC CData driver to JIRA Server. These steps will not work for JIRA Cloud at this time.
Pre-requisites
Configure JIRA Server to allow OAuth connections, as described in the following link, under "Step 1: Configure Jira": https://developer.atlassian.com/server/jira/platform/oauth/
Take note of the following values we will need in order to make the connection
- Oauth ClientId/OauthKey (it is the same value the was configured as the consumer id in JIRA)- Oauth Client Secret- Private Key file
Steps
Install The CData JIRA JDBC Driver
Download the CData JIRA JDBC Driver from here: https://www.cdata.com/drivers/jira/jdbc/
Extract the downloaded JiraJDBCDriver.zip file to its own folder
Install the CData JIRA JDBC Driver using the following command (run it from a cmd window that is being run as Administrator😞
java.exe -jar setup.jarNOTE: The full path to
java.exeisC:\Program Files\Sisense\infra\jre\bin\java.exe
Create A JDBC Connection String
Open a cmd window as Administrator and navigate to the "lib" subfolder where the CData JIRA JDBC Driver was installed:
cd C:\Program Files\CData\CData JDBC Driver for JIRA 2019\libOpen the CData JDBC Connection String Builder by running the following command (from the same cmd window that is being run as Administrator😞
java.exe -jar cdata.jdbc.jira.jarNOTE: The full path to java.exe is C:\Program Files\Sisense\infra\jre\bin\java.exeConfigure the following parameters:
Authentication:
Use your company's JIRA instanceOAuth
Initiate OAuth:
GETANDREFRESHOAuth Client Id:
OauthKey(it is the same value the was configured as the consumer id in JIRA)OAuth Client Secret: Your client secret (it is a dummy value)
Certificate Store Type:
PEMKEY_FILECertificate Store:
C:\temp\jira_privatekey.pem(full file path to the jira_privatekey.pem file on the server)
Miscellaneous
Include Custom Fields:
True
Click Test Connection
Click Allow
6. You should then see the following confirmation message:
7. Go back to the JDBC Connection String Builder utility, click Copy to Clipboard.
The generated JDBC Connection String should look something like this:jdbc:jira:URL="https://{your JIRA URL}";Initiate OAuth="GETANDREFRESH";OAuth Client Id="OauthKey";OAuth Client Secret="{your secret}";Certificate Store Type="PEMKEY_FILE";Certificate Store="{path to .pem file}";_persist_oauthaccesstokensecret={Oauth Secret};_persist_oauthrefreshtoken={refresh token};_persist_oauthexpiresin=157680000;_persist_oauthaccesstoken={your token};_persist_token_timestamp=1567596913560;
Configure The Connection In Sisense
Open the Sisense Web App → Data tab
Create a new ElastiCube and click Add Data
Select the Generic JDBC Connector
Configure the following parameters:
Connection String: Paste the value copied in step 7 above
JDBC JARs Folder:
C:\Program Files\CData\CData JDBC Driver for JIRA 2019\libDriver's Class Name:
cdata.jdbc.jira.JIRADriver
