Issues with Setting Up Database Connection

[Stephen Higgins]

We are having issues with getting the Adaptavist Resources setup for one of our database connections and are unsure of how to resolve. Any help would be greatly appreciated. We are leveraging the server edition of JIRA.

We tried this connection string and included the jtds-1.3.1.jar driver and it is not working.

• NOTE: This connection string and driver work on our Confluence instance.

url jdbc:jtds:sqlserver://df2v-catsql-p01.ebsi.corp:1433/cornerstone_catalog;domain=EBSI;useNTLMv2=true

driver net.sourceforge.jtds.jdbc.Driver

driver file jtds-1.3.1.jar

Error:

2020-05-28 08:01:58,727-0500 https-jsse-nio-8443-exec-3 WARN dcorrigan 481x41945x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.util.DriverDataSource] Registered driver with driverClassName=net.sourceforge.jtds.jdbc.Driver was not found, trying direct instantiation.

2020-05-28 08:01:58,739-0500 https-jsse-nio-8443-exec-3 ERROR dcorrigan 481x41945x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.pool.PoolBase] 32b58ee3-46c6-41a1-9108-36e0f4d52482 - Failed to execute isValid() for connection, configure connection test query (null).

2020-05-28 08:01:58,745-0500 https-jsse-nio-8443-exec-3 ERROR dcorrigan 481x41945x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: null

java.lang.AbstractMethodError

            at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)

------------------------------------------

We also tried this connection string which we believed should use an included driver and it is failing.

Included driver is mssql-jdbc-7.2.1.jre8.jar

We tried including sqljdbc42.jar as well, no dice.

jdbc:sqlserver://df2v-catsql-p01.ebsi.corp;databaseName=cornerstone_catalog;domain=EBSI;useNTLMv2=true

We also tried calling the drivers two different ways. 

com.microsoft.sqlserver.jdbc.SQLServerDriver

com.microsoft.jdbc.sqlserver.SQLServerDriver

2020-05-28 08:06:05,367-0500 https-jsse-nio-8443-exec-1 WARN dcorrigan 486x43711x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.util.DriverDataSource] Registered driver with driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found, trying direct instantiation.

2020-05-28 08:06:06,391-0500 https-jsse-nio-8443-exec-1 ERROR dcorrigan 486x43711x1 bkgxw6 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection [c.z.hikari.pool.HikariPool] d3d6c4da-aec8-4a15-b633-48351caddd3e - Exception during pool initialization.

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'OUR USER IDl'. ClientConnectionId:df005aab-b901-4b7d-b0a7-bdd0a0210d77

            at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)

[Peter-Dave Sheehan]

Both com.microsoft.sqlserver.jdbc.SQLServerDriver and net.sourceforge.jtds.jdbc.Driver normally come bundled with Jira.

The only driver I've ever had to install is the mysql driver, and that's done at install since that's what I use for my jiradb.

Looks like your attempt using 'com.microsoft.sqlserver.jdbc.SQLServerDriver' worked except for the username/password being wrong.

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'OUR USER IDl'

I can replicate the exact same log outputs when I try to use a bad un/pw combination.

[Alejandro Suárez - TecnoFor]

@Peter-Dave Sheehan you're right, I just read between the lines. It seems like its a login error.

[Stephen Higgins]

@Peter-Dave Sheehan / @Alejandro Suárez - TecnoFor The user account is the same as the one we leverage for our BobSwift database macro connection within Confluence. At this point, when I click Preview or Add in the Resources screen, nothing happens. No errors are displayed and the connection is not saved. 

[Peter-Dave Sheehan]

I found that preview was not always reliable. For example, change the un/pw didn't cause a failure until I attempted to update the resource.

You can keep an eye on your atlassian-jira.log file, it may show more detail/errors.

[Stephen Higgins]

