How to change admin user password in Atlassian Jira and Confluence in embedded database (H2)

Hello!



This article will talk about how to change the admin user password in the built-in Atlassian Jira and Confluence database.



Often, when migrating Jira and Confluence servers, it becomes necessary to put Jira and Confluence backups on your local server in order to analyze the state of Jira or Confluence or simply clean Jira and Confluence from unnecessary objects.



To do this, it is not necessary to deploy postgres, oracle, mysql or sql server. Enough to use the built-in H2 database. H2 cannot be used in production, as it may fall at the most inappropriate moment, but for some temporary unimportant installation it is well suited.

')

So, we installed local Jira and Confluence and imported backups there. We start to login with the user admin and we have an error that the username or password is incorrect.



It is necessary to correct the password in the database. How to do it?



Here the Atlassian developers took care of us. The deployed Jira and Confluence includes H2 database manager, which is located in the following way: jira_installation or confluence_installation / webapp / WEB-INF / lib / h2-XXXXX.jar. In my case, the name of the jar file is h2-1.3.176.



Run this file (usually double-clicking helps) and we get this screen:







The JDBC URL field contains the path to the H2 database, which is located in jira_home / database. When specifying the path, you need to be careful and indicate the correct path, since if the path is not correct, the error will not appear. An empty database will be created. You can distinguish an empty database as follows. There will be only one schema in the database, and it will be called INFORMATION_SHCEMA. Here's what she looks like:







The correct database looks like this:







We see a large number of tables with the prefix AO_. This is a good sign that we have the right database.



After that we can execute our SQL queries to set the password.



For Jira (admin user password is sphere):

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

For Confluence (admin user password admin):

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

Now you can turn off and run Jira and Confluence.



It is important to know that the H2 database supports only one connection at a time. Therefore, to run Jira and Confluence, you must exit the H2 database manager. And when working in the H2 database manager, you should not have Jira and Confluence running.