none
ADFS Sync server loaded with Event ID 28005 - MSSQL$MICROSOFT##WID RRS feed

  • Question

  • Hey folks,

    We have a client with an ADFS Sync Server running on Server 2012 R2.  The Application Logs in Event Viewer are full of the Event ID 28005 Errors (every few seconds):

    Log Name:      Application
    Source:        MSSQL$MICROSOFT##WID
    Date:          12/24/2015 9:33:57 AM
    Event ID:      28005
    Task Category: Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      servername.domainname.local
    Description:
    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.
    Event Xml:
    < Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQL$MICROSOFT##WID" />
        <EventID Qualifiers="49152">28005</EventID>
        <Level>2</Level>
        <Task>2</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2015-12-24T16:33:57.000000000Z" />
        <EventRecordID>3957441</EventRecordID>
        <Channel>Application</Channel>
        <Computer>servername.domainname.local</Computer>
        <Security />
      </System>
      <EventData>
        <Data>15517</Data>
        <Data>1</Data>
        <Data>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.</Data>
        <Binary>656D0000100000001D000000540041002D004100440046005300530059004E004300300031005C004D004900430052004F0053004F0046005400230023005700490044000000070000006D00610073007400650072000000</Binary>
      </EventData>
    < /Event>

    Initially the WID service was using the NT Service\MSSQL$MICROSOFT##WID account to logon, so I tried changing it to a domain service account that has the "logon as a service" permission on the local server, but the errors persisted.  I also tried using logon as "Local System", but no change.  This doesn't seem to affect functionality at all.. everything is still working fine as far as we can tell, but the client would like to get rid of these errors.  

    I then came across this article: https://ecmtechnicalexpertise.wordpress.com/2014/04/09/configuring-active-directory-federation-services/, so I changed the WID service logon back to NT Service\MSSQL$MICROSOFT##WID, and then made sure it had the "Logon as a service" permissions.  Restarted the server just to make sure I covered all the bases - but the errors are still there.  

    Have any of you come across this issue before?  I'm still digging.. but stumped at this point.

    Tuesday, January 5, 2016 5:02 PM

Answers

  • I have seen this in the past... I am not sure why it gets in that way though.. I'll do some research.

    To fix it, you can install the SQL Server Management Studio and run the following:

    select s.name
    from sys.schemas s
    where s.principal_id = user_id('contoso\adfs')
    alter authorization on schema::identityServerPolicy to dbo
    use adfsconfiguration exec sp_dropuser 'contoso\adfs'
    use adfsconfiguration exec sp_changedbowner 'contoso\adfs'
    use adfsartifactstore exec sp_dropuser 'contoso\adfs'
    use adfsartifactstore exec sp_changedbowner 'contoso\adfs'
    Of course you replace the name with the actual account. I haven't seen that on 2012 R2 yet though, only on ADFS 2.0. Please do backup your server before (baremetal backup) before touching anything. 


    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.



    Tuesday, September 26, 2017 2:18 AM
    Owner
  • I did not take a chance and run something untested.

    I resolved the issue by fixing the other issues on my server causing Windows Updates to fail, Features to be added / removed, and even the SQL Management Studio install to fail. 

    Now that SQL Management Studio is installed and I can access the WID, I confirmed the owner is set to "UNKNOWN" (using SP_DBHELP).  And setting the owner to a valid value has stopped the events in the event log.  I used SQL commands similar to what you mentioned earlier:

    • use adfsconfiguration exec sp_changedbowner 'contoso\adfs'
    • use adfsartifactstore exec sp_changedbowner 'contoso\adfs'
    Wednesday, November 1, 2017 2:04 PM

