Documentation for Confluence 5.4.
Documentation for Confluence OnDemand and earlier versions of Confluence is available too.

Skip to end of metadata
Go to start of metadata

Use this document if you are unable to log in to Confluence as administrator. The most common reason for using these instructions is if you have lost the administration password for your Confluence site.

Before you Start

Please note the following 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.
New user management in Confluence 3.5 and later
  • Confluence now uses the CWD_USER table in the database to store and refer to its users.
  • During an upgrade from Confluence 3.4.9 or earlier, the upgrade process copied the users from the OS_USER table (for upgrades from versions older than 2.7) or the USERS table (for versions 2.7 to 3.4) into the CWD_USER table.
  • The new user management framework also introduced user directories. Making modifications to users in the database will only fully work for users in Confluence's Internal Directory. The instructions below include extra steps for instances in which the user management has been delegated to external sources (via LDAP, Crowd or JIRA).

Please refer to the older documentation if you are still using OSUser or AtlassianUser.

Using Crowd for SSO
  • If Confluence is configured for SSO through Crowd, you will only be able to authenticate as users from the Crowd server.
  • This document covers how to recover administration rights from the local 'Confluence Internal Directory' only. However, you will not be able to authenticate as a local Confluence administrator while Crowd SSO is enabled. Please refer to Integrating Crowd with Atlassian Confluence for details on how to configure or disable Crowd SSO.

Step 0. Get access to the database

If you are using the embedded HSQL database, you can find the files containing your database in <confluence-home-directory>/database. When you shut down Confluence, the SQL will be written to a '.script' or '.log' file in that directory to which you can append the SQL described below.

If you are using a proper 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.

 

Icon

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:

  1. Add a new admin user by running:

  2. Add new groups by running:

  3. Add group memberships into cwd_membership:

(warning) If using an Oracle database, use sysdate instead of a string for the created_date column.

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 admin

For an External Database

To change the password to admin for a given username:

  1. Shut down Confluence.
  2. Connect to your database.
  3. Run the following SQL:

For the Evaluation Embedded HSQL Database

To change the password to admin for a given username:

  1. Shut down Confluence.
  2. Open <confluence-home>/database/confluencedb.script, or confluencedb.log if the .script file looks empty.
  3. Search for:

  4. Keep searching until you find the appropriate user, then replace their password with the hash value above.
  5. Save the file.
  6. Restart Confluence.

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:

  1. Find the directory names and their order:

  2. Take note of the ID with list_index 0, and the list_index and ID of the Confluence Internal Directory.
  3. Switch the order of the directories:

  4. Check to see if the directory is active (the 'active' column should be set to 'T'):

  5. If necessary, activate the directory:

Step 4. Clean Up

To tidy up:

  1. Start Confluence.
  2. Log in with your modified/created username and use password admin
  3. Change your password. Do not leave your password as admin, or your instance will not be secure.
  4. 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.
  5. If you followed Stage Three, go to Confluence Administration > User Directories and rearrange your directories so they are correctly configured again.

Notes

