Connect To JIRA Server Using CData JDBC Driver

[intapiuser]

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

1. 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/
2. 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

1. Download the CData JIRA JDBC Driver from here: https://www.cdata.com/drivers/jira/jdbc/
2. Extract the downloaded JiraJDBCDriver.zip file to its own folder
3. 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.jar
4. NOTE:
   The full path to java.exe is C:\Program Files\Sisense\infra\jre\bin\java.exe

Create A JDBC Connection String

1. 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\lib
2. Open 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.jar

NOTE: The full path to java.exe is C:\Program Files\Sisense\infra\jre\bin\java.exe
3. Configure the following parameters:
   1. Authentication
      1. URL: https://jira.sisense.com
   2. OAuth
      1. Initiate OAuth: GETANDREFRESH
      2. OAuth Client Id: OauthKey (it is the same value the was configured as the consumer id in JIRA)
      3. OAuth Client Secret: Your client secret (it is a dummy value)
      4. Certificate Store Type: PEMKEY_FILE
      5. Certificate Store: C:\temp\jira_privatekey.pem (full file path to the jira_privatekey.pem file on the server)
   3. Miscellaneous
      1. Include Custom Fields: True
4. Click Test Connection
5. 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

1. Open the Sisense Web App → Data tab
2. Create a new ElastiCube and click Add Data
3. Select the Generic JDBC Connector
4. Configure the following parameters:
   1. Connection String: Paste the value copied in step 7 above
   2. JDBC JARs Folder: C:\Program Files\CData\CData JDBC Driver for JIRA 2019\lib
   3. Driver's Class Name: cdata.jdbc.jira.JIRADriver