locked
ADFS SQL Server Confguration Error RRS feed

  • Question

  • I have ADFS 2.0 configured in a SQL Farm.  I checked the event viewer and noticed a recurring error coming from the ADFS service.  I found the error listed on MSDN (http://technet.microsoft.com/en-us/library/ff730198.aspx) but it didn't say how to fix it.

    Failed to register notification to the SQL database with the connection string Data Source=******;Initial Catalog=AdfsConfiguration;Integrated Security=True for cache type 'ServiceStateSummary'. Changes to settings may not take effect until the Federation Service restarts.

    Additional Data

    Exception details:
    The EXECUTE permission was denied on the object 'xp_logevent', database 'mssqlsystemresource', schema 'sys'.

     

    This looks like a permissions issue but I ran the SQL Scripts that the ADFS command line util generated.  Any help is appreciated.

    Tuesday, October 26, 2010 6:14 PM

Answers

  • I should probably clarify the misconfiguration part in case anyone runs into strange sql errors such as this.  The underlying cause was the SQL Server was running as a local user account, not Network Service or a domain account.  The Queues and Sprocs included in ADFS use "Execute as Owner" which means it gets ran in the context of the owner of the schema.  The owner is a domain user and Sql Server ran xp_loginInfo to retrieve the user info.  Since Sql Server was running as a local user account it couldn't authenticate against the domain.  To correct the issue we changed Sql Server to run as a domain user.
    • Marked as answer by Shawn Brandt Wednesday, January 12, 2011 5:34 PM
    Wednesday, January 12, 2011 5:34 PM

All replies

  • The issue was unrelated to ADFS.  Turns out it was a misconfiguration of the SQL Server.
    • Marked as answer by Shawn Brandt Tuesday, November 2, 2010 9:15 PM
    • Unmarked as answer by Shawn Brandt Wednesday, January 12, 2011 5:34 PM
    Tuesday, November 2, 2010 9:15 PM
  • I should probably clarify the misconfiguration part in case anyone runs into strange sql errors such as this.  The underlying cause was the SQL Server was running as a local user account, not Network Service or a domain account.  The Queues and Sprocs included in ADFS use "Execute as Owner" which means it gets ran in the context of the owner of the schema.  The owner is a domain user and Sql Server ran xp_loginInfo to retrieve the user info.  Since Sql Server was running as a local user account it couldn't authenticate against the domain.  To correct the issue we changed Sql Server to run as a domain user.
    • Marked as answer by Shawn Brandt Wednesday, January 12, 2011 5:34 PM
    Wednesday, January 12, 2011 5:34 PM