Delegation, Claims, Active Directory…Oh My!…Aw Crap!

Heads up, this posting’s title was “User credentials could not be delegated and Active Directory” but I realized I needed a title that evoked my emotional state  ;-)

PowerPivotTwinsComic

Do not fret or worry, this is not “yet another user credentials delegation” blog. After all, there are already the postings including Troubleshooting #PowerPivot Excel Services connectivity (written by yours truly) and Excel Services delegation (by PowerPivotTwins partner Dave Wickert).

 

More importantly, if you want to debug and troubleshoot your way through the PowerPivot / Excel Services delegation issues, the coup-de-grace is Lee Graber’s excellent post: that The data connection uses Windows Authentication and user credentials could not be delegated

 Dave[1] And don’t forget Dave Wickert’s Help: c2wts has fallen and it cannot get up which contains a great tip about crypto service. 

.

But interestingly enough, Ayad Shammout (Database/IT Pro extraordinare from CareGroup Healthcare, he’s also pinged in the recent eWeek article: Execs Take BI into Their Own Hands) and I had ran into a rather hinke issue concerning user credentials delegation in a PowerPivot for SharePoint environment.  We had followed the above blogs and still could not resolve the problem. 

In Ayad’s PROD environment, clicking on the slicers on a PowerPivot workbook within PowerPivot for SharePoint would only work if you were a domain administrator.  If you were a regular domain account, you would get the error “The data connection uses Windows Authentication and user credentials could not be delegated”.  But in the DEV environment, everything worked normally.

By following Lee’s posting The data connection uses Windows Authentication and user credentials could not be delegated, we carried out the steps within How To: Request a Token from C2WTS and was better able to map out which AD accounts were able (and were not able) to map a claims token back to a windows identity.  What we had discovered was that for the AD accounts within PROD that were able to have their user credentials delegated were had the additional permissions of:

  • Were members of the “Pre-Windows 2000” NT group (i.e. have Pre-Windows 2000 Compatible Access”
  • Were part of the “Authenticated Users” group and had “Read Permissions”

Once we added the above permissions to the regular AD domain accounts, now they no longer had problems having their user credentials delegated (i.e. could click on slicers in PowerPivot workbooks within SharePoint). 

014

But there was still something interesting…if we go back and look at the DEV domain, none of the domain users required the above permissions yet they were able to have their user credentials delegated without any issues.   Further analysis by Ayad came to the realization that the DEV domain was a newly minted domain built on top of Windows 2003.  But, the PROD domain was a domain that was originally built on top of NT 4.0 and migrated to NT 4.0 SP3, Windows 2003.  (Note, both will be upgraded to Windows 2008 R2 in the near future).  And in NT 4.0, there was no such concept as the “Authenticated Users” group – this was introduced in NT 4.0 SP3.

So the lesson learned here as well is that if you have an upgraded active directory, you may run into issues like failure to delegate user credentials within SharePoint unless those domain accounts are included in the “Pre-Windows 2000” group and the “Authenticated Users” group (with Read Permissions).

Whew!

Many thanks also go out to Meghann Wilkinson Poku, Jason Fournerat, Corey Myers, Dave Wickert, and Lee Graber for helping Ayad and I solve this problem!

9 thoughts on “Delegation, Claims, Active Directory…Oh My!…Aw Crap!

  1. Pingback: Delegation, Claims, Active Directory….Again?! Frak! « Denny Lee

  2. Pingback: Delegation, Claims, Active Directory….Again?! Frak! « PowerPivot Twins!

  3. Denny,

    Thanks for this article. This saved me a ton of hair pulling. Just as an FYI, the issue I was having was not even related to Claims to Windows. Claims to Windows was not turned on in my case. It would not do pass through Windows Identity and allow me to authenticate. I was trying to create an External Content Type to SQL and it was failing. Once I added myself to the Pre-Windows 2000 group it worked. Voila!

    thanks.

  4. I had the problem that for all users access to the service c2wts was denied.

    What resolved the problem in my configuration:

    One allowed caller was missing c2wtshost.exe.config (Folder: C:\Program Files\Windows Identity Foundation\v3.5).

    This entry did the job:

    I restarted the service: And voila: Pivot slices where working!

  5. Pingback: SQLBI - Marco Russo : Solving security issue in PowerPivot for SharePoint and Power View

  6. Pingback: #PASSBAC – Ensuring Compliance of Patient Data with Big Data and BI | Denny Lee

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s