@Peter-Dave Sheehan / @Alejandro Suárez - TecnoFor at this point, we tried reinstalling older drivers to see if that would resolve the issue, but no go. The JTDS driver refuses to work on our instance. The SQL driver appears to claim a login failure and we think it has to do with the it not respecting the ;domain=EBSI. My IT department is telling me this is required to do Windows Authentication. They also tell me this is more than likely coming from outside of the domain. 

Like I mentioned before, we are confident the driver works since we are using it on our Confluence instance.

Any assistance would be greatly appreciated since at this point we are unable to leverage this feature which is a big loss for us. 

Here is the latest log:

jdbc:sqlserver://df2v-catsql-p01.ebsi.corp;databaseName=cornerstone_catalog;domain=EBSI;useNTLMv2=true com.microsoft.sqlserver.jdbc.SQLServerDriver 020-06-02 07:53:47,358-0500 https-jsse-nio-8443-exec-14 WARN dcorrigan 473x39355x2 14bk5l4 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection/preview [c.z.hikari.util.DriverDataSource] Registered driver with driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver was not found, trying direct instantiation. 2020-06-02 07:53:48,388-0500 https-jsse-nio-8443-exec-14 ERROR dcorrigan 473x39355x2 14bk5l4 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection/preview [c.z.hikari.pool.HikariPool] 1ced3367-a8d4-43e1-8716-677c228e7cba - Exception during pool initialization. com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'svc_atlconf_sql'. ClientConnectionId:2aa31e59-1e28-4848-b8eb-452d64c4426e at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)

jdbc:jtds:sqlserver://df2v-catsql-p01.ebsi.corp:1433/cornerstone_catalog;domain=EBSI;useNTLMv2=true net.sourceforge.jtds.jdbc.Driver 2020-06-02 07:54:54,962-0500 https-jsse-nio-8443-exec-24 WARN dcorrigan 474x39869x2 14bk5l4 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection/preview [c.z.hikari.util.DriverDataSource] Registered driver with driverClassName=net.sourceforge.jtds.jdbc.Driver was not found, trying direct instantiation. 2020-06-02 07:54:54,986-0500 https-jsse-nio-8443-exec-24 ERROR dcorrigan 474x39869x2 14bk5l4 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection/preview [c.z.hikari.pool.PoolBase] 10790599-8ec0-4ba9-8116-706dae57109d - Failed to execute isValid() for connection, configure connection test query (null). 2020-06-02 07:54:54,995-0500 https-jsse-nio-8443-exec-24 ERROR dcorrigan 474x39869x2 14bk5l4 192.168.46.27,127.0.0.1 /rest/scriptrunner/latest/resources/com.onresolve.scriptrunner.canned.db.ExternalDatabaseConnection/preview [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: null java.lang.AbstractMethodError at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833) at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:452)

[Peter-Dave Sheehan]

Could the username be allowed to only connect from specific hosts? That would explain the ability to connect from confluence but not from Jira.

Another thing you can try is to connect directly in the console:

import groovy.sql.Sql
import java.sql.Driver
import java.sql.Connection

props = new Properties()
props.setProperty("user", "svc_atlconf_sql")
props.setProperty("password", "putyourpasswordhere")
props.setProperty("DatabaseName", "cornerstone_catalog")
def driver = Class.forName('com.microsoft.sqlserver.jdbc.SQLServerDriver').newInstance() as Driver
Connection conn = driver.connect("jdbc:sqlserver://df2v-catsql-p01.ebsi.corp:1433/cornerstone_catalog;domain=EBSI;useNTLMv2=true", props)
return new Sql(conn)
def sql = new Sql(conn)

def rows = sql.getRows('Select @@version')
sql.close()
return rows

If this works, then you know your username/password are correct.

If this works, then you know your username/password are correct and perhaps there is an issue with your scriptrunner installation

If if does not, then you need permissions with your dba.

BTW, I had an issue a few weeks ago where the scriptrunner resources didn't work (don't remember the details of the errors), but the direct connection like I shared above worked and we resolved it by changing the Java installation on the server. OpenJDK was failing but AdoptOpenJdk was working.

