nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

MS SQL management studio can connect, Jira cannot...what the heck?

Thomas Theobald

I'm doing a completely vanilla install of Jira here, on a hyper-v VM, connecting to a SQL Server on a hyper-v VM on the same host. SQL Server Management Studio connects just fine using the same login / pw combo. And Jira's install blows a gasket claiming the connection timed out.

Come on, guys. This is just connecting to a DB. This should not be this painful. What the heck is up?

I've been using Jira for over a decade, though admittedly I've only had to do an install three times before, many years ago.

Clue me in here. These BS java problems bust my head in. How can a connection string be so hard to assemble?

Accepted answers

Thomas Theobald

After struggling with this for quite some time, it turns out that JIRA's configuration system is a great big steaming pile.

What's happened here is that JIRA is incapable of self-configuration when dealing with MS SQL named instances (you know, named instances, the most common and best-practice method of setting up MS SQL?).

Jira creates a dbconfig.xml file in its Application Data subdirectory, which when told to set up an MS SQL database, creates something like this (wherever you see all-caps "YOUR_SOANDSO_HERE", these are where the values you give it will appear):

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>YOUR_SCHEMA_HERE</schema-name>
<jdbc-datasource>
<url>jdbc:sqlserver://;serverName=YOUR_SERVER_HERE;portNumber=1433;databaseName=YOUR_DB_HERE</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<username>YOUR_LOGIN_NAME_HERE</username>
<password>YOUR_PASSWORD_HERE</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>
</jdbc-datasource>
</jira-database-config>

In the case of a database with a named instance, the system needs more information, which neither the configuration tool in the bin directory, nor the installation screen can give it. Which means you are borked from the get-go.

To correct this travesty, you must change the <URL> line to reflect the following:

<url>jdbc:sqlserver://YOURSERVERNAME\INSTANCE;instance=INSTANCE;databaseName=YOURDBNAME</url>

Notice the following:

1. The semicolon preceding "serverName" is removed.

2. The serverName should include the instance name, just as you would when logging into SSMS or literally any other software program on this freaking planet.

3. You must add an additional parameter, "instance=[insert your instance name here]", following the server name and preceding the databaseName.

4. The parameter "portNumber=1433" is removed (including a port # reference on an instanced connection string will confuse the connection and override the instance with the port number).

There are a few threads running around on the net which allude to various aspects of this solution, but I was never able to find all of the points needed to correct the situation in one post. Hence, I am attempting to include them all here.

The final file should read something like this (substituting your own values where I have "YOUR_SOANDSO_HERE", of course), as this is taken directly from the final working dbconfig.xml which got me up and running:

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>YOUR_SCHEMA_HERE</schema-name>
<jdbc-datasource>
<url>jdbc:sqlserver://YOUR_SERVERNAME_OR_IP_HERE\YOUR_INSTANCE_HERE;instance=YOUR_INSTANCE_HERE;databaseName=YOUR_DB_NAME_HERE</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<username>YOUR_LOGIN_NAME_HERE</username>
<password>YOUR_PASSWORD_HERE</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>

<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

<pool-test-while-idle>true</pool-test-while-idle>
<pool-test-on-borrow>false</pool-test-on-borrow>
</jdbc-datasource>
</jira-database-config>

I hope this helps someone else who runs into this problem, at least moreso than the less-than-worthless answers I got in my thread above.

All comments

Nic Brough -Adaptavist-

It's not the connection string (unless you've deliberately tried to do odd things with it and got them wrong). The installers create perfectly valid connection strings and tomcat connectors, and when it goes wrong, you'll almost certainly find it's something in the connection, not what Jira has. Or even Java. Java actually has quite a simple and solid database connection layer, and when you get problems with it, it's usually the database, network or OS config.

As you say, it is "just connecting to a DB", but the place to look is at the connection failures, not Jira's installation.

Connection timed out implies that, yes, even locally, you may have it firewalled off, or the database has security settings that allow an admin using the management studio to connect, but ignore us more mortal users, or or or.

I'd start with a closer look at the users. What user does Jira (try to) run as? Can that user connect with the Studio software?

Thomas Theobald

That's exactly my point of frustration - using SSMS my Jira login/user can connect just fine to the database in question (freshly created for the use of Jira Software).

The shots below are the users of the new database (JiraSystem in this case), then the SSMS login which I use to connect from the Jira server (successful connection), and then the installation details from the Jira install screen.

