1dent1ty cHa0s

Oh boy, where to start, we have been having various issues with SQL applications failing with different security related error messages and we did not see a connection until just today. The two prominent issues we saw were:

• Could not apply patches to an ILM 2007 FP1 installation running on SQL Server 2008 with the servers in a Windows 2008 domain/forest, the errors we got were:

Error 25009.The Microsoft Identity Integration Server FP1 setup wizard cannot configure the specified database. Invalid object name 'mms_management_agent'. A required privilege is not held by the client.

MSI (s) (6C!80) [16:34:17:656]: Product: Microsoft Identity Integration Server -- Error 25009.The Microsoft Identity Integration Server FP1 setup wizard cannot configure the specified database. Invalid object name 'mms_management_agent'. A required privilege is not held by the client.

• SQL Server Reporting Services report subscriptions were failing to run in the SQL Agent with the following errors:

SQL Server Scheduled Job '52840C4F-5D9F-4CAA-96BE-4C587F655571' (0xBB61E338688B8C459E28A61A6761669D) - Status: Failed - Invoked on: 2009-09-03 17:40:03 - Message: The job failed.  Unable to determine if the owner (DEV\svc.ssrs.ilm) of job 52840C4F-5D9F-4CAA-96BE-4C587F655571 has server access (reason: Could not obtain information about Windows NT group/user DEV\svc.ssrs.ilm', error code 0x5. [SQLSTATE 42000] (Error 15404)).

Subsequently, it was this troubleshooting technique using xp_logininfo found by Jaime Martinez that led us to the eventual solution posted by Matticus:

• Find the account that you're getting the error on and open up a new query in SQL Management Studio and then run the xp_logininfo command against it – in our case it looked like this:

xp_logininfo 'DEV\svc.ssrs.ilm'

• This command generated the following new error:

Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user DEV\svc.ssrs.ilm', error code 0x5.

As it turns out there is a new built-in security group in Windows Server 2008 domains called BUILTIN\Windows Authorization Access Group. The description on this group reads, "Members of this group have access to the computed tokenGroupsGlobalAndUniversal attribute on User objects". This somehow causes issues when certain operations require enumeration of a person's group memberships (the computed tokenGroupsGlobalAndUniversal attribute).

Resolution

Add the domain service account for your SQL Server (your SQL Server service account) to the BUILTIN\Windows Authorization Access Group group. You don't need to restart anything, it just starts working from that point forward. What was bizarre is that this also fixed my problem with applying the patches to ILM!