[Stephen Higgins]

@Peter-Dave Sheehan I couldn't get the original script to work so made a few mods to the Class and Connection. I received the following "groovy.sql.Sql@2d383025" result. Based on what you said previously is it safe to assume there is an issue with the installation? If so, do you know how that can be resolved? 

import groovy.sql.Sql
import java.sql.Driver
import java.sql.Connection

props = new Properties()
props.setProperty("user", "svc_atlconf_sql")
props.setProperty("password", "OUR PASSWORD")
props.setProperty("DatabaseName", "cornerstone_catalog")
def driver = Class.forName('net.sourceforge.jtds.jdbc.Driver').newInstance() as Driver
Connection conn = driver.connect("jdbc:jtds:sqlserver://df2v-catsql-p01.ebsi.corp:1433/cornerstone_catalog;domain=EBSI;useNTLMv2=true", props)
return new Sql(conn)
def sql = new Sql(conn)

def rows = sql.getRows('Select @@version')
sql.close()
return rows

[Peter-Dave Sheehan]

I'm afraid I led your astray ... there were a couple of severe mistakes in the code I shared. Try this instead: 

import groovy.sql.Sql
import java.sql.Driver
import java.sql.Connection

props = new Properties()
props.setProperty("user", "svc_atlconf_sql")
props.setProperty("password", "OUR PASSWORD")
props.setProperty("DatabaseName", "cornerstone_catalog")
def driver = Class.forName('net.sourceforge.jtds.jdbc.Driver').newInstance() as Driver
Connection conn = driver.connect("jdbc:jtds:sqlserver://df2v-catsql-p01.ebsi.corp:1433/cornerstone_catalog;domain=EBSI;useNTLMv2=true", props)
def sql = new Sql(conn)

def rows = sql.rows('Select @@version')
sql.close()
return rows

It should return something like this:

[[:Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) ]]

In my instance, I don't have net.sourceforge.jtds.jdbc.Driver, but I had access to com.microsoft.sqlserver.jdbc.SQLServerDriver without having to install it, so it came bundled with Jira.

[Stephen Higgins]

@Peter-Dave Sheehan thanks! I received the following. So is it safe to say we have an issue with our install?

[[:Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Aug 15 2017 10:23:29 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) ]]

[Peter-Dave Sheehan]

Let me add, that if you want to try the microsoft driver, the connection will look like this instead:

Connection conn = driver.connect("jdbc:jtds:sqlserver://df2v-catsql-p01.ebsi.corp:1433;domain=EBSI;useNTLMv2=true", props)

From your earlier logs, that driver appeared available and the correct one to use, but you appeared to supply a bad un/pw.

But I can't guarantee that it's something wrong with your installation. All I can say is that I don't understand why the driver would give you a login error if you are providing the correct login information.

I can replicate the error you displayed, both in the console and in resources when I put a bad password:

But when I use the correct password, I'm able to connect using both methods.

You can also use the console to test various classes to see which are actually available:

import com.atlassian.jira.component.ComponentAccessor
def classLoader = ComponentAccessor.pluginAccessor.classLoader
classLoader.findClass('com.microsoft.sqlserver.jdbc.SQLServerDriver')
classLoader.findClass('net.sourceforge.jtds.jdbc.Driver')

[Alejandro Suárez - TecnoFor]

The error statement is clear, it cannot found the SQL Driver that you are using. You need to upload the driver to the <JIRA installation directory>/lib/ directory

Make sure that the user it's executing Jira (usually "jira") can read that Driver.

You may have to restart the app. 

[Stephen Higgins]

@Alejandro Suárez - TecnoFor I tried using the default driver by selecting the SQL Server (jTDS) link in the setup. It no longer throws an error, but doesn't do anything.  Selecting preview or add just stays on the same screen with no form of notification of success or failure.