This week, I encountered unexpected behavior with Active Directory Federation Services (AD FS) on a Windows Server installation that an organization had recently transitioned to from an AD FS server running a previous version of Windows Server. I’m sharing my experiences, so others may benefit from our troubleshooting and solution.

The situation

Your organization has deployed Active Directory Federation Services (AD FS). The AD FS implementation runs Windows Server 2019 and uses the Windows Internal Database (WID) as the AD FS configuration database.

The AD FS farm was recently transitioned from Windows Server 2012 R2 and the AD FS farm runs the Windows Server 2019 AD FS Farm Behavioral Level (FBL).

The issue

After each reboot to apply Windows Updates, the AD FS server consumes more CPU to the point where it consumes 100 CPU all the time. This becomes clear from your monitoring solution:

When you sign into the AD FS server, and start Task Manager (taskmgr.exe), you notice that the SQL Server Windows NT – 64 Bit service running the Windows Internal Database consumes the lionshare of CPU:

There are no events in the Event Viewer (eventvwr.exe) that point to problems.

In the log location for the Windows Internal Database (C:\Windows\WID\Log), you see several error logs. When you open the log, you read

The activated proc '[dbo].[IdentityServerNotificationCleanup]' running on queue 'AdfsConfiguration.dbo.IdentityServerNotificationsQueue' output the following:  'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

and

Starting up database 'AdfsConfiguration'.

Error: 28005, Severity: 16, State: 2.

An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user    , error code 0x2.

The cause

This error is caused by a lacking owner for the AdfsConfiguration and/or AdfsArtifactStore databases. After you install the latest version of the Microsoft SQL Server Management Studio (MSSMS), you run the sp_helpdb stored procedure:

This misconfiguration does not affect the functionality of AD FS as the AD FS farm runs the Windows Server 2019 AD FS Farm Behavioral Level (FBL). The AD FS farm uses the AdfsConfigurationV4 database.

The solution

To resolve the issue, run the following two queries, targeting the ownerless databases, where you replace the DOMAIN\User part with an actual account in Active Directory:

Note:
The account specified cannot have permissions on the databases themselves, so you can’t use the AD FS service account to this purpose.

ALTER AUTHORIZATION ON DATABASE::AdfsConfiguration TO "DOMAIN\user"

ALTER AUTHORIZATION ON DATABASE::AdfsArtifactStore TO "DOMAIN\user"

These two queries set an owner for the databases. After these changes, restart the AD FS server and uninstall the Microsoft SQL Server Management Studio.

Concluding

Using Windows Internal Database (WID) with Active Directory Federation Services (AD FS) can be messy. While installing AD FS with a full-fledged SQL Server installation requires more work upfront, its investment pays off, when you don’t have to troubleshoot strange issues like the one above.

Further reading

A Real-world tested Approach for Transitioning AD FS Servers
HOWTO: Deploy AD FS with SQL Server to gain Artifact Resolution and Replay Detection
ADFS database [dbo].[IdentityServerNotificationCleanup]
Download SQL Server Management Studio (SSMS)