Monday, November 19, 2012

Stash - Case Sensitive Collation Detection at the Database Level vs Server Level


Using MS-SQL 2008 & Stash 1.31 and the database migration wizard (after a clean install using the embedded hsql db), a new DB for stash set to SQL_Latin1_General_CP1_CI_AS for collation (CI = case insensitive) made the database migration wizard spit out the following error when I clicked "Test":

Stash requires a case sensitive database, but the target database is case insensitive. For information about configuring databases for use with Stash, please refer to the Stash documentation.

I set the database to SQL_Latin1_General_CP1_CS_AS, yet Stash's DB migration wizard continues to insist it's still case insensitive. I restarted Stash, it still says the DB is case insensitive. I took the DB offline and then back online, and Stash still says it's case insensitive.

I put some dummy chars in the name of the database, and Stash's DB migration wizard still says the "the target database is case insensitive". So I'm thinking it's not querying the database, it's querying the server default. And since this is a multi-tenant DB server, the owners prefer it to be SQL_Latin1_General_CP1_CI_AS by default. Regardless, this server also hosts a Jira database, which has to be set case insensitive.

Your products should work together a little better, Atlassian.

I can't go forward with this tool until I have it using a real database. After a couple hours of mucking around, I'm answering my own question, but it's not an optimal answer.

Stash apparently stores its external database configuration in a stash-config.properties file, in the root of the stash_home folder. When you install Stash using HSQL, it doesn't create the file. The absence of the file in fact prompts Stash to use its embedded HSQL DB.

Atlassian documentation (https://confluence.atlassian.com/display/STASH/Stash+config+properties) gives an example for a Postgres file - I looked at a Fisheye config file that uses MS-SQL and cobbled together one for Stash that also uses Windows authentication, like thus:

jdbc.url=jdbc:jtds:sqlserver://servername:1433;databaseName=database;domain=company.com;
jdbc.user=jirauser
jdbc.password=password
jdbc.driver=net.sourceforge.jtds.jdbc.Driver


I saved the file, restarted Stash and the web UI was back on the initial setup questions, this time populating a MS-SQL DB instead of HSQL.

So I'm "working", but there still seems to be a bug here. The migration wizard appears to be querying the server's collation, not the database's collation. Blindly crafting a config file to bypass faulty logic != acceptable workaround.

I posted this question & eventual answer on the Atlassian Answers forum, and Atlassian support down-voted my workaround; in at least v1.31, Stash's case sensitivity check is bypassed once the stash-config.properties file is present & valid, so I'm playing with fire here ;)

I'll mark it as answered. Someone else can create a defect ;)

-Kelly

Thursday, November 1, 2012

Jira Email Listener Failure?


Scenario: Either out of the blue or after an upgrade to either your Jira server or Jira itself, emails to Jira - to append comments onto an existing issue for instance - are seemingly ignored by Jira with no obvious errors.


In Jira, go to Administration (assuming you're an admin), Incoming Mail, "Edit" the Jira incoming mail listener you have setup and click "Test". If you get the following exception the rest of this article is for you:
SunCertPathBuilderException: unable to find valid certification path to requested target

Solution: The exception SunCertPathBuilderException: unable to find valid certification path to requested target means you're probably using SECURE_IMAP as your mail protocol. SECURE_IMAP makes a secure connection to your email server and needs the x509 (essentially SSL) cert the email server was secured with to be trusted by the account/process running Jira. Most companies run Jira internally and secure their (internal) mail server with a self-signed cert, so the cert has to be manually trusted for it to work (unlike a VeriSign cert, for instance).

The error message (SunCertPathBuilderException: unable to find valid certification path to requested target) simply means that the Jira process can't find a trusted public cert in a keystore with the same name as your email server.

So - either:
A) The email server name changed or
B) For some reason the cert is no longer in "the" keystore.

Solution to A) - the email server name changed or they changed which email server you can use -
1) Either install openssl on the server running Jira or on your local workstation, and use it to retrieve the public cert from your mail server:

openssl s_client -connect [email.servername.com]:imaps

It should open up a connection and you should see the server's public certificate in plaintext right in front of you - grab everything from Begin to End like thus:

-----BEGIN CERTIFICATE-----
MIIFjjCCBHagAwIBAgIKVggUuwAAAACkATANBgkqhkiG9w0BAQUFADBIMRMwEQYK
[snip]
3xUBlGXakDBkbL1euN3AR2yAZcXi2LqFrAz0mWKvR3icew==
-----END CERTIFICATE-----

and save it to a text file, call it "imap_cert.cer". 

2) Import it!
keytool -importcert -keystore /new_jre_location/lib/security/cacerts -file imap_cert.cer  -alias [email.servername.com]

Close to real world example:
jira@jira_server:/opt/atlassian/jira/jre/bin$ keytool -importcert -keystore /opt/atlassian/jira/jre/lib/security/cacerts -file ~/imap_cert.cer -alias mail.company.com

When you're prompted to trust it, type "yes" and enter. 
Restart Jira for the change to take effect. 


Solution to B)
In my case, the cert failure to be found was because I cleaned up an install of Jira and put it on the recommended straight-and-narrow path from Atlassian, which includes using the JRE bundled with Jira (at least for version 5.1.x for Linux) vs a Java runtime you installed manually or as a package.
It turns out that by default Jira uses the "cacerts" keystore in its Java JRE home, not the user keystore; so changing the JRE location will break the imap cert installation path (which is what happened to me).

Two obvious options:
1) Re-grab the x509 cert from the imap mail server (see Solution A)
2) If you can't use openssl and you have the JRE you were using before still, you can export the public cert from the old keystore and import it to the new keystore. That's what I did, and it worked out very well.

Three steps:
1) Get the name of your cert - this doublechecks you actually have the prior cert and it's named correctly.
keytool -list -keystore /old_jre_location/lib/security/cacerts
This dumps the summary contents of your old keystore; get the exact name of the cert alias so you can export it - normally this is the dns name of your email server (what I refer to as )
2) Export it!
keytool -exportcert -keystore /old_jre_location/lib/security/cacerts -file imap_cert.cer -alias [email.servername.com] 
3) Import it!
keytool -importcert -keystore /new_jre_location/lib/security/cacerts -file imap_cert.cer  -alias [email.servername.com]
By default, the keystore password in the JRE bundled with Jira is "changeit".
Make sure you trust it when prompted and it takes a restart of Jira for this to take effect.

Good luck!

-Kelly Schoenhofen