Unable to Log In to Confluence after an XML restore

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

After restoring an XML backup from another instance users are unable to log in to Confluence.

Cause

The cwd_application and cwd_app_dir_mapping tables are empty (i.e., they contain no data).

Resolution

  • Shutdown Confluence
  • Backup your database

Run the following SQL commands, one by one:

INSERT INTO cwd_application (id, application_name, lower_application_name, created_date, updated_date, active, description, application_type, credential ) 
VALUES ('327681','crowd-embedded','crowd-embedded','2012-8-14 12:00:43','2015-1-14 10:57:35', 'T', 'NULL', 'GENERIC_APPLICATION', 'X');
INSERT INTO cwd_app_dir_mapping (id, application_id, directory_id, allow_all, list_index) 
VALUES ('393217','327681','360449','T','0');

(info) If the query above results in a foreign key constraint error returned for cwd_directory, then run the following two queries to create an internal directory before proceeding to the query that inserts an admin user:

  • Directory entries
INSERT INTO cwd_directory(id,directory_name,lower_directory_name,created_date,updated_date,active,description,impl_class,lower_impl_class,directory_type)
        VALUES ('360449','Confluence Internal Directory','confluence internal directory','2020-02-14 13:14:58.331','2020-02-14 13:14:58.331','T','Confluence default internal directory','com.atlassian.crowd.directory.InternalDirectory','com.atlassian.crowd.directory.internaldirectory','INTERNAL');

INSERT INTO cwd_directory_attribute (directory_id, attribute_value, attribute_name) VALUES ('360449','atlassian-security','user_encryption_method');

Consider changing the date input format as it depends on the DB configuration. For example: instead of "2020-02-14 13:14:58.331" you can use "to_date('14.02.20','DD.MM.RR')"


  • User entries
INSERT INTO cwd_user(id, user_name, lower_user_name, active, created_date, updated_date, first_name, lower_first_name, last_name, lower_last_name, display_name, lower_display_name, email_address, lower_email_address, directory_id, credential) 
VALUES (1212121, 'admin', 'admin', 'T', '2009-11-26 17:42:08', '2009-11-26 17:42:08', 'A. D.', 'a. d.', 'Ministrator', 'ministrator', 'A. D. Ministrator', 'a. d. ministrator', 'admin@example.com', 'admin@example.com', (select id from cwd_directory where directory_name='Confluence Internal Directory'), 'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==');
 
INSERT INTO user_mapping 
VALUES ('2c9681954172cf560000000000000001', 'admin', 'admin');

(warning) If you did have to add these 2 as a result of encountering a FK constraint error, go back and try to run the cwd_app_dir_mapping INSERT statement again before proceeding. 


INSERT INTO cwd_group(id, group_name, lower_group_name, active, local, created_date, updated_date, description, group_type, directory_id)
VALUES ( '888888','confluence-administrators','confluence-administrators','T','F','2011-03-21 12:20:29','2011-03-21 12:20:29',NULL,'GROUP',(select id from cwd_directory where directory_name='Confluence Internal Directory'));

INSERT INTO cwd_group(id, group_name, lower_group_name, active, local, created_date, updated_date, description, group_type, directory_id)
VALUES ( '999999','confluence-users','confluence-users','T','F','2011-03-21 12:20:29','2011-03-21 12:20:29',NULL,'GROUP',(select id from cwd_directory where directory_name='Confluence Internal Directory'));
INSERT INTO cwd_membership (id, parent_id, child_user_id) 
VALUES (888888, (select id from cwd_group where group_name='confluence-users' and directory_id=(select id from cwd_directory where directory_name='Confluence Internal Directory')), 1212121);

INSERT INTO cwd_membership (id, parent_id, child_user_id) 
VALUES (999999, (select id from cwd_group where group_name='confluence-administrators' and directory_id=(select id from cwd_directory where directory_name='Confluence Internal Directory')), 1212121);
UPDATE cwd_user 
SET credential =
'x61Ey612Kl2gpFL56FT9weDnpSo4AV8j8+qx2AuTHdRyY036xxzTTrw10Wq3+4qQyB+XURPWx1ONxp3Y3pB37A==' 
WHERE id=1212121;


After running the above query, restart Confluence and try to log in with the following credentials:

  • Username: admin

  • Password: admin


Last modified on Mar 31, 2023

Was this helpful?

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