There are a couple of concepts to understand here.
Firstly, the difference between a Login and a User in SQL Server. A Login is a server-level principal used to authenticate to the server and provide authorisation for server-level objects. A User is a database-level principal used to provide authorisation for database-level objects. In the special case of a Contained Database User, the User can also be used for Authentication.
The method by which a Login is granted access to a database is by mapping the Login to a database User. this mapping is achieved using the SID. This link provides a good overview of the permissions hierarchy in SQL Server.
Next, we need to understand what those ## logins are. As per this Docs article, Logins with a ## prefix and suffix denote special certificate-based logins for "internal system use only". These logins are used by SQL Server for specific internal functions and as such are isolated in purpose and utility to only the instance they're created on.
Now, as far as Availability Groups go, the replica state for any given AG has no impact on the ability of a particular Login to authenticate to the SQL Server. The Availability Group merely controls which replica is responding to requests for a given database in the group. It controls the data movement between replicas and the routing of read-only requests (if configured).
So, to your points:
As I understand it, server-level objects must be kept in sync (aside:
then why isn't it part of AG by default?). For example, if a Login
(identified by SID) does not exist on a Secondary replica (regardless
of username and password) and the system fails over to that replica,
then the corresponding principal will fail to authenticate.
The Login will still be able to authenticate after failover provided the following is true:
- For a SQL Authentication Login, the username and password match between replicas.
- OR, a Windows Login is used and the Login exists on each replica
- OR, for a Certificate-based Login, the certificate exists in master and the Login exists on each replica
The problem arises when your SID does not match and you want to access the database. After failover, any mismatched SID will result in an orphaned database User. The User still exists, the Login still exists but the mapping is gone, and so the authorisation of the Login to the database is now broken.
If your connection string pointed to the AG listener but specified the master database, then even with mismatched SIDs your login would still work. The problem would appear when you try to change DB context or query the AG-joined database where the mapping has been broken. Typically, connections are made to the specific database required, and so the problem manifests as a login failure, but technically it is a failure to authorise in the database after the login has occurred.
For your ## logins, this doesn't matter as they will never be used for the purposes of accessing databases in an AG. Again, they're only used for internal system processes by SQL Server.
Is this of concern? Will a failover work despite these Logins having
mismatching SIDs between some replicas?
No, it is of no concern. The mismatched SID for the internal system logins will have zero impact on failover. Mismatched SIDs for other Logins may have an impact and that is why correctly synchronising these logins is vital.
For Windows Logins, the SID of the account in AD is used and so will automatically be correct when a Windows Login or Group is added to a replica. For SQL Auth Logins, you need to create the logins on the primary replica, and then extract the SID and password hash for these logins to create them with the matching SID & password on other replicas.
To simplify this management task, you can use tools like dbatools which has PowerShell cmdlets for synchronising logins with SIDs and passwords.