42 Comments

  1. Anonymous

    Excellent, this article saved my butt!

    - Jon Verville, NASA/GSFC

  2. san

    Hi , i am trying to restore the "admin" user password, it is not working for me.

    I ran this query on my sql server:

    update users set password =
    'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A=='
    where name='admin';

    then i restarted the Tomcat service, but i am not able to login using "admin" user account and pasword i set here.

    any idea ?

    Thanks in advance,

    San

  3. Anonymous

    Same issue as Craig, except in our case we did the Confluence upgrade first!   Now we have JIRA 4.3 running just fine, but no one can access Confluence 3.5 (which uses JIRA user management).   Trying to add an admin account fails because the cwd_group table is empty.   We've successfully upgrade JIRA and Confluence many times.   This 3.5 / 4.3 upgrade is a complete disaster.

    1. You'll need to roll back your upgrade and migrate the JIRA user management manually to Conflueence 3.5 as described here: Upgrade to Confluence 3.5 with JIRA User Management Fails.

      Please raise a support ticket if that doesn't help.

  4. Anonymous

    The 3.4.9 to 3.5 Upgrade is broken!  This is for mysql and postgresql - the cwd_* tables are empty - no AD admins or local admins and this page is useless!

    Real bummer!!  Lucky I'm testing this before doing the production upgrade!!

  5. Anonymous

    well..  really disaster :)  wasted two expensive days on this upgrade - no positive result with confluence - can't login (jira 4.3  is running )

    # select id from cwd_directory where directory_name='Confluence Internal Directory';
    id
    ----
    (0 rows)

    can see only 'JIRA Internal Directory'
    # select id from cwd_directory where directory_name='JIRA Internal Directory';
     id
    ----
      1
    (1 row)

    Guys, how  it is possible at all  to issue such upgrades  like 3.5/ 4.3 or such "help" pages like this one? Was all this stuff ever tested before went to production?

    1. Hallo to both Anonymouses

      I'm really sorry you have encountered such difficulties. I've forwarded your questions to the developer concerned, who will revisit this page and make any necessary updates.

      In the meantime, please would you lodge a request with our support team. They are best suited to give you help with the upgrade process.

      I hope this helps a little.
      Cheers
      Sarah

  6. To the three anonymous commenters above -

    Hi!

    Like Sarah said, I'm very sorry that your upgrades have been problematic. Unfortunately, this page was written with less complicated cases such as yours in mind. If your groups and/or directories are missing, it means there have been issues with your migration that would most likely continue to be problematic even if you could regain admin access by modifying your database. You can take a look at the Troubleshooting Upgrades Knowledge Base article, but your best course of action is to raise a support ticket.

    Thanks,
    Anna

  7. Anonymous

    Hi,

    I understand the instructions but what if the cwd_directory is empty?

    Steven

    1. Steven, if cwd_directory is empty, your upgrade to Confluence 3.5 has failed. This is probably due to a configuration that requires manual migration to 3.5. You should roll back, record the relevant messages in your log file, and follow the instructions at Upgrade Troubleshooting.

      1. Anonymous

        I'd like to know what the difference is between the 3.5.3 upgrade, and the 3.5.2 upgrade that required a patch (see CONF-22356). Because the patched 3.5.2 upgrade didn't wipe out the admin account, when I'm finding the 3.5.3 upgrade did.

        Please correct the SQL statements for creating an Account in the event that an account doesn't exist, because the current statements fail in SQL Server (message reads: 'Subqueries are not allowed in this context. Only scalar expressions are allowed."). It's not the technical writers' fault, but the SQL is not being QA'd (same issue as CONF-22356):

        Add new admin user:

        Add groups:

        Add group memberships

        1. Anonymous

          Sorry, cut out the "CONFLUENCE." in the query to add the groups -- it's my schema name.

        2. As mentioned above, the SQL provided is just an example that works for PostgreSQL and MySQL. You may need to customise it yourself for Oracle, SQL Server or DB2. As you've noted, with different schema names and support for subqueries in updates, sometimes it will vary a bit between different installations as well.

      2. Anonymous

        That's how we are to determine that the upgrade failed?!

        1. That's how we are to determine that the upgrade failed?!

          No, the upgrade will fail with an error message. The absence of certain data in the database is just another symptom of the problem, and the one Steven asked about above.

          Unfortunately, some customers simply restart their system after a failed upgrade and this sometimes leads to a broken but somewhat running system. We've patched the upgrade system to make the messages more meaningful in 3.5.2 and later and to prevent skipping subsequent upgrade tasks when one fails.

  8. Anonymous

    Thanks Matt,

    I got it figured out for my system.

    I was upgrading from 3.3 stanalone  - apparently a no no. For the benetits for anyone having the same issue: Here are my steps:

    1) First upgrade to 3.4.9 by duplicating the confluence dir and database. Some gotchas I found:

         a) make sure you clear up the <confluence-home>/index directory

         b) Since I have duplicated the database. Make sure you make mofiications to the following files:

                - server.xml  :  add new connection string

                 - confluence-init.properties : modify it for the new confluence home dir path

                 - <confluence-home>/data/confluence.cfg.xml : modify the connection string used by hibernate

                 - copy in the old osuser.xml and atlassian-user.xml since you are using delegate jira user management.

    2) Once you have upgraded to 3.4.9 then upgrade to 3.5 using the new database and <confluence-home> that you have

        used for upgrading to 3.4.9.

        Follow the same gotchas steps as for 3.4.9 but do not use the osuer.xml and atlassian-user.xml modified for Jira User Managerment. Instead use the original ones that was modified for that.

        Start up confluence- You should see "upgradeFinished Upgrade completed successfully" in the <confluence-home/logs/atlassian-confluence.log

    3) Now follow steps for restoring Admin password as given above.

    1. This helped me out in upgrading an old Confluence 3.0 instance, so thanks!

      I also discovered i had to change my MySQL table type from MyISAM to InnoDB per this link: Database Errors When Using MySQL and MyISAM Tables

      Hope this helps someone else out there.

  9. Anonymous

    For those that need to add groups in addition to just adding the admin user and mappings:

    Note: Make sure to replace <Internal Directory Id> with the ID you found the step (If No Local Users Exist > step 1)

    Note: You may need to replace sysdate with whatever makes your DB happy if you don't use Oracle.

    1) Create admin user per instructions above

    2) Create confluence-users and confluence-administrators groups:

    insert into cwd_group(id, group_name, lower_group_name, active, local, created_date, updated_date, description, group_type, directory_id) values (1212121, 'confluence-users', 'confluence-users', 'T', 'F', sysdate, sysdate, '', 'GROUP', <Internal Directory Id>)

    insert into cwd_group(id, group_name, lower_group_name, active, local, created_date, updated_date, description, group_type, directory_id) values (1212122, 'confluence-administrators', 'confluence-administrators', 'T', 'F', sysdate, sysdate, '', 'GROUP', <Internal Directory Id>)

    3) Create membership mappings per instructions above

  10. in "where directory_name='Confluence Internal Directory)" there is lack of ' character...

    1. Thanks for spotting that, Piotr! I've corrected it.

      1. so look also at "`" usage in next examples - such naming of columns does not work under postgresql as db backend

        btw. thanks for quick reply.

  11. Anonymous

    so there is no way to go from 3.3 to 3.5 directly?

    1. Yes, you can definitely upgrade from 3.3 to 3.5 directly. Use the Upgrading Confluence instructions. If you're stuck on user management issues, check the knowledge base (the articles labeled conf35 are a good place to start) or open a Support ticket.

  12. Anonymous

    This really worked for me ( 'For an External Database') ! Thanks in Tons.

  13. Anonymous

    We did a server move and Atlassian product upgrade.  Everything seems to be working fine, except for Confluence's Admin page.  I have the SSO setup for all products, and everyone can log into Confluence just fine.  But the admin can't get to the Confluence Admin page.  How does Confluence know which Crowd group should be admin.  Running the above directions, I'm showing that I have no admin users in the Confluence internal directory, but that's because I don't want any in there.  It it pointing to the Crowd "confluence-administrators" group.  Plus the Confluence Internal Directory looks like is disabled in the Directory table.  Any advice?  Thanks!

    1. The group "confluence-administrators" is hard-coded to always have admin permission, so you should be fine if your users are definitely seen to be members of that group by Confluence.

      It could be a variety of problems. It is probably easiest to enable the internal directory, ensure there is an admin user in that directory, and use that account for debugging the problem.

      1. Anonymous

        Thanks! Problem resolved.  So, is there a way to change which group is the Admin group for Confluence?  I'm trying to clean up the crowd groups.  One Admin group for all Atlassian products would be nice.  Is this possible?

        1. You can grant administrative permission to any group via the "Global Permissions" screen in Confluence. If you have a central administration group in Crowd, just grant that "System Administration" permission here.

          Just be sure that the admin user in the internal directory still has admin permissions, so you can use that account if you have problems with your Crowd configuration or server in the future.

  14. Anonymous

    How do you shut down confluence using ssh or another method? I am using Confluence 4.

    Thanks!

  15. Anonymous

    In Confluence 4.0 We added an LDAP directory which, for some reason, wiped out all our administrator rights. SQL srvr back-end

    We can't log in as an admin.

    Followed the instructions for:  If No Local Users Exist

    Was able to add an 'admin' to local

    1. Add new groups by running: {ok}(tick)

       

       

    2. Add group memberships into cwd_membership:  (error)
      Could not do.  Duplicate key msg from SQL

    3. Add group memberships into cwd_membership: (tick)

    We presume that 2 didn't happen because the memberships are already there. 

    But the admin we added via sql can't log in. The message on log-in says the user does not exist. The local confluence directory is order '0' and still can't log in.  I set the LDAP to 'F' - no joy.

     How do we fix that - get an admin authority or  roll  back or take that LDAP added directory out from Confluence from the back-end.
     

    Any ideas?

     
  16. Anonymous

    And we pay for this......

    1. Anonymous, 

      Sorry if we've missed something here. If you need support please contact us through http://support.atlassian.com rather than through comments. If the documentation is unclear please let us know what needs to be clarified so we can fix it up for you.

  17. Anonymous

    Hi,

    I'm using HSQL with 4.2.2 and provided procedure won't work for me. After changing admin password in confluencedb.script still can't login via admin/admin combination. My sql looks as following:

    INSERT INTO CWD_USER VALUES(491521,'admin','admin','T','2012-05-14 13:32:13.651000','2012-05-14 13:32:13.651000','','','admin','admin','admin','admin','admin@','admin@',360449,'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==')

    Did I miss something?

     

  18. Anonymous

    The table 'local' is now called 'is_local"

  19. Anonymous

    child_user_id is now called child_id

     

  20. Anonymous

    If I use hosted Confluence solution, what should I do to restore admins level access? (deleted by mistake)?

    1. If it's Atlassian OnDemand, the best thing to do would be to raise a ticket at https://support.atlassian.com - the Support team are your administrators so they can sort you out (smile)

  21. As with confluence 4.0.3, and having password encryptions beginning with PKCS5S2, I had to use another kind of password encryption to make it work. Found it on Restoring the Stash Administrator's Password

    {PKCS5S2}4PCXluhV1YoY3yGgp77MfHjoFoS7GwNxif4gQLpwIfqLs9n/3seRLlECMu2CWGtm

     

    1. Thank you very much Martin! This saved my day!

  22. Thanks for posting.  This saved me BIG time.  Also your support team did an excellent job assisting.  

  23. To anyone having issues logging in with a local admin account after following this guide:

    I had to set logininfo.CURFAILED to 0 in the database before I could successfully authenticate with the local admin account.

    I assume something in the Captchaish mechanism is broken or buggy.

    I had Jira/Confluence/Fisheye all installed and authenticating to Jira, but that authentication failed after upgrading Jira.  I was able to use the local admin account on Fisheye to restore its authentication to Jira, but sadly there was no way to log in to Confluence locally to restore it's authentication without going through this guide, which didn't help until I reset CURFAILED.