Fixing Encryption Key Issues After epiCentre 2.3 Upgrade

Applies to: epiCentre version upgrade from 2.2.6 to 2.3
Issue: After the upgrade, an "Encryption key is not correct" message appears during the Database Configuration Wizard.

Problem Summary

This issue occurs when the database upgrade from epiCentre 2.2.6 to 2.3 fails partially. As a result, some required database schema changes are not applied correctly. This leads to a mismatch in the encryption key handling and causes the application to show an error.

Solution Steps

Step 1: Prepare the Environment

Connect to the database using SQL Server Management Studio (SSMS).
Take a full backup of the database before making any changes.

Step 2: Run SQL Recovery Queries

Execute the following SQL statements in SSMS.

Some queries may fail if they were already applied — this is expected behaviour due to the partial upgrade.

-- Rename columns
EXEC sp_rename 'tbl_da_ep_User.Username', 'EncryptedUsername', 'COLUMN';
GO
EXEC sp_rename 'tbl_da_ep_User.Email', 'EncryptedEmail', 'COLUMN';
GO

-- Add missing columns
ALTER TABLE [tbl_da_ep_User] ADD PasswordLastChangedDate datetime NULL;
GO
ALTER TABLE [tbl_da_ep_User] ADD Flag_ResetPass bit NULL;
GO

-- Alter renamed column type
ALTER TABLE [tbl_da_ep_User] ALTER COLUMN EncryptedUsername nvarchar(255) NOT NULL;

-- Update version and code descriptions
UPDATE [tbl_ep_Settings] SET [SettingValue] = '2.3' WHERE [SettingName] = 'database_version';
GO
UPDATE [tbl_cs_CodeSetValue] SET [Description] = 'Procedural intervention - Non-implant (Therapeutic intent)' WHERE [codesetID]=68 AND [CodeSetValueID]=874;
GO
UPDATE [tbl_cs_CodeSetValue] SET [Description] = 'Procedural intervention - Cancer block (Therapeutic intent)' WHERE [codesetID]=68 AND [CodeSetValueID]=875;
GO
UPDATE [tbl_cs_CodeSetValue] SET [Description] = 'Procedural intervention - Other (Diagnostic intent)' WHERE [codesetID]=68 AND [CodeSetValueID]=1317;
GO
UPDATE [tbl_cs_CodeSetValue] SET [Description] = 'Often present (pain free periods last less than 6 hours)' WHERE [codesetID]=213 AND [CodeSetValueID]=1396;
GO
UPDATE [tbl_cs_CodeSetValue] SET [Description] = 'Often present (pain free periods last less than 6 hours)' WHERE [codesetID]=200 AND [CodeSetValueID]=1172;
GO
UPDATE [tbl_cs_CodeSetValue] SET [Description] = 'Often present (pain free periods last less than 6 hours)' WHERE [codesetID]=214 AND [CodeSetValueID]=1401;
GO
UPDATE [tbl_ss_lookup_SurveyCategory] SET [ShortDescription] ='Post-Episode' WHERE [SurveyCategoryID] =5;
GO

Step 3: Final Step – Manual User Table Encryption

After the queries above are executed:

UOW EPPOC IT support will encrypt the sensitive user data (such as usernames and emails).
The encrypted values will be securely shared with your IT contact.
Your team will then manually insert the encrypted data into the appropriate fields in the tbl_da_ep_User table.

Notes

This fix is required only if the upgrade partially failed.
From version 2.3 onward, the generic eppoc user is no longer allowed for login or configuration purposes.
If there are no administrator accounts configured in the system other than the eppoc user, a manual role update must be performed in the database to assign Administrator rights to a valid user account.