If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

Question

I am reading mixed messages about server level object syncing between replicas in an Availability Group.

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.

All well and good - keeping these objects in sync is a task to be done. But what about certificate-based Logins? I read these are for internal system use only, so I would expect the DB engine to maintain SIDs across replicas if that's required. I have a case with three nodes; the third being read-only. I see the Primary's SIDs for logins such as ##MS_SQLAuthenticatorCertificate## do not exist on either of the other nodes. However on those other nodes, there exists a login with the same name and a different SID, but that SID is the same between those two nodes, viz:

SQL01 (Primary) SID = ABC
SQL02 (Secondary) SID = DEF
SQL03 (Read-only) SID = DEF

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?

Answer 1

Is this of concern? Will a fail-over work despite these Logins having mismatching SIDs between some replicas?

I don't think this is a concern with fail-over due to following facts:

• The fail-over basically targets user databases based on database mirroring endpoint
• CONNECT permissions on the ENDPOINT required to user defined LOGIN or SERVICE ACCOUNT (not for server principals named like ##%).

As you mentioned, fundamentally, the SID is the correlation between Login and User in a database level. Since the server level metadata sync is out of scope in availability groups, we must maintain Server Level login's SID same in all the replicas (SQL Instances), so that it would match with User SID in secondary copy of the database.

When fail-over happens there is no failure of application connectivity as the login SID = user SID, eventually application gets the same permissions on the database. Otherwise users in database become Orphaned

Following are some points that indicates why we do not have to concern on server principals that are named '##%':

1. These are created during SQL Server installation, we do not have control over these to manage SID
2. These are only internal purpose, meaning only exists in system databases, doesn't have any relation with user database principals (sys.database_principals)
3. There is no issue with user DB connection, since any application usually doesn't have any relation with the principals that are named like '##%', and the principals (##%) not existed in user database.
4. Finally, our aim is to match Server principal's SID = database principal's SID within each replica. For the principals that are named like ##% are by default and always maintains same SID within replica which you can verify with following query:

select name, sid from sys.server_principals where name like '##%'

select name, sid from master.sys.database_principals where name like '##%'
select name, sid from msdb.sys.database_principals where name like '##%'

---

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?).

You're right, it's limitation of availability group which clearly stated here.., in this case contained database feature would be a solution to persists the authentication with database itself.

Not only logins, but also there are server level metadata aspects that need to be manged outside the availability group as required.

Answer 2

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.