April 26, 2019 at 6:33 pm
There are a couple of adfs servers (pri and sec) with backend adfs database. This was installed by an ex-employee and that user is the database owner (account does not exist in AD anymore). However, the ADFS service runs on a service account and that service account also owns the schema for IdentityServerPolicy in database and is a user within the database. Please NOTE that ADFS service has been functional and is not down.
BUT,
On a reboot of the SQL server we started seeing the following in the sql logs. It wasn't happening before and started after a reboot of sql. And this is logged every five seconds flooding the current log file.
The activated proc '[dbo].[IdentityServerNotificationCleanup]' running on queue 'AdfsConfiguration.dbo.IdentityServerNotificationsQueue' output the following: 'Could not obtain information about Windows NT group/user 'mydomain\ex-employee', error code 0x534.'
Within the configuration database I see dbo.IdentityServerNotificationsQueue under service broker -> Queues.
Any insight on how to rectify this? Thanks much.
April 26, 2019 at 6:46 pm
In SSMS, find the database in Object Explorer. Expand the Service Broker node. There select Queues. Find IdentityServerNotificationCleanup. Right-click and select Script as ALTER to New Query window. Change the user to something useful. Exactly what depends on what the activation procedure does. That is, you will need to figure out what permissions are needed. The best is probably to create a user WITHOUT LOGIN and grant it the permissions needed and use that user.
I should that I have no idea what ADFS is. But I know Service Broker.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 29, 2019 at 4:53 pm
Thank you very much for the response and that too detailed.
I right clicked the dbo.IdentityServerNotificationsQueue -> Script Queue as -> Alter to -> New Query Editor Window
Then noticed the "execute as owner" in the query.
ALTER QUEUE [dbo].[IdentityServerNotificationsQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[IdentityServerNotificationCleanup] , MAX_QUEUE_READERS = 1 , EXECUTE AS OWNER ), POISON_MESSAGE_HANDLING (STATUS = ON)
What do you think would be the best approach to change the execute as owner? There is another service account associated with the database and is active directory based "domain\account". How should I go about using that account to execute this job? That account has "db_genevaservice" role membership and owns the "IdentityServerPolicy" schema.
April 29, 2019 at 6:37 pm
Hm, if the queue has EXECUTE AS OWNER, I think you should not change the queue activation, but the owner of the queue should be changed. Which could be the database owner. If you run
SELECT user_name(principal_id), * FROM sys.service_queues WHERE name = 'IdentityServerNotificationCleanup'
What does the first column say? What does the principal_id column say? If we have NULL, it is the database owner that should be changed:
ALTER AUTHORIZATION ON DATABASE::yourdb TO betterowner
If the query returns a non-NULL value, don't touch anything yet, since there are probably more objects you need to change ownership on.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 29, 2019 at 7:04 pm
Executed
Select USER_NAME(principal_id), * FROM sys.service_queues WHERE name = 'IdentityServerNotificationCleanup'
From the adfsconfiguration database. It returns just the column headers. The version of SQL is 2008 r2.
April 29, 2019 at 7:05 pm
Sorry, that was the name of the procedure. You put the name of the queue there.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 29, 2019 at 7:26 pm
Executed
The first column header is "(No column name)" and is NULL. Some other attributes are
name : IdentityServerNotificationsQueue
principal_id : NULL
activation_procedure : [dbo].[IdentityServerNotificationCleanup]
April 29, 2019 at 7:30 pm
So it seems that the entire database is owned by my-domain\ex-employee. Verify this with sp_helpdb and then change the DB owner.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 29, 2019 at 7:45 pm
sp_helpdb showed the "owner" of the database as "domain\ex-employee".
So, I would
ALTER AUTHORIZATION ON DATABASE::yourdb TO betterowner to rectify. I am thinking the better owner in this case should be the service account that has the access to the database which is NT account. Should that be specified as 'domain\username' as below
ALTER AUTHORIZATION ON DATABASE::yourdb TO 'domain\username'
Also, would changing the owner cause any disruption? I would think not but you obviously are the expert. And thank you very much for the prompt response! Greatly appreciated.
April 29, 2019 at 7:50 pm
Personally, I think a database should be owned by an SQL login that exists solely to own that database, and which has not been granted any permissions at all.
But if you want to use that service account, that is the syntax.
There could be disruption if there are more databases owned by this login and cross-database ownership chain has been enabled. Which I hope is not the case. There are a few more cases where it could be a problem - but since this user is no longer in the AD, I don't think those scenarios would be working at this point anyway.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 29, 2019 at 7:59 pm
There is another database that gets installed during the install of the ADFS. The ex employee is an owner there as well. The account is not present in the domain that the server belongs to. So, I would think that it should be fine.
I will make the changes during a quiet time. Will mark this as answered after that.
Again, thank you!
April 16, 2020 at 7:16 pm
Hi - I'm curious to know what happened at the end, did the issue resolve? I'm having a similar issue, only that the domain account is not of an ex-employee but a domain service account and is active. For some reason these errors have started to fill up in the event viewer.
An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domain\svc_acc', error code 0x5.
August 4, 2020 at 2:41 pm
Sorry sabiralvi. Didn't see you message until now.
Changing the user to a different domain based account helped with the error.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply