Kerberos Woes

[Wikipedia] Cerberus, (pronounced /ˈsɜrb(ə)rəs/);[1] Greek form: Κέρβερος, /ˈkerberos/[2] in Greek and Roman mythology, is a multi-headed hound (usually three-headed) which guards the gates of Hades, to prevent those who have crossed the river Styx from ever escaping.

I think Microsoft got the name right although the Windows version of Kerberos has more than three heads for sure. Yet another weekend spent in troubleshooting Kerberos. This one has an interesting setup.

  1. SharePoint Server 2010 on a Web Front End (WFE) box.
  2. SharePoint Server 2010 (core install) + SQL Server and Reporting Services 2008 integrated with SharePoint on a second box.
  3. Analysis Services 2008 R2 on a third box.

The customer wanted this setup to minimize the SQL Server licenses. Microsoft recommends installing Reporting Services on the WFE servers but this requires as many SQL Server licenses as the number of the WFE servers, plus probably two more (for SQL Server to host the SharePoint configuration databases and Analysis Services (if it is on a separate box). By contrast, the above setup requires two SQL Server licenses irrespective of the number of the WFE servers.

We hit issue #1 when we tried to deploy from BIDS and we got a login prompt that just won’t go away. This was related to the fact that the SQL Server setup configures Reporting Services for NTLM authentication. Since this scenario required Kerberos delegation, we have to change the following setting in the rsreportserver.config file:

<Authentication>

<AuthenticationTypes>

    <RSWindowsNegotiate/>

    <!–RSWindowsNTLM/–>

</AuthenticationTypes>

<EnableAuthPersistence>true</EnableAuthPersistence>

</Authentication>

As you can see, we had to disable NTLM and add Negotiate. This helped us to a point where BIDS can deploy reports to the SharePoint site and we can manage data sources and report properties. However, for some reason, after a few minutes the data source and report definitions somehow would get invalidated and trying to access their properties result in HTTP 401 User unauthorized error. Re-deploying the reports and definitions “fix” the definitions for a few minutes and then 401 error again.

More troubleshooting…and we figured out the issue was related to a misconfigured SPN entry for the SSRS service account on the ServicePoint core (database) server by executing setspn -l <SSRS service account>. The results didn’t include http/<the account used for SQL Server>. Once we got the Active Directory administrator fix this, the issue went away.

We found the Configuring Kerberos Authentication for SSRS 2008 R2 with SharePoint 2010 whitepaper very useful. It’s a must read before venturing into the Kerberos candy land

Good luck!

Applied PowerPivot Course Available

082310_0058_PowerPivotT1I am excited to announce that Prologika has added an Applied PowerPivot course to the list of our training offerings in response to the strong interest for self-service BI. The class can be delivered as two-day online class (4 hours each day) or as one full-day onsite class. The cost for the online version is $599. Applied PowerPivot is designed to help students become proficient with PowerPivot and acquire the necessary skills to implement PowerPivot applications, perform data analysis, and share these applications with other users. The full course syllabus is available here. I scheduled the first run for September 21st. Happy self-service BI!

PowerPivot Implicit Data Conversion

A student noticed an interesting behavior while I was teaching my PowerPivot class last week. He noticed that PowerPivot lets you join a text-based column to a numeric column. As it turns out, PowerPivot does an implicit conversion to text when it discovers that you are attempting to join columns of different data types. However, as a best practice, you should convert numeric columns to a numeric data type, such as Whole Number. This will help PowerPivot minimize storage and improve performance.