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  😉

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. 

  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). 

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!