All replies

  • I just started having this same issue occur last night. I replaced my SSL cert 2 days earlier and all was golden. Old cert hit expiration late yesterday and then found secondary server not communicating properly with primary and it reverting to old cert (thumbprint shows changed). Restart service and its fine again, and then changes again later. Going back to Premier support to figure this out.

    Brad Boozer Support Technician Mattress Firm

    Monday, January 11, 2016 5:20 PM
  • Did you ever find a solution to this?  I am experiencing the very same issue.
    Friday, September 8, 2017 3:23 PM
  • Hi,

    I am seeing the same error. Has anyone been able to fix this?

    Thank you,

    Monday, September 25, 2017 4:09 PM
  • I have seen this in the past... I am not sure why it gets in that way though.. I'll do some research.

    To fix it, you can install the SQL Server Management Studio and run the following:

    select s.name
    from sys.schemas s
    where s.principal_id = user_id('contoso\adfs')
    alter authorization on schema::identityServerPolicy to dbo
    use adfsconfiguration exec sp_dropuser 'contoso\adfs'
    use adfsconfiguration exec sp_changedbowner 'contoso\adfs'
    use adfsartifactstore exec sp_dropuser 'contoso\adfs'
    use adfsartifactstore exec sp_changedbowner 'contoso\adfs'
    Of course you replace the name with the actual account. I haven't seen that on 2012 R2 yet though, only on ADFS 2.0. Please do backup your server before (baremetal backup) before touching anything. 


    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.



    Tuesday, September 26, 2017 2:18 AM
    Owner
  • I too am experiencing this issue, aside from the SQL Management Studio suggestion (have not verified if that does/doesn't fix it) I do not see a fix for this yet.
    Wednesday, October 25, 2017 4:30 PM
  • If you don't want to install SQL Management Studio, you can try directly in PowerShell:

    #Set the service account name
    $_account = "V\GMSA_2$"
    #Connect to the local WID, this does not require the SQL PowerShell module
    $_db_connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    $_db_connection.ConnectionString = "Server=\\.\pipe\MICROSOFT##WID\tsql\query;Database=AdfsConfiguration;Integrated Security=True;"
    $_db_connection.Open()
    #Create a command and run a T-SQL query
    $_db_command = $_db_connection.CreateCommand()
    $_upade = @"
    select s.name
    from sys.schemas s
    where s.principal_id = user_id('$_account')
    alter authorization on schema::identityServerPolicy to dbo
    use adfsconfiguration exec sp_dropuser '$_account'
    use adfsconfiguration exec sp_changedbowner '$_account'
    use adfsartifactstore exec sp_dropuser '$_account'
    use adfsartifactstore exec sp_changedbowner '$_account'
    "@
    $_db_command.CommandText = $_upade 
    #Load the results in a table
    $_db_results = $_db_command.ExecuteReader()
    $_data_table = New-Object -TypeName System.Data.DataTable
    $_data_table.Load($_db_results)
    $_data_table
    $_data_value = $_data_table.Value
    $_data_table.Dispose()
    $_data_value
    #Close the connection with the local DB
    $_db_connection.Close()

    Before running this:

    • change the service account with yours (mine has a $ at the end because it is a GMSA account, if you are using a regular user as a service account, don't put the trailing $)
    • this is for WID
    • this does not work in ADFS 2016
    • I never tested it, so you're on your own... that's why the next point is critical
    • ensure that you have a backup of the environment (full backup on the primary server) 

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.


    Thursday, October 26, 2017 1:44 PM
    Owner
  • Any chance at this?

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Tuesday, October 31, 2017 11:00 PM
    Owner
  • I did not take a chance and run something untested.

    I resolved the issue by fixing the other issues on my server causing Windows Updates to fail, Features to be added / removed, and even the SQL Management Studio install to fail. 

    Now that SQL Management Studio is installed and I can access the WID, I confirmed the owner is set to "UNKNOWN" (using SP_DBHELP).  And setting the owner to a valid value has stopped the events in the event log.  I used SQL commands similar to what you mentioned earlier:

    • use adfsconfiguration exec sp_changedbowner 'contoso\adfs'
    • use adfsartifactstore exec sp_changedbowner 'contoso\adfs'
    Wednesday, November 1, 2017 2:04 PM
  • Good :) When I meant untested is that I never ran the PowerShell version of it. The T-SQL in it has been tested and worked.

    Anyhow, I am glad this is fixed!


    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Wednesday, November 1, 2017 3:48 PM
    Owner
  • Your details here are vague.  I have installed SQL Server MGMT Studio.  There is no table called sys.schema.  
    Monday, December 4, 2017 7:03 PM