The "test connection" has just been timing out for me. That's why I'm banging my head here. It's probably some tiny detail, small and stupid, that I'm overlooking. But where I sit, it looks like all the stars are aligned correctly...it just bombs on me. And it shouldn't - if SSMS can connect and be happy, what's up with the Jira installation? What didn't I give it?

SQL Users.JPG

SQL Connect.png SQL Jira Installation.png

Nic Brough -Adaptavist-

> if SSMS can connect and be happy, what's up with the Jira installation?

Probably nothing.

Jira fails to connect messages are thrown when it tries to talk to the database and gets a failed response. If it were a problem in the Jira code or setup, you'd be getting crash messages, not "the database isn't talking to me"

You say you're using SMSS, but are you running that on the Jira server? If not, then there's a good chance that the problem is the network - the Jira server can't get to the database server, or if it can for the initial login, the data exchange is failing for some reason. If you are testing from the same machine, then you'll need to look at the database side - you need that to tell you why it's not talking to Jira

Thomas Theobald

Thanks for the reply - yes, these are all taken from the Jira server. SSMS connects from the same VM to the SQL database from the Jira server. It's all the same client talking to the server.

Thomas Theobald

After struggling with this for quite some time, it turns out that JIRA's configuration system is a great big steaming pile.

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>YOUR_SCHEMA_HERE</schema-name>
<jdbc-datasource>
<url>jdbc:sqlserver://;serverName=YOUR_SERVER_HERE;portNumber=1433;databaseName=YOUR_DB_HERE</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<username>YOUR_LOGIN_NAME_HERE</username>
<password>YOUR_PASSWORD_HERE</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>
</jdbc-datasource>
</jira-database-config>

To correct this travesty, you must change the <URL> line to reflect the following:

<url>jdbc:sqlserver://YOURSERVERNAME\INSTANCE;instance=INSTANCE;databaseName=YOURDBNAME</url>

Notice the following:

1. The semicolon preceding "serverName" is removed.

2. The serverName should include the instance name, just as you would when logging into SSMS or literally any other software program on this freaking planet.

3. You must add an additional parameter, "instance=[insert your instance name here]", following the server name and preceding the databaseName.

4. The parameter "portNumber=1433" is removed (including a port # reference on an instanced connection string will confuse the connection and override the instance with the port number).

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>YOUR_SCHEMA_HERE</schema-name>
<jdbc-datasource>
<url>jdbc:sqlserver://YOUR_SERVERNAME_OR_IP_HERE\YOUR_INSTANCE_HERE;instance=YOUR_INSTANCE_HERE;databaseName=YOUR_DB_NAME_HERE</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<username>YOUR_LOGIN_NAME_HERE</username>
<password>YOUR_PASSWORD_HERE</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>

<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

<pool-test-while-idle>true</pool-test-while-idle>
<pool-test-on-borrow>false</pool-test-on-borrow>
</jdbc-datasource>
</jira-database-config>

I hope this helps someone else who runs into this problem, at least moreso than the less-than-worthless answers I got in my thread above.

Nic Brough -Adaptavist-

So, nothing to do with the Jira setup or the connection, but you were feeding it the wrong jdbc connection settings.

Thomas Theobald

Oh, and P.S. - Jira also never bothers to tell you that you don't have the correct version of the JVM installed. Nor does it carry a copy with it to install for its own use. I totally forgot that I had to get past that blocker before I even could discover that Jira doesn't know its [expletive deleted] from a hole in the ground when it comes to SQL Server.

So swing by the Java site and use the Windows self-download to your server before you get rolling.

Thomas Theobald

"So, nothing to do with the Jira setup or the connection, but you were feeding it the wrong jdbc connection settings."

Incorrect, numpty.

I gave it exactly the correct data it needed - JIRA failed to assemble its own connection file correctly. The installation itself has absolutely zero concept for a SQL Server "instance" - which is why it includes the port number regardless of whether you specify an instance in the hostname or not. config.bat is equally ignorant.

Go back and re-read that. My settings had the servername\instancename correct. I didn't tell it to insert that semicolon before the servername param. If whatever genius coded that form had spent an extra 30 minutes on QA to test against monolithic as well as named-instance servers, (s)he would have seen what a gore-fest that installation was.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira-software

jira-software-server

ms-sql