Company: Link Technologies
Case No: L12658
Logged By: Vineet (Link Business Solutions) on 06 Dec 2021 04:38PM
Priority: High
Product: Point of Sale
Group: Support
Time Taken: 17.25 (Weight: 17.25)
Assigned To: Vineet (Link Business Solutions)
Circulation: Sanjay, Vineet
Resolve By: Friday, 18 March 2022 02:14 PM [844 days since logged date]
Status: Closed
Subject: User Roles Replication Issue for migrated data
Summary:    

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
  1. User Created on HQ database as “Code: Linkfiji, Name: Link Business Solutions “
  2. Assigned the user “Administrator” Role
  3. Notice at Branch after enabling SQL Agent the user would be migrated but not the role
Audit Notes:Edited by sanjay on 18/02/22 10:13. Edited by sanjay on 18/02/22 10:13. Edited by aarti on 07/12/21 10:50. Edited by aarti on 07/12/21 10:49. Edited by sanjay on 07/12/21 09:37. 
16 Feb 202210:49AM Comment 1 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 28-02-2022 03:23 PM Time Taken: 6.00 Notes: ETC extended from: 08/12/2021 to 28/02/2022

Hi Vineet, I couldn't check this on the environment you provided as the environment is not available, however, I have tested this on 13.20 and I am not able to reproduce the issue. See test results below:

Table 1 - Test Results
NoTest CaseExpected ResultPass/FailComments
1Create a new user at HQ named LinkFiji1, Create role REPL-TEST and assign this to user LinkFiji1. Assign some access to role REPL-TEST
The user-created should flow into Branch BA and BB
Pass

2
Role should flow in ranch BA and BB
Pass

3
User role allocation in branch BA and BB should be correct
Pass

4
Role Menu Access should flow in branch BA and BB
Pass

5Change ROLE access in Branch BA
Changes should flow into HQ and Branch BB
Pass

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


17 Feb 202201:46PM Comment 2 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 20-02-2022 07:34 PM Time Taken: 1.00

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


18 Feb 202210:12AM Comment 3 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 18-02-2022 03:20 PM Time Taken: 6.00

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.


18 Feb 202210:20AM Comment 4 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 18-02-2022 04:19 PM Notes: Edited by sanjay on 18/02/22 11:50. 

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


22 Feb 202209:54AM Comment 5 by Vineet (Link Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 22-02-2022 03:46 PM Time Taken: 0.25

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



22 Feb 202202:11PM Comment 6 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 22-02-2022 08:08 PM Time Taken: 3.00

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.


24 Feb 202208:00AM Comment 7 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 24-02-2022 01:58 PM
Hi Vineet, another thing to be cautious about is that user IDs are used in transactions, so it would be wise to do a Compare Data and ensure there are no UserID differences.  If there are no UserID differences, this utility can help fix the issue with RoleID. If there are UserID differences, best to create new users and assign roles.

08 Mar 202208:14AM Comment 8 by Sanjay (Link Technologies) Assigned To: Vineet (Link Business Solutions) Followup Date: 18-03-2022 02:14 PM Time Taken: 1.00 Notes: ETC extended from: 28/02/2022 to 18/03/2022

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.


10 Mar 202210:16AM Comment 9 by Vineet (Link Business Solutions) Assigned To: Vineet (Link Business Solutions) Followup Date: 10-03-2022 04:14 PM

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


10 Mar 202210:39AM Comment 10 by Vineet (Link Business Solutions) Assigned To: Vineet (Link Business Solutions) Followup Date: 10-03-2022 04:38 PM

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


If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12658 in the subject line of all emails regarding this issue.

Document size: 18.2 KB
For call complaints, please contact the Managing Director of the company using this form