DB_CHAINING lost on on planned Availability Group failover for SQL Server 2019 cumulative update

Question

I'm looking for ideas on how to determine a plausible root cause for DB_CHAINNG problems encountered when we recently performed a planned AG failover.

We're using SQL Server 2019 Standard Edition with "Basic" AGs (meaning that every database is in it's own AG). In "Basic" AGs the Secondary Replicas are not readable, only one Primary and one Secondary is possible per-AG, and the commit-mode must be synchronous. Due to licencing (and cross-database queries), all AGs always have their Primary Replicas on the same Instance at any one time.

The Windows cluster is two Windows Server 2019 nodes, with a File Share Witness on a third server.

The purpose of our planned failover was to apply SQL Server 2019 CU10 (from CU8) to both servers, this went without issue. Patch SRV2, failover to SRV2, patch SRV1.

The planned failover of all AGs was fine, no problems at all.

However, the IIS application immediately started failing with permissions problems due to DB_CHAINING being lost. Altering the databases to enable DB_CHAINING addressed the issues. Patching the second server proceeded without incident.

'cross db ownership chaining' is (and has always been) disabled at the server-level on both servers. This is as per the MS recommendation:- https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/cross-db-ownership-chaining-server-configuration-option?view=sql-server-ver15

I'm aware that restore or attach can affect this property, please see this article and the comments:- https://thomaslarock.com/2009/08/reattach-will-reset-database-chaining, and that DB_CHAINING can only be changed on the Primary Replica.

The configuration of the servers, databases and AGs, etc. predates my time. I know DB_CHAINING can compromise security, it wasn't my choice, it's an old application.

To pre-empt some potential suggestions, this was not an issue with Login passwords, SIDs, Server Role membership, database Users, Roles or permissions (anything User/Role/permission related is stored in the databases and would not have been affected by an AG failover). This was not an orphaned database User issue.

This was not a problem with client connections to a 'stale' Listener address/IP or similar. Both servers are on the same subnet and all clients reconnected via their Listener network names without any issues. The problem was when the clients successfully connected, they could not see tables, or execute procedures, etc.

As soon as DB_CHAINING was enabled, everything worked as expected - no other changes were needed.

I've confirmed on a test environment that failing over an AG has no effect on DB_CHAINING, it neither enables it nor disables it, on either Replica. This implies that the option was not enabled before I failed-over.

I would normally accept this and ensure that checking, and if necessary, enabling this option is added to the failover plan for the future, however about six weeks ago, we experienced an unplanned automatic failover caused by a switch reboot at the hosting company. On this occasion, all AGs failed-over and the application continued to function without any issues or intervention.

I've reviewed the ERRORLOG files and I have a complete history of when DB_CHAINING was enabled on which databases since the two servers were installed, I can see that no-one re-enabled DB_CHAINING after the unplanned failover. Furthermore, no-one has ever disabled DB_CHAINING on any database on either server.

Has anyone experienced a SQL Server update removing DB_CHAINING from User databases? I've reviewed the patching (setup) logs and can find no mention of anything like this being recorded. The closest I can find is someone reporting chaining issues sometime after applying SQL Server 2016 SP2:- cross database ownership - The server principal is not able to access the database under the current security context. But the answer suggests changing the application behaviour to workaround it, which I'm not able to do.

is_db_chaining_on currently returns 1 for all (necessary) databases on both servers (which is good evidence that patching doesn't alter this option, otherwise databases on the last patched currently passive might report 0).

I cannot explain how an unplanned failover from SRV2 to SRV1 was OK, but a planned failback six-weeks later to SRV2 from SRV1 had DB_CHAINING issues (when there is no evidence in the logs that anyone/anything disabled the option). It seems like either the update silently removed the property or the property was not set correctly on SRV2 before the planned failover - in which case how was the application functioning (for months) before the unplanned failover six-weeks ago?

Any ideas where I can look, or what I can test for, to identify a plausible root cause would be appreciated.

EDIT: thanks for your response AMTwo, but no, there have been no backup/restore or detach/attach events. The databases were created from native backups on both SRV1 and SRV2 in early Dec-2020, since then, there have been no restores of the any databases in question. I'm the only DBA and there's no-one else who could/would have done this, msdb.dbo.restore_history is empty. We also have Redgate SQL Monitor coverage over this pair of servers which would have generated a lot of alerts if anything like a restore was attempted which broke the AG replication.