OTGL is facing an issue where the role IDs from the HQ database are not migrated correctly to the Branch database.

When a site is configured for replication and is upgraded from version 10 to 12 and above, the roles from the back-office are migrated to web tables with unique codes. Upgrading individual sites means a unique ID will be generated for each role code in the individual databases.

For example, when the Administrator role is migrated it will have a Role ID of A001 in Database 1 and A002 in database 2. 

Refer scenario below:

1. A new user was created in the HQ Database as “LinkFiji1” and assigned the “administrator” role
2. The user “LinkFiji1” was replicated successfully to the branches, however, the roles assigned to the user are not replicated

Now when this happens:

1. A user created in the HQ database and roles assigned would give a foreign key error in the Branch Database
2. Although the user will be replicated, the assigned roles will not be replicated due to conflict in the role id.

Need an SCR for branch servers that need to be executed to realign the branch role id with that of HQ for the migrated data.

Environment Setup on QA8:

1. Login Information:

1. HQ URL: http://10.0.0.18/LinkWEB/default.aspx
2. HQ Login: username: admin, password: admin
3. Site URL: http://10.0.0.18/LinkWEB-OTGLBranch/default.aspx
4. Site Login: username: admin, password: admin

2. User Created on HQ database as “Code: Linkfiji, Name: Link Business Solutions “
3. Assigned the user “Administrator” Role
4. Notice at Branch after enabling SQL Agent the user would be migrated but not the role

Environment Details

1. OS version: Win10
2. Application version: 13.20
3. Setup on:
   1. Server: LinkQA4
   2. Database: DEV-LinkSOFT-REPL-HQ, DEV-LinkSOFT-REPL-BA, DEV-LinkSOFT-REPL-BB
   3. LinkSOFT URL: HTTP://LinkQA4/DEV-LinkSOFT-REPL-HQ, HTTP://LinkQA4/DEV-LinkSOFT-REPL-BA, HTTP://LinkQA4/DEV-LinkSOFT-REPL-BB
4. Login Details: Standard username and password for user "admin"

Next Step: Vineet to confirm setup details and re-allocate the case to Sanjay

Bula Sanjay

Setup is the same as the case header. As discussed in December, the issue is not with current roles created but rather the ones migrated from version 10 to 12 during the upgrade. Please review the setup. The license for these setups has expired in December 2021. Please do renew this while reviewing.

Regards
Vineet Ram
222 0085

Hi Vineet, I have had a look at this issue in more detail and the statement that the ID's are upgraded incorrectly is incorrect.

The issue here is that there is a mismatch in ID's between the branch and server databases. This can be caused in several ways. When upgrading, the system does not reassign primary keys as database constraints will prevent this even if we tried.

Please follow documentation closely when upgrading, and make sure clients do not go live when issues like this arise.

To resolve this issue, you have two options:

1. Delete the user tables from the branch database and use "compare data" to bring them back in from HQ
2. Create new roles in HQ, and assign the users the new roles in the branch once data from HQ has been replicated. 

The second option is probably the least intrusive and is the easiest solution. Instructions on how to create roles and role menu access is in our documentation.

The first option is a consulting exercise and will require some downtime as we are dealing with user accounts and roles. The development team can script this but will need the following:

1. A static copy of the HQ DB
2. Confirmation that HQ User accounts are correct and that branch data needs to be REPLACED

In saying that, please make sure you test your upgrades and resolve these issues before taking the client to live.

Let me know which path you choose and assign the case back to me for Closure or next steps.

Important note: - If you choose to remove user accounts and use compare data, remember to exclude the user you are logged in with as "Compare Data" required user access to run

Also, if you use Compare Data, the User Tables are in the Table Name: Master, Compare Group Name: Configuration

Hi Sanjay

This is not an issue with userid. It's issues with roleid. When OTG was upgraded from version 8 to version 10, we had executed this procedure on all branch sites "LT_REPL_UTILITY_AlignBranchUserAccountWithHeadOffice". However the same was not functional in v12 hence not executed in any of the sites. 

Do you think this could be the issue?

Let me know and we will review and execute this at non-business hours after testing on UAT

Regards
Vineet Ram
222 0085

Hi Vineet, the utility "effectively" does what is stated in comment 3, which is resync user data. Role assignments are part of user data.

The utility is available in all versions, however, you need to have access via linked servers for this utility to work.

You MUST test this utility before applying it to production. Use QA8 if you like as it has sufficient space.

Can you please attend to this case? If the issue is either resolved or no longer requires attention, please assign the case to me for closure.

if you have any concerns and wish to pursue this case further, please respond within 3 days. Thank you.

Bula Sanjay

Thanks for the clarification. We had applied this on 3 sites and left for a week to review if any errors come up. No issues were reported thus it has been executed on all sites.

The issue has been resolved therefore closing this case

Regards
Vineet Ram
222 0085