Not able to create a local admin id directly in the database

Question

Hi,

the jira version in question is 5.2.11 . The database is default hsqldb.

I have done a complete export from the jira production environment and imported into the test server. The import has been successful. the production environment is using crowd. test sever does not have access to the production crowd. after the import as expected it overwrote the user directory settings and now I am not able to login using local admin id. So I followed the instructions given on page https://confluence.atlassian.com/display/JIRA052/Retrieving+the+JIRA+Administrator#RetrievingtheJIRAAdministrator-2.Setthepassworddirectlyinthedatabase\

but when I give command to create a local admin id

insert into cwd_user values (999999,1,'localadmin','localadmin',1,'2012-01-04 19:49:05-08','2012-01-04 19:49:05-08','local','local','admin','admin','local admin','local admin','localadmin@localadmin.com','localadmin@localadmin.com','uQieO/1CGMUIXXftw3ynrsaYLShI+GTcPS4LdUGWbIusFvHPfUzD7CZvms6yMMvA8I7FViHVEqr6Mj4pCLKAFQ==');

it throws an error message as follows:

Wrong data type: java.lang.NumberFormatException: For input string: "05-080000000"

I have a crowd on the test server also which is replica of production crowd but in order to make jira point to it I need to login into it. or please help me if there is any sql command to set user directory configuration in database directly.

This is a bit urgent for us because we have to upgrade this instance to the latest jira and we want to test the upgrade first as recommended by atlassian

Regards

sanan

Andy Heinzer · Answer

There are other possible reasons this can happen.  For example, if the new account called 'localadmin' exists in both the Jira internal user directory and some other user directory Jira is connected to, then the order in which those directories appear in Jira matter.

You can only login to the top ordered directory in jira where that username exists.

It's also possible that you might have a different password for that account, if it exists in the other user directory.

You can see if you have other directories via SQL with

select * from cwd_directory;

There should be a field in there called 'directory_position' that indicates where that directory appears in Jira.  0 = the top, 1 = second position, 2 = third position and so on.

If you have another directory ordered higher than the internal directory where you created that account, then it is possible there already exists another account with the same name, but a different password.

If this is the case, then you can change the directory position to place the internal directory in the highest position '0'.  If you make that change via SQL, then you need to restart Jira before you can login with localadmin / sphere combination.

Typically you only see the captcha on login when you have failed to login with that account beyond a set threshold, (usually 3-5 attempts).   However if you can login successfully, even once, with the captcha and correct username/password, then the captcha should be reset.

sanan sanan · Answer

Hi Branden,

Thanks for your response.

Yes, your are right and that is why I have now moved to mysql as that is what is used in the production environment also.

Also, this time, after switching to mysql, I was able to create the localadmin id. But I am not able to login using that id.

On the logon screen, when I give localadmin as id and sphere as password, it just keeps re-appearing with asking to enter captcha everytime.

I dont know what wrong am I doing now.

Regards

sanan

somethingblue · Answer

Hi sanan,

I would recommend migrating to a Supported database.  The reason for this is so you can see how the database you're using would behave in production so if you're using MS SQL use a test MS SQL database since the built in database may behave differently than your production database as we do not recommend using the built in database for production use.

Cheers,

Branden