I had to do some troubleshooting on our ADFS (Active Directory Federation Service) servers today and I noticed the Event Viewer was completely full of hundreds of these messages every minute:
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. 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.
There was so many of these messages, I couldn’t see anything else and it was taking too long to filter. So I did a little bit of research on this and with my SQL DBA background, I was able to figure it out. My knowledge of ADFS is limited and this fix worked in our environment but every environment is different, so make sure it will work in your environment too before following this!
The issue is the fact that when ADFS tries to connect to the WID (Windows Internal Database) for ADFS, the user is tied to DBO for the DB. But when the DB Owner is no longer valid, you will get the impersonation error and will no longer be able to connect. Most likely (this is just my assumption for my environment) ADFS was setup a long time ago by a different user that no longer exists. Which then invalidated the owner of the DB. All that needs to be done is change the DB ownership – instantly without a reboot or service restart, it resolved the error messages in my environment and had to be done to each ADFS server.
Many other sites says to install the full SSMS, but this can require reboots and no updates pending, etc. That is actually not necessary if you use PowerShell. If you dont already have it installed for one reason or another, install PowerShell SQL commands:
Run PowerShell ISE (PowerShell will work, I just prefer ISE) AS ADMINISTRATOR. Run this command (be sure its cool with your security team you install this or follow any steps below):
Install-Module sqlserver
After installing the SQLserver module, if you want, you can verify if this is the issue by using the below command.
Invoke-SQLcmd -ServerInstance "np:\.\pipe\MICROSOFT##WID\tsql\query" -Query "select name,owner_sid from sys.databases"
After running the command, if SA does own the DBs (meaning this is NOT your issue) you should see all {1, 0, 0, 0…} in the owner_sid rows. If you see something other than {1, 0, 0, 0…} in all columns like the screenshot below, then you COULD be effected by this (its possible the owner is still a valid user)
To change the owner of the DBs, runs these two commands.
Invoke-SQLcmd -ServerInstance "np:\\.\pipe\MICROSOFT##WID\tsql\query" -Query "ALTER AUTHORIZATION ON DATABASE::[AdfsArtifactStore] TO [sa]" Invoke-SQLcmd -ServerInstance "np:\\.\pipe\MICROSOFT##WID\tsql\query" -Query "ALTER AUTHORIZATION ON DATABASE::[AdfsConfiguration] TO [sa]"
After running those commands, you should no longer see any new events in the error log.
I hope this helps you! Please let me know if it does in the comments below!