I recently upgraded the home network here, and pulled a load of stuff back in from my AWS cloud. Not because I thought AWS was bad or anything, I just needed to satisfy some geekdom here in the house, and this seemed like a good way.
So…long story short, in the last eight weeks or so I’ve become comfortable with SuperMicro mainboards, lots of RAM, replacing Xeon CPU coolers, setting up iSCSI on Synology NASes, Installing and maintaining Hyper-V (I was going to go vmWare, but the cost for me as a personal user was prohibitive…and I refuse to run command-line trash unless I absolutely have to).
And Installing Jira.
First problem I encountered, Jira forgets to let you know that you don’t have the correct JVM version running on your machine, and it doesn’t bother to carry it along with itself, nor does it direct you to pull a copy down, so swing on by the Java page and get the latest-greatest onto your server in advance.
Now, Jira’s base demo install with its own bundled database is pretty simple. Pull it down, let it run, record the account names you give it, voi-la, done.
But if you try to get it to load on MS SQL Server, you better have a steady supply of blood pressure medicine, or you’d better read on.
Pertinent details: this is running on a Microsoft Windows Server 2019 Standard edition virtual machine, hosted within a WS2019 DataCenter edition host computer. The guest VM has an external switch, so it can reach out to the internet (for now, might change to internal only in a while) when it needs to.
My SQL server uses a named instance, which is both good sense and as it happens “best practice” in Microsoft circles. This seems to have escaped the notice of the folks at Atlassian, though, because Jira doesn’t know a goddamned thing about named instances. Maybe that’s because the folks who write it are using Java, and that’s always been a solution looking for a problem. Anyhow, my personal gripes with Java aside, for some reason Jira appears to be ignorant of how MS SQL uses named instances.
When it installs, Jira creates a configuration file called “dbconfig.xml” within its installation directory (to be specific, in the [Atlassian directory]\Application Data\Jira directory). Pretty simple little file, contains only the details necessary to connect the JDBC driver to the host database for your Jira install. When Jira’s service app wakes up, it reads from this file in order to get its parameters set correctly.
In order to get that file built properly, Jira will ask you for details regarding your setup during its installation. It’s the second thing you’ll see when you start up. Looks just like this:
As you can see here, you pick your DB type from the drop-down, give it the host name as either a resolvable name or an IP (in my case I used a name), a port (1433 is standard for SQL Server, more on this later), the name of the database you created for Jira to use, the login name, the login password, and a schema name for it to use.
Most developers would test this once the wrote a step of this importance. You’d think a company with the kind of cash Atlassian has could afford a proper QA team to put this through its paces, wouldn’t you? Yeah, I was surprised as well. The install screen’s code completely borks up the dbconfig file.
Once you realize that the regular install will simply not proceed (because “test connection” and “next” both bomb, timing out because it can’t even connect to your server), you’ll end up discovering that there’s this little Java applet called “config” which you can invoke from within the Jira directory. You have to get to it through a command line, but it has a handy little GUI into which you enter data similar to the above. But then config borks up the file in a similar fashion.
What you end up with is something like this (value you entered appear as “YOUR_SOANDSO_HERE” in Red):
<?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_NAME_HERE</schema-name>
<jdbc-datasource>
<url>jdbc:sqlserver://;serverName=YOUR_SERVERNAME_HERE;portNumber=1433;databaseName=YOUR_DB_NAME_HERE</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<username>YOUR_USERNAME_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>
Schema name is fine. That’s no problem. Same with your user name and password. The problem is in the assembly of the “URL” line there. In the case of a server running MS SQL with a named instance, Jira is going to need more, and correct, information. That URL line will have to end up looking something more like this:
<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 MS SQL, and will override the instance with the port number – so if your instance uses a different port #, that’s yet another problem).
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. I posted to this effect on the Atlassian community as well (where one of their “community leaders” had some particularly bad advice).
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_NAME_HERE</schema-name>
<jdbc-datasource>
<url>jdbc:sqlserver://YOUR_SERVERNAME_HERE\YOUR_INSTANCE_NAME_HERE;instance=YOUR_INSTANCE_NAME_HERE;databaseName=YOUR_DB_NAME_HERE</url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<username>YOUR_USER_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>Notice you have to have the instance name in both the servername, and in the new “instance” parameter. And you delete the port parameter if it is present. And before you ask, yes, I do rankle at the thought of storing my password in an unencrypted text file on disk. However, if someone has access to that disk, there are a host of far larger problems that will have already taken place. All of which are bigger than someone having my Jira SQL password.
And that should get you past the problem with dbconfig.xml. I hope this helps someone else who will of course run into this problem, at least until 2024, which is when Atlassian will be discontinuing sales of its software products and moving everyone onto the Atlassian cloud. (I have to wonder if they made that decision because they had so many damned problems with their installations.)
Anyhow, I anticipate more than a few customers will be in process of migrating away from Atlassian products when that happens. I know of several major firms who won’t stand to have their information stored on a non-approved cloud platform, and Atlassian’s will have some serious hoops to jump through to win approval. As well, there are lots of little firms who simply don’t want to get tied into a monthly bill if they can run something on-prem.
Now I don’t want to give the impression here that I hate Jira. Quite the contrary. I’ve been using it for over a decade (almost two), and the only reason I fought with this goddamned thing for so long was because I want it to work, I want to use the damned thing. And I wrote this up in the hope that others will be able to work with it, too. Am I ashamed of whichever coder made this grotesque error? And the QA staff that let it escape into the wild? You bet I am. And I hope the shame of this makes them fix it.
But if they don’t, well, that’ll be a sad day when I finally decide enough is enough and end up moving to GitLab or something.