How to change passwords for admins in Jira and Confluence in the in-built database

[#1]

Hi all!

Often migrations of Jira and Confluence Servers are required. One of the things you should do is to install your backup on a local Jira or Confluence Server. In most cases the in-built database can be used (H2 database). Sometimes errors happen in H2 database but mostly it works fine. Of course H2 can not be used for production, but for temporary usage the database is ok.

So you have a backup and local instances of Jira and Confluence, then you imported your Jira and Confluence backups and you try to login under the admin user, but you have a error that your user or password is wrong. You should set your own password for the admin user.

First of all you need to connect to your H2 database, and Atlassian did a good job for us. There is an H2 database manager available in your Jira and Confluence instances.

You can find this H2 manager in jira_installation or confluence_installation/webapp/WEB-INF/lib/h2-X.X.XXX.jar. In my case the name of the jar is h2-1.3.176.

Then you have to launch this file (usually double click on the file will do) and you will have a window like this:

 String in the JDBC URL contains the path to your H2 database, which is situated in jira_home/database. You should make sure that you provide the correct path, otherwise an empty database will be create. You can understand that you provided the wrong path by not having Jira database after connecting. Here is an example of an empty database:

Here is how the correct database looks:

You see there are many tables starting with the AO_ prefix. It is a good sign that you have the correct database.

Now you can enter the following SQL queries:

For Jira (password sphere):

update cwd_user set credential='uQieO/1CGMUIXXftw3ynrsaYLShI+GTcPS4LdUGWbIusFvHPfUzD7CZvms6yMMvA8I7FViHVEqr6Mj4pCLKAFQ==' where user_name='admin';

 For confluence (password admin):

update cwd_user set credential='{PKCS5S2}8WEZjkCbLWysbcbZ5PRgMbdJgJOhkzRT3y1jxOqke2z1Zr79q8ypugFQEYaMoIZt' where user_name='admin'

Now you can login into Jira with the admin user using sphere password, and you can connect to Confluence with the admin user using the admin password. 

[Gonchik Tsymzhitov]

Thanks for your article!

Before I used to sed and backup/restore in xml, after that I use RazorSQL.

Now I will do it from built-in functionality :)

Cheers,

Gonchik