Step 1. Create Backups

Make sure to backup your database before proceeding with the database modifications:

1. From Jira, create a XML backup site backup.
2. Stop Jira.
3. From your Database administration tool, create a database backup for the Jira database.

Step 2. Replace Jira User Management

Use the SQL below to move groups and users from your External User Directory to Jira by transferring table content. The SQL provided is specific to MySQL and must be modified for other databases.

Find the IDs for your Directories

1. Run the following query and take note of the directory ID's. It will be referenced throughout the following instructions as <Jira Internal DirectoryID>.

   select * from cwd_directory where directory_name = 'Jira Internal Directory' or directory_name = '<LDAP Directory Name>'
   

    Take note of the internal directory ID and the external directory ID you will be migrating from.

Find and remove duplicate users who belong to the same group in multiple directories

To make sure you don't introduce duplicates in the next step, when you move groups to Jira, use the following SQL query to locate any users that belong to a group with the same name in both your external directory and internal directory. 

1. Run the following command to find any users with the same name, that belong to the same group across different directories:

   SELECT count(*), a.user_name, c.group_name from cwd_user a
   join cwd_membership b on b.child_id = a.id
   join cwd_group c on c.id = b.parent_id group by 2,3 having count(*)>1

   Take note of each of the usernames and groups returned. You'll need this in the next step. 

2. In your external directory, remove the users from their respective groups. Their membership will still be retained in the Jira internal directory. 
3. Run the SQL query above again. Once it returns no results, you can move to the next step. 

Move Groups to Jira

1. It is possible that you have several groups in your Internal Directory that have the same name as groups in your External Directory. To find these, run:

   select distinct a.id, a.directory_id, a.group_name, d.directory_name from cwd_group a join cwd_group b on a.group_name=b.group_name join cwd_directory d on d.id=a.directory_id where a.directory_id != b.directory_id;
   

   
   

   1. If you have results from the previous query, for each of the group names that have duplicates, find the id for the group in the Jira Internal Directory (<internal group id>) and the External Directory (<external group id>). Run the following:

      update cwd_group_attributes set group_id=<internal group id>, directory_id=<Internal Directory ID> where group_id=<External Group ID>;
      update cwd_membership set child_id=<Internal Group ID> where child_id=<External Group ID>;
      update cwd_membership set parent_id=<Internal Group ID> where parent_id=<External Group ID>;
      delete from cwd_group where id=<External Group ID>;
      

2. Move all the groups in the External Directory to the Jira Internal Directory.

   update cwd_group set directory_id=<Internal Directory ID> where directory_id=<External Directory ID>;
   update cwd_membership set directory_id=<Internal Directory ID> where directory_id=<External Directory ID>;
   

Move Users to JIRA Internal Directory

1. It is possible that you have several users in your Internal Directory that have the same name as users in your External Application. To find these, run:

   select distinct a.id, a.directory_id, a.user_name, d.directory_name from cwd_user a join cwd_user b on a.user_name=b.user_name join cwd_directory d on d.id=a.directory_id where a.directory_id != b.directory_id;
   

   
   

   1. If you have results from the previous query, for each of the user names that have duplicates, find the id for the user in the Jira Internal Directory (<internal user id>) and the External Application (<external user id>). Run the following:

      update cwd_membership set child_id=<Internal User ID> where child_id=<External User ID>;
      update cwd_user_attributes set user_id=<Internal User ID>, directory_id=<Jira Internal ID> where user_id=<External User ID>;
      delete from cwd_user where id=<External User ID>;
      

2. Move all the users in the External Application to the Jira Internal Directory.

   update cwd_user set directory_id=<Internal Directory ID> where directory_id=<External Directory ID>;
   

Delete the External Application directory

1. You need to change the order of your directories so that the Internal directory is at the top, and active.

   1. If you have only two directories - the Internal and the External directory you are deleting, then do the following:

      update cwd_directory set directory_position = 0 where id = <Internal Directory ID>;
      

   2. If you have more than two directories, you need to rearrange them so the Internal Directory is at the top (directory_position 0) and the External Application directory you are deleting is at the bottom.

      • List the directories and their order using

        select * from cwd_directory order by directory_position;
        

      • Change the list order so that they are in the order you want. Directory order can be rearranged using

        update cwd_directory set directory_position = <position> where directory_id = <directory id>;
        

   3. Check that the internal directory is enabled.

      • List the internal directory. An enabled directory will have its 'active' column set to '1'

        select id, directory_name, active from cwd_directory where id = <Internal Directory ID>;
        

      • If the internal directory is not active, activate it by

        update cwd_directory set active = '1' where id = <Internal Directory ID>;
        

2. When the directories are ordered correctly, delete the External Application directory from the directory order:

   
   

   1. The External Application directory is referenced in several other tables in the database. You need to remove the remaining references to it:

      delete from cwd_directory_attribute where directory_id=<External Directory ID>;
      delete from cwd_directory_operation where directory_id=<External Directory ID>;
      

   2. All references to the External Directory should now have been removed. Delete the directory using:

      delete from cwd_directory where id = <External Directory ID>;
      

Reset passwords

All users who were in the External Directory you deleted, including admins, will be unable to log in. Their passwords need to be reset by choosing the 'Forgot your password?' link on the login page. 
Alternatively, you can run the following database update to reset users password to 'sphere'

update cwd_user set credential='uQieO/1CGMUIXXftw3ynrsaYLShI+GTcPS4LdUGWbIusFvHPfUzD7CZvms6yMMvA8I7FViHVEqr6Mj4pCLKAFQ==' where lower_user_name in ('user1', 'user2', 'user3')