Tutorial: Migrate Google AD to LDAP using Crowd


Posted by
Anand UNADKAT

January 26, 2016

Many of you may be using Google AD to authenticate your users through Crowd, since Crowd has the connector for Google AD. However if your company is continuing to grow, you might want to move to your LDAP while keeping user history intact.

This scenario is not an easy one to achieve, and after researching and implementing it with a client, I thought I’d share the steps that are likely to be required in order to migrate Google AD to LDAP using Crowd.

The scenario that I am going to cover is as follows:

  • There are two active directories: Google AD and LDAP
  • LDAP is connected to all the other applications and therefore we want Atlassian applications to be connected to LDAP too
  • Both ADs store their usernames in different ways
  • Google AD follows firsname.lastname (i.e. joe.bloggs)
  • LDAP follows lastnamefirstinitial (i.e. bloggsj)
  • The idea is to move away from Google’s firstname.lastname to LDAP’s lastnamefirstinitial

The first challenge was to change the usernames in the Google AD so that they matched LDAP naming conventions, while keeping the history of each user untouched. We achieved this as follows.

Is it important that you upgrade your Crowd to version 2.8, as you’re likely to experience a few bugs in version 2.7. All the most recent Atlassian versions were used for this project.

Step 1

Extract the users and group membership from Google AD using Crowd database, since Google AD is connected to Crowd. This was done by using Atlassian’s knowledge base, which can be found here.

Step 2

Create a temporary internal directory in Crowd called “Temp”. Instructions can be found here.

Step 3

Import the extracted CSV to “Temp”. You may have to do this twice if in the first attempt the group memberships aren’t imported.

Step 4

From here things become slightly interesting – add this “Temp” directory to all the Atlassian applications in Crowd and change the order of the directories in the applications. So “Temp” should be at the top, followed by Google AD.

Step 5

Log in to each application as an internal administrator and do a full synchronisation again. This is done by editing the Crowd directory configurations, clicking Save and Test and then clicking Synchronise.

After this stage, users will not be able to log in as applications are connected to the “Temp” directory in Crowd, which doesn’t have any password stored.

Once the sync is complete, check that all the users remain in the applications and that their history is intact.

Step 6

This step will test your Excel skills. In step 1, we extracted all the users and groups. Now we are going to use that file to change the usernames in Excel. This is because we will be making a database change, and the script that will do this will be created using Excel.

There are many ways of doing this, and it is entirely up to you how you perform Excel formulas to change the usernames from firstname.lastname to lastnamefirstinitial.

The way I performed it was to extract users from LDAP exactly the same way as step 1, by connecting LDAP Crowd. After this I used two Excel sheets from which I ran a VLOOKUP to match names and emails and add the username to initial sheet. After you have both usernames in one sheet, you add the following script to each cell and drag it all the way to the last cell.

update cwd_user, cwd_membership set cwd_user.user_name =’bloggsj’, cwd_user.lower_user_name = ‘bloggsj’, cwd_membership.child_name = ‘bloggsj’, cwd_membership.lower_child_name = ‘bloggsj’ WHERE cwd_user.user_name = ‘joe.bloggs’ AND cwd_membership.child_name = ‘joe.bloggs’ AND cwd_user.directory_id = ‘<tem internal directory id in crowd>’;

Step 7

Run this query in Crowd DB by copying all the scripts in Excel sheet and pasting them in the command line (i.e. all the scripts from the above steps).

Before you run this query, make sure that all the applications in Crowd are no longer connected to Google AD.

At this stage you will have one or two users that have thrown an error, but this is common. No pain no gain! Simply run a manual script to make sure their usernames have incorporated the changes.

Step 8

Do another manual synchronisation with all the applications as explained in Step 5 to make sure the usernames have changed and their history remains intact.

Step 9

If successful, add your LDAP to each application in Crowd. If you haven’t added LDAP to Crowd already, then you will have to add it using following steps here, before adding to each Atlassian application.

After you have added to each Atlassian application in Crowd, remove the temporary Internal Directory and perform Step 5 again to see if the usernames and history remains.

It is important that all the groups are added in the LDAP as well otherwise you will end up having permission issues i.e. user not able to login to applications.

Remember, you always have Valiantys on the other side, so if you don’t feel comfortable performing these steps, you can always contact us.

  • Anand Unadkat

    Hi Matt,
    Thanks for your comment. When i did the sync, Crowd took care of changing everything in JIRA :) I am not great with Python so I went with Excel route!

  • Useful article, thank you. I did not know that if the user_name was changed in the Crowd DB then JIRA would change it in all the necessary places. Did you later find anywhere that JIRA had embedded the old name and it didn’t get changed?

    I have done a few bulk name changes for customers and generated the SQL script with Python. But Excel works too.