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? 

[Thomas Theobald · Accepted solution]

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.

  T

[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.

[Graham Reynolds]

Hey @Thomas Theobald just to let you know you aren't crazy, my JDBC line in the dbconfig was just as messed up as yours and I never even edited it manually.

And the hilarious part is that I installed Confluence at the same time and it had no issues during setup of the DB understanding that I was using a named instance.

[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?  

[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.

[Nic Brough -Adaptavist-]

Ok, then that pretty much proves that it's something on the database that is going wrong, ruling out the network.  You'll need to debug why the database is not talking back to Jira when these connections are dropped.

[Thomas Theobald]

Hmm...for the last 28 years of dealing with SQL Servers of various stripes, when you get one piece of software that can talk to the database using an identical environment (and credentials), and a second piece of software can't, I don't think that's ever pointed a finger at the database server.  

In this case we have the same operating environment - even the same machine - for both SSMS and Jira.  Other software can also connect to the database server from multiple nodes.  

This has to be some kind of configuration issue in what is being given to the Jira installation.  That's why my question was "What didn't I give it?"  Because if software A can connect to database C, and software B cannot, the problem rests with B, not with A or C.  

[Nic Brough -Adaptavist-]

In the last 35 years of dealing with all sorts of SQL servers, messages like

The "test connection" has just been timing out for me.  

Have always turned out to be down to the setup of the database server.

The timeout on the Jira side is telling you "the database is not talking back to me".  You need to ask the database why it is not talking back.

I would want to check the Jira logs to establish that it really is a timeout on the connection, then hunt down why that might be happening.  You need to ask the database why it isn't talking back to Jira.