How to update User Directory information through Database when Jira UI is inaccessible

Summary

This KB article describes the way to use the database method to update user directory connection settings when there is no option to access GUI.

Whenever an external user directory (like LDAP, Crowd, etc.) is created in Jira, the settings are saved directly into Jira database and not on the file system. For any specific reason, if the UI is not accessible for the admins, then they can still rely on the following steps to make changes to directory connection settings directly in the database.

➖Note: Please do not use this as your regular method to update directory connections. Direct DB manipulation method would be recommended only in cases where there's no other way to login to the Jira UI in the following possible scenarios:

• All users/admins are part of external directory and there is a hostname change that is blocking all users.

• Username/Password of syncing account changed, User schema setting changes etc.

Environment

Jira 8.x and 9.x versions.

Cause

When an external user directory (like LDAP, Crowd, etc.) is created in Jira, the connection details are saved directly into Jira database table namely 'cwd_directory_attribute' and not on the file system. Hence for any specific reason, if GUI is not accessible for the admins, then they can still rely on database hack to make required changes to connection settings to bring the system up and running. The situations may include but not limited to:

• All users/admins are part of external directory and there is a hostname/domain changes made on the directory side and now Jira is not able to resolve domain name and hence blocking users from logging in.

• Username/Password of syncing account changed, User schema setting changes etc.

• The admin users who are part of Internal Directory have forgotten the username/password and logging in the instance via recovery_admin is not an option.

Solution

• When you configure a User Directory (like LDAP, Crowd, etc.) in Jira, the settings are saved directly into the Jira database table namely 'cwd_directory_attribute'. These settings include details like the connection URL, username/password, user schema settings and all options that are available in GUI when creating User Directories.

• Validate the available user directories configured in the system and get the details of the user directory you want to modify by running below query: In our use case, it's 'LDAP Server' with ID '10000'.

  1 SELECT * FROM cwd_directory;
  

• Update the directory attribute value you prefer to modify by using the right 'UPDATE' query matching your database variant. In the screenshot below we see only 2 columns of the 'cwd_directory_attribute' table. Another column namely 'attribute_value' holds the values for all the configuration records for user directories, it's not shown for confidential reasons. However, it does exist and is the one being updated here.

• The queries shared in this article are validated for Postgres Database. Using below 'UPDATE' query, we are updating the 'ldap.url' attribute for the specific directory mentioned above.

  1 2 3 UPDATE cwd_directory_attribute SET attribute_value = '<URL to connect to your LDAP server>' WHERE attribute_name = 'ldap.url' AND directory_id = <directory_id>; --> Standard Query Format. UPDATE cwd_directory_attribute SET attribute_value = 'https://abc.atlas.net' WHERE attribute_name = 'ldap.url' AND directory_id = 10000; --> Query used as part of local testing.
  

We hope this article helps all those Jira Admins who are willing to update the user directory configurations using database methods. However, we prefer to avoid database manipulation and use GUI methods wherever possible and this approach should only be used as last resort when GUI option is not at all available.