ADFS database [dbo].[IdentityServerNotificationCleanup]

  • noobdba

    SSC Enthusiast

    Points: 183

    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.

  • Erland Sommarskog

    SSC-Insane

    Points: 23881

    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]

  • noobdba

    SSC Enthusiast

    Points: 183

    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.

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23881

    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]

  • noobdba

    SSC Enthusiast

    Points: 183

    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.

  • Erland Sommarskog

    SSC-Insane

    Points: 23881

    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]

  • noobdba

    SSC Enthusiast

    Points: 183

    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]

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23881

    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]

  • noobdba

    SSC Enthusiast

    Points: 183

    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.

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23881

    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]

  • noobdba

    SSC Enthusiast

    Points: 183

    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!

  • sabiralvi

    Newbie

    Points: 1

    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.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply