Migrating from External Directory to Jira Internal User Directory

Still need help?

The Atlassian Community is here for you.

Ask the community

If your Jira instance currently uses LDAP or a Crowd application for user management, you can revert to internal user management as described below. If your Jira instance has only a few users, it is easier to recreate the users and groups in Jira manually. If you have a large number of users and groups, it is more efficient to migrate the relevant users and groups into the Jira Internal directory.

Both options provided below will reset the affected users' passwords. When done, be sure to notify them to use the 'Reset My Password' link on the Jira log in page before they attempt to log in.

On this page:

Option 1 – Manually Recreate Users and Groups in Jira

Use this option if you have only a few users and groups.

  1. Log in to Jira as a Jira system administrator.
  2. Go to the user directories administration screen and move the internal directory to the top of the list of directories, by clicking the arrows in the 'Order' column.
  3. Make sure that you have at least one user from the internal directory in each of the Application Access groups.
    1. You can find out which groups are providing Application Access by browsing to Administration >> Applications >> Application Access
  4. Make sure that you have a username in the internal directory with Jira system administrator permissions (Administration >> System >> Global Permissions). 
    • If you do not have such a user, add a new one now, and log out of Jira.
    • Log back in as the user you just added, and go back to the user directories administration screen.
  5. Disable the 'External Directory' directory.
  6. Manually create the required users and groups in Jira. They will be added to the internal directory, because you have moved it to the top of the list of directories.
    • If you have assigned Jira permissions to a group which exists in your LDAP or in the External Directory, you must create a group in Jira with the same name.
    • If a user who exists in your LDAP has created content or has had permissions assigned to them in Jira, you must also create that user in Jira.
  7. Add the users to the required groups.

Option 2 – Transfer LDAP/Crowd application Users and Groups to the Jira Database

This method is not officially supported. The Atlassian Support team won't be able to assist you with this process.

We strongly recommend trying this in a test environment, and then making a full backup of your database before deciding to deploy the change in your production environment.

User content is associated through the app_user:user_key value, so the user's content should remain associated with them after the changes below. The app_user on its hand is associated back with the cwd_user table through the lower_user_name value.

There are apps on our marketplace that ease the migration of users between directories, without you requiring to perform the complex workaround below. We suggest that you evaluate these apps before proceeding with the option below.

Use this option to migrate External User Directory (LDAP or Crowd applications) users into the Jira database. You need a knowledge of SQL to perform this task.

The SQL commands given below are tailored for MySQL. If you are using a database other than MySQL, you will need to modify the SQL to work in your database.

Step 1. Create Backups

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

  1. From Jira, create a [XML Backup|https://confluence.atlassian.com/jira/backing-up-data-185729581.html] site backup including.
  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 id from cwd_directory where directory_name='Jira Internal Directory';
    
  2. From the User Directories administration page, find the name of the directory who's users/groups you want to move. Run the following command and take note of the ID. It will be referenced throughout the following instructions as <External Directory ID>.

    select id from cwd_directory where directory_name='<External Directory Name>';
    

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=<Jira Internal 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=<Jira Internal ID> where directory_id=<External Application 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=<Jira Internal 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 directory_id = <Jira Internal 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 'T'

        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 = 'T' 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 Application 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. 

Last modified on Feb 2, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.