AEDB shifting to different MSSQL server and getting database errors during connection to the new SQL Server

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• Workload Automation Agent

Issue/Introduction

Issue:

We have a requirement for Disaster Recovery Solution. The same AEDB database is replicated from one MSSQL Server to another MSSQL Server. But when we start the CA WAAE Application Server at DR site, after stopping the replication,  we get the below error in the application server log

CAUAJM_E_18600 Database function call <SQLDriverConnect> failed in <connect SQLDriverConnect Failed>

 

Environment: 

CA WAAE 11.3.6 on Windows 2008 with SQL SERVER 2008 R2

Cause:

MS-SQL “autosys” account is present in the second database but its user mapping on AEDB is lost

 

Resolution:

In  SQL Server 2008 / SQL Server 2008 R2, run this command from SQL management studio

         ALTER USER autosys  WITH LOGIN = autosys

This  command re-map the user's Security Identifier (SID) to match the sql server login's SID.

Then check if the “autosys” account is mapped to AEDB and granted the ujoadmin role

 

Additional Information:

When trying to add the user mapping or grant the “ujoadmin” role from SQL management studio, you SQL error message is raised:

 User, group, or role ‘autosys” already exists in the current database. (Microsoft SQL Server, Error: 15023) 

Environment

Release: ATSYHA99000-11.3.6-Workload Automation AE-High Availability Option
Component: