First published on MSDN on Jun 10, 2009
This post is about a case I worked on back in April, which was a real doozy, to say the least. My customer was trying to set up SSO for Excel Services. In short, they wanted to create a couple of shared data connections, that used one credential to access a data source. Different domain groups and users would then have access to the data connections to utilize in Excel. Seems straight forward enough, right? ;)
The picture got a little trickier, because the customer has two domains, a user domain and a resource domain, with a one way trust in place. To simplify this piece, we set up a test lab that had a two way trust between the domains. For some reason, we still were getting what appeared to be access denied errors, when users would try to pull data from the shared data source via Excel. Knowing that we granted the user permissions to the data connection, and the service account trying to make a connection to the actual data had permission, we had to assume authentication was failing somewhere in the chain. To figure this out, we turned to the ULS logs. We set SSO and all the Excel components to verbose and started digging. We found the following critical error in the ULS logs.
“User DOMAIN\ServiceAccount-UserName failed to retrieve credentials using an access token for user DOMAIN\USERNAME, for enterprise application definition AdventureWorks. The error returned was 0x80070005. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).”
Doing some further investigation, I spotted the following message.
“AuthzInitializeContextFromSid() failed for S-1-5-21-913004495-3688694002-1748028306-21984”
I then hit the web to translate this message into English. :) I found the following hit.
http://support.microsoft.com/kb/842423
Although this issue didn’t relate to MOSS, it did relate to SSRS, which does similar activities to what some MOSS components do. Reading through the KB, things started to come together. In order to look up user domain group memberships, users, in this instance, the service account, needs rights to the TGGAU attribute of a user account. Whether or not this functionality is enabled depends on how your AD infrastructure was installed and configured.
In order to have SSO and Excel function correctly together, the service account needs to be able to look up what groups a user is a member of, to see if they’re in the group that has access to a shared data connection. If you don’t have the permissions set up the way they need to be, follow the directions in the KB article linked above. In short, assuming a 2003 domain functional level, there’s a domain group, Windows Authorization Access Group, that has the permissions necessary to do user domain group membership lookups. You will need to add your service account to this group. After that, you should be able to fully leverage this powerful and complex technology. I hope this saves someone out there some time, because it took some time to figure this one out.
Below I’ve pasted come entries from ULS logs that contain this issue, in hopes it helps people find this post.
“StoredCredentialsProvider.GetCredentialsFromStore: SsoProvider.GetCredentialsUsingTicket threw a SingleSignOnException. LastErrorCode: -2147024891, Exception: Microsoft.SharePoint.Portal.SingleSignon.SingleSignonException: A call into SPS Single Sign-on failed. The error code returned was '-2147024891'.
at Microsoft.SharePoint.Portal.SingleSignon.Credentials.GetCredentialsUsingTicket(UInt32 ui32Flags, String strApplicationName, String strTicket, String[]& Credentials)
at Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider.GetCredentialsUsingTicket(String Ticket, String AppID)
at Microsoft.Office.Excel.Server.CalculationServer.StoredCredentialsProvider.GetCredentialsFromStore(Request request, ConnectionInfo connInfo).”
“SSO has failed with the following error code -2147024891, exception: A call into SPS Single Sign-on failed. The error code returned was '-2147024891'. [Session: 21.3JktrWHF9yqzbHMMHYA+W90.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: DOMAIN\Username]”
“Unable to establish a connection using credentials retrieved from SSO. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSO are not valid. [Session: 21.3JktrWHF9yqzbHMMHYA+W90.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: DOMAIN\Username]”
“IsImpersonateMemberOfGroup() failed”
