WEM Server Failed to Connect Database after SQL AlwaysOn Failover to Secondary

Description

User has followed Citrix Doc below to setup AlwaysOn for WEM:
https://docs.citrix.com/en-us/workspace-environment-management/current-release/system-requirements.html#sql-server-always-on

However, the WEM server won't be able to connect AlwaysOn database after a failover from primary SQL to secondary.

There are two key errors exhibiting the database connection failure after SQL failover:

1. WEM Administration Console:
Specific Infrastructure Server seems to be offline or have a wrong database configuration. Please check configuration and try again.

2. Citrix WEM Infrastructure Service Debug.log:
Exception (0x80131904): Login failed for user 'vuemUser'.

11:50:11 PM Event -> MainBrokerWinSvc.<StartAdminBroker>b__46_0() : Connection in progress -> Client CRDDC version 2305.1.0.1 with Ip address 10.110.xxx.xx
11:50:11 PM Exception -> AdminBrokerService..ctor() : Failed to create AdminBrokerService instance object. Exception: System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'vuemUser'.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

Environment

Citrix is not responsible for and does not endorse or accept any responsibility for the contents or your use of these third party Web sites. Citrix is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement by Citrix of the linked Web site. It is your responsibility to take precautions to ensure that whatever Web site you use is free of viruses or other harmful items.

Resolution

Admin needs to leverage a series of queries in order to transfer vuemUser login and its password from SQL primary server to SQL secondary server.

Detailed steps:
https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances

Problem Cause

The vuemUser login doesn't get created on the secondary SQL server under Security - Login.
Note: Screenshots are generated from lab, not a real customer's environment.

Meanwhile, vuemUser exists under the corresponding WEM database on the secondary SQL server after the WEM database is added to the AlwaysOn availability group:

Issue/Introduction

The current Citrix Doc of setting up AlwaysOn for WEM doesn't include the necessary step of creating vuemUser login under the secondary SQL server: https://docs.citrix.com/en-us/workspace-environment-management/current-release/system-requirements.html#sql-server-always-on We have provided feedback to the product document team for including the necessary step of creating vuemUser login.

Additional Information

https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances

Welcome to "KB Articles"