Locked out of Jira

[Philip Schroeder]

So I just connected our Jira Service Desk to Active Directory. After doing this, it appears that it locked out our internal administrator accounts. We can login with our standard AD accounts but not the admin accounts. How can we re-enable the admin accounts?

[Andy Heinzer · Accepted solution]

Are the local jira admin accounts having the same usernames as the accounts in AD?  If so, then you should understand that Jira can't have two users with the same username.  What happens is that when you add a new user directory with the same usernames as an existing directory, Jira only uses the top ordered user directory for authentication that account.

If you have no Jira administrator accounts that can login because of this change, then please follow this guide:  Retrieving the JIRA Administrator

You could use this guide to create an internal Jira administrator account and then give that account system admin rights.   I would recommend that you choose a username that does NOT exist in the active directory, this way there will not be confusion over which credentials should be used for this account.   You can then login to Jira with this newly created account. 

Once you are able to login as a local admin, you can the change the order of the user directories as they appear in Jira.   See Managing multiple directories.  

What has likely happened is that your user directory that you just added is in the top ordered position, however if that user directory does not have the necessary groups to grant application access then the accounts can't actually login to Jira, even though the password credentials might be correct.  Since Jira controls application access based on user group membership, this is most likely the explanation for this situation.

By reordering the user directories in Jira you should at least be able to undo your changes for previously existing users in the local directory.   

I suspect that when you made this change, your Jira admins might have been using the Jira password when they should have been using the AD password.  If the account names are the same, this is plausible.   You could unlock the account via AD in order to let that user login again too.

[Philip Schroeder]

Yea this is what happend. The internal accounts are the same as the AD accounts. I tried following the instructions to create the account but the query isnt working. The error I get says.

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_app_user'. Cannot insert duplicate key in object 'dbo.app_user'. The duplicate key value is (999999).
The statement has been terminated.

[Andy Heinzer]

Yeah, that error means you tried to insert a user account when one with the same name already exists in that table, and since this table won't allow duplicate records, you can't insert this value here.  This is why we need to make sure that the username selected for this internal account does not already exist within Jira.

Did you use the exact query in that KB?  Or did you change the username here?

Could you run these SQL queries and let me know the results:

select group_id from globalpermissionentry where PERMISSION='SYSTEM_ADMIN';
select child_name, directory_id from cwd_membership where parent_name='jira-administrators';
select * from cwd_directory;

The first is to determine what groups grant system admin rights.

The second query shows you all the users in the jira-administrator group (if the first query returns different group name(s), you will need to adjust this query and run it for each group returned in query #1.

The 3rd query tells us the specifics about the directories. With this last bit of information, it might be possible for us to reorder the directories as a means to get past this problem and login as an admin once more without have to try to make an account in SQL.

[Philip Schroeder]

Thank you. I have attached a screenshot of the results. For the third table, Jira internal is position 1 and active directory is position 0.

[Andy Heinzer]

Hi Philip,

In the last query I was looking to see the value in the directory_id field for each, but it looks like it could not be displayed in the screenshot.

But we can still reorder these directories via SQL

1. Stop Jira
2. run

   update cwd_directory set directory_id=0 where id=1;
   update cwd_directory set directory_id=1 where id=10000;

3. Start Jira again

This should reorder the directories to allow the internal to be on top again.  From there you can then login as any one of the 3 sys-admin accounts, using the previous username/passwords that Jira has stored in the internal directory for those accounts.  If you can't remember those passwords, then you can also update those passwords via SQL with a command such as

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

If you replace that example username, this sets the password to be 'sphere' (no quotes).

This should at least let you login to Jira as an admin once more.   From there I would recommend that you create an internal system admin account in Jira's user management, and give it a username that does not exist in the AD.   This way should you reorder the user directories in Jira later on, this account can still login with the internal account credentials.  

[IT Tech]

That did it! I had to change the column heading but other then that it worked great and I am back in!

[Philip Schroeder]

Oops I posted from our generic account. Thanks again Andrew!

[jndeverteuil]

I'm really not sure if what I'm going to say make sense, but you could see if you have access to the database and edit the groups of the users to add the jira-administrator group manually.

It's really just a guess of something you can try and others could probably have better solutions!

[Philip Schroeder]

thanks. I am not to familiar with SQL query's though. Is someone able to help?