Unable to Log In to Confluence after an XML restore
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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');
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');
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:
adminPassword:
admin