Summary of the Login Issue
After building a contained availability group in SQL Server, a customer was experiencing intermittent issues connecting to their primary database. Our investigation revealed that the SQL Authentication login being used had been created both within the context of the contained AG as well as directly on the primary node but had different SIDs in each location.
Context
For those unfamiliar with contained availability groups, they are an enhanced type of Always On Availability Group, introduced in SQL Server 2022, that includes some instance-level objects as part of the availability group itself, which allows the AG to function as a more complete, portable unit, removing the need to synchronize logins and jobs across replicas to maintain functionality if failover to a secondary node occurs.
The Problem
Managing the objects within the contained AG requires being connected to the listener name, and changes made outside of the contained AG, even if you are connected to the instance acting as the primary node, are not necessarily reflected within the AG itself. In this case, the customer was unfamiliar with the difference between these contexts, and had created logins both in the context of the contained AG and outside of it, resulting in inconsistent behavior for users depending on what name was being used in their connection string.
The Investigation
When the issue was reported to us, because a specific user was experiencing the issue, we quickly zeroed in on that user’s login, and by querying their database user in the sys.database_principals table both in the context of the contained AG and outside of it, we identified the mismatch in SIDs and were able to piece together what had been done. We determined that when the user connected using the listener name, they were successful, but when they connected using the instance name of the primary node, they were denied.
The Fix: Synchronizing the SIDs
While the user should have been using the listener name for all connections, in order to prevent continued failures, we use the Copy-DbaLogin command from dbatools, with the “-Force” parameter, to synchronize the user’s login using the AG listener name as the source and the instance name of the primary node as the destination. This aligned the user’s SIDs in both locations and they have been connecting successfully since.
The Lesson
Complexity for complexity’s sake is not the answer. We ultimately moved this customer away from the contained AG to a more standard configuration, and it is a much better solution for their use-case. The feature itself is not the enemy, but the extra complexity in their case was too much to manage, and ultimately didn’t benefit them.