Contained Availability Group and login failed

A contained availability group is a special availability group which manages its owns logins, users, permissions, jobs… with a dedicated master and msdb system databases, more information here.
At a customer place we found some unexpected “login failed” for the group managed service account in the SQL Server logs.

The source of the issue is coming from a PowerShell script executing some dbatools cmdlets. This script is executed inside an agent job so by the SQL Server service account which is sysadmin of all instances.
I will reproduce the issue on my lab environment.

This script sniffs instances for a multiple instance server with the cmdlet Find-DbaInstance, it returns a list of instances:

The instance list received will then be used to gather some information, for example with the cmdlet Get-DbaDatabase:

The login failed is visible here but, of course, when it’s part of a job you don’t have it so obviously but you can see it on the SQL Server logs or in the logs of the job:

The question is more why do we have a failed login for the service account which is sysadmin of all instances.
As we have SQL Server 2019 and SQL Server 2022 with Always Availability Group on this server, we put in place contained availability group for our SQL Server 2022 instances.
A the creation of the contained availability groups we used an active directory service account which was sysadmin of all instances which has been replaced a bit later by a group managed service account. This new gmsa is also sysadmin of all instances.
The problem is coming from the creation of the contained availability group and how this contained AG manages logins, users…
Logins part of the instance are local to the instance and won’t be copied to the system database of the contained AG when you create it. What does it means? It means that if you have created a database with logins and users before the creation of the contained AG, when you move your database in the contained AG, logins and users won’t be part of this contained AG. Users won’t be able to connect to the database until you connect in the context of your contained AG through the listener and recreate those logins.
There is one exception, all logins mapped with the sysadmin server role will be copied automatically to the master system database of a created contained AG.
It was the case of our old service account which has been copied to all our contained AG, but when we changed it to a new group managed service account, this new login, even with sysadmin server role has not been copied automatically to the master database of the contained AG. This conduct to our failed login when the gmsa tries to connect to databases part of a contained AG.

This problem seems to be evident but contained AG has been introduced with SQL Server 2022 while AwaysOn Availability Groups with SQL Server 2012. As DBA, we took some habits with availability groups which cannot be reproduced with contained AG. So take care if you or one of your customer use them to change your way of thinking and remember the contained AG specificities 😉