How to migrate users from one Directory to another using SQL.

[Gregory Hicks]

Good Afternoon everyone,

As the title says I have been working on a way to migrate users from one LDAP directory to another in the new 5.1.

Reason:

The reason I am doing this is because when I upgraded JIRA to the new 5.1 the OSuser.XML file did not update correctly. I read that if this happens and you have multiple LDAP directories written remove all but one. I follow these instructions and it worked. I kept the main LDAP directory used by 90% of our staff (icf-hq). After the install I edited the OSuser.xml file back to the way it was thinking happy thoughts and high fives to myself :).

A few weeks later Users using the icfi domain (the other domain we use) could not login. Turns out after working with JIRA support that the OSuser.xml file is no longer reconized.?.?!?!? FINE OK, so I created a ICFI Directory in the JIRA GUI thinking that would resolve the issue..Nope. Since the XML file didn't upload correctly JIRA placed those users in either the ICF-HQ domain or Internal JIRA. *Face-Palm*

I then began searching for a way to manually move users from 1 Directory to another using the GUI and yep you guessed it, not possible. (JIRA support, if you read this, this is a HUGE HUGE issue and needs to be resolved ASAP. Not being able to move users individually from one directory to another is a BIG problem. If a user is assigned to a directory and their account gets migrated they can no longer login because JIRA can not find their account.) The only way that Suport and I found to fix this issue was to set the ICFI Directory to copy user and add them to jira-users group. (What this does is if a user is in the wrong directory and when he/she tries to login it makes a copy of the account and moves it to the correct Directory making sure it has jira-users group permissions)

This is fine for 1 or 2 people but my problem is much worse. At some point we are planning to migrate all 600 people from the ICF-HQ AD to the ICFI AD. This is a Company migration that is out of my controle. That means all users that were using the ICF-HQ domain would no longer be able to login to JIRA. The bigger issue is that it's not going to be everyone at once but 100 - 300 users at a time. So I can't just use the migration tool built into JIRA. (JIRA Support, if you edited that tool to let you choose people individually that would be the perfect fix in my opinion).

SQL Solution:

OK, so i did some more research here is a link I used to fix the problem. https://jira.atlassian.com/browse/JRA-24213

Here are the directions I made:

Moving Users from 1 directory to another using SQL.

!!!!!!MAKE A BACKUP OF YOUR DATABASE BEFORE DOING THESE STEPS!!!!!!

1. User has already been created and we are moving him/her from the old directory to the new directory.

2. Login to JIRA database.

3. Open dbo.cwd_user (note that the user directory_id is going to be the old ID number in my case 4, that number needs to change to the new directory_id 10000).

4. Open dbo.cwd_membership (note that the user directory_id's will have the old directory_id # 4. Those numbers will need to change to the new directory_id # 10000).

5. First script that needs to be run is the..

update dbo.cwd_user set directory_id=<Your ID Number> where USER_NAME='<USER NAME OR ID>';

This script will move the user from the old directory_id to the new directory_id. (meaning it will move the user from the old LDAP directory icf-hq to the new LDAP directory icfi). If you go back to the dbo.cwd_user table and hit the execute button you will notice that the directory_id number has changed to 10000.

6. Now you will need to move all the internal groups that were assosiated with that user to the new LDAP directory.

update dbo.cwd_membership set directory_id=<Your ID Number> where child_name='<USER NAME OR ID>';

This script will move all the groups for that user to the new LDAP directory. If you go back to the dbo.cwd_membership table hit execute you will see the directory_id's have changed to the new directory number 10000.

7. Now that you have moved the groups over you need to change the parent ID of each group to the new directory parent ID. you will need to run this script for each group changing the parent_id number and the parent_name alias. If you don't you will not be able to remove these groups from the users account.

update dbo.cwd_membership set parent_id=<Your ID Number> where parent_name='<Your Group Name>' AND child_name='<USER NAME OR ID>';

update dbo.cwd_membership set parent_id= <Your ID Number> where parent_name= '<Your Group Name> ' AND child_name='<USER NAME OR ID>';

update dbo.cwd_membership set parent_id=<Your ID Number> where parent_name='<Your Group Name>' AND child_name='<USER NAME OR ID>';

8. Now you will need to reset the JIRA service on the server.

Note: NEVER EVER EVER EVER change the User membership parent ID for JIRA-User!!!!!!!!!!!!!!!!! If this is changed the user will NOT be able to login. I thought this would need to get moved over also but just doesn't work. It wont allow the user to login.

9. Load the dashboard and try to login.

I was able to get that user moved from the old directory to the new directory, along with his groups, and was able to login with no problems. I have only tested this is stage for one user so I need to do some more testing before production but this should help those in my situation.

Let me know if you have any questions reguarding this subject. I'll do my best to answer.

Greg

[Renjith Pillai]

You can also migrate users between to LDAP directories by simply creating the new LDAP directory in JIRA and allowing to synchronize. Once finished, the everything will work as before provided the following are met:

• The usernames that come from the new LDAP matches exactly what was present before
• Group information should be exacty same in the LDAP if the JIRA groups were being derived earier from the LDAP groups.
• This can also handle partial movement to the new LDAP. JIRA will continue to authenticate with the old LDAP for all the users which are not found in the new LDAP.

Do a test of this in a development server before performing in the actual server.

[Gregory Hicks]

Can you further expand on this? I have both LDAP directories in JIRA and people are curently using both. I would go to Administration>User Directory>Edit (the new directory everyone is moving too) check the box for Copy users on logon and also check Synchronise Group Memberships? Let me know if this is correct.

Greg

[michal.komolowski]

Hi 

Instruction works fine but if you don't change cwd_user_attributes info about login count, last login will be lost , so directory_id in cwd_user_attributes must be changed too.

[Michael Simon]

Gregory,

I'm trying to move from a JIRA directory to a Delegated AD Authentication. So if I understand that correct, I actually need both directories to be configured. How do I get people's password to start being looked up via AD instead of JIRA? I tried moving the Delegated AD to the top in the ordering. But people were no longer able to login and user ids starting duplicating in the cdw_group.

I've tried to migrate a user so that their directory_id is set to the 10001 in cwd_user, cwd_user_attributes. Still no luck.

I noticed that the cdw_groups now has a duplicate jira-users group both directory_id 1 and 10001. Do these groups need to exist in the AD server? I dont' want to deal with gropus in AD only in JIRA.

Is recycle needed for any of the settings to take place?

[Gregory Hicks]

Do you have 90 users total or 90 of like 200 users? If you are moving everyone, JIRA has a tool in there system Users>User Directory>migrate users from one directory to another.

This tool will migrate every user in that Crowd directory to Jira internal group. After that you can go through your database and just delete rows.

Let me know if this answeres your question

Greg

[Naren]

Thanks Gregory, I have total 90 users in my JIRA. I am looged in as a 'jira-administrator' and using JIRA 4.3.2, but under the User Directories, I am unable to see the 'Migrate Users From One Directory to another'.

[Gregory Hicks]

Sorry I missed this comment, Have you been able to resolve the issue? I thought you have the most recent version of JIRA which is what this work around is for.

[Naren]

Hi Gregory,

Good workaround!

Following is the case w.r.t my JIRA 4.3.2 DB integrated with Crowd (which I want to get rid off)

Let's say I have same set of users present in two Directoris i.e., JIRA Internal Directory and Crowd Directory. I want to remove the Crowd Directory, which is currently in use and switch back to the JIRA Internal Directory, which is inactive.

To update the DIRECTORY_ID in the CWD_USER table, I can see for the two DIRECTORY_ID - 1 and 10000, I have the same set of users -

DIRECTORY_ID USER_NAME

10000 abc

1 abc

To update the CWD_MEMBERSHIP to move all internal groups associated to that user, I can see the following -

CHILD_NAME DIRECTORY_ID

abc 1

abc 10000

To change the PARENT_ID of each group to new directory PARENT_ID, I can see -

PARENT_ID PARENT_NAME CHILD_NAME DIRECTORY_ID

10000 jira-users abc 1

10323 jira-users abc 10000

10327 internal-group abc 10000

10003 internal-group abc 1

How can I remove the entry for DIRECTORY_ID = 10000 in CWD_USER and CWD_MEMBERSHIP and make the entries for DIRECTORY_ID = 1 active, so that I can remove Crowd application from my server.

This is the case with the ~90 users in my JIRA. Can you please sueggest approach to get rid of this Crowd Directory and switch back to the JIRA Internal Directory having DIRECTORY_ID = 1, before I plan to upgrade JIRA 4.3.2 to latest.

Thanks,

Naren.

[PauloP]

Hi Gregory

Great job, good workaround.

Just as an information, JIRA will be able to move only between internal directories. So, to move LDAP users to another directory, it must be a DELEGATED type (internal with LDAP authentication).

Cheers,

Paulo Renato