Before you Start
The following instructions include example SQL that should work on MySQL and PostgreSQL. You may need to customise the queries for other databases or for your installation.
We strongly recommend testing the queries on a test database before modifying your production database.
If you know the admin username with a valid email address, and you have outgoing mail configured, you can reset the password using the forgot password link instead.
We'll send a link to your admin email account to reset your password.
Get access to your database
If you're using the embedded H2 database, you can find the files containing your database in
<confluence-home-directory>/database. See Embedded H2 Database for information on how to connect.
If you are using an external production database, connect to the database with your normal tools. You will need to have permission to run queries and update data in the database.
Step 1. Identify Administrator
To find out which usernames have admin privileges, connect to your database using a database admin tool such as DBVisualiser. Please download a database admin tool now if you do not have one installed already. Then connect to your database and retrieve the list of administrator usernames and IDs with:
If there are multiple results, choose one ID/username combination to use for the following steps.
If there are no results, skip down to If No Local Administrator Exists.
It is important to make sure that the "active" field contains a value of "T". Without this flag trying to authenticate with this user is a non starter.
To set active to true run the following query replacing "<user_name>" with the user name from the previous query
If No Local Administrator Exists
There may be no administrators in your Internal Directory. If this is the case, you need to add one:
Add a new admin user by running:
Add new groups by running:
Add group memberships into cwd_membership:
Note: If using an Oracle database, use sysdate instead of a string for the
Step 2. Replace Administrator Password
Confluence does not store passwords in plain text in the database, but uses hashes computed from the original password. You will need to insert a hash, rather than the plain password, over the existing password in the database. Below is the hash for the password
To change the password to
admin for a given username:
- Shut down Confluence.
- Connect to your database.
Run the following SQL:
Step 3. Put the Internal Directory in First Position
Start Confluence, and try logging in with the username of the user you updated/created and the password 'admin'. If this works, skip to Step 4. Otherwise, your Internal Directory does not have high enough priority.
To put your Internal Directory in first position:
Find the directory names and their order:
- Take note of the ID with list_index 0, and the list_index and ID of the Confluence Internal Directory.
Switch the order of the directories:
Check to see if the directory is active (the 'active' column should be set to 'T'):
If necessary, activate the directory:
Step 4. Clean Up
To tidy up:
- Start Confluence.
- Log in with your modified/created username and use password
- Change your password. Do not leave your password as admin, or your instance will not be secure.
- If you created a new user in Stage 2, create a new admin via the UI and delete the admin you created in Stage 2.
- If you followed Stage Three, go to > General Configuration > User Directories and rearrange your directories so they are correctly configured again.