Diagnosing Kerberos Delegation Issues on SQL Server, SharePoint, SSRS and SSAS
Until now, I have found working with Kerberos when setting up a SQL Server stack to be a complete nightmarish experience, mainly due to two reasons:
- Working with Kerberos usually requires access rights to Active Directory for the account setting up this authentication protocol on the stack, in order to be able to effectively diagnose the setup and also configure the Service Principal Names (SPN) for the various SQL Server and SharePoint service accounts, and setup delegation. This means SQL Server architects and Network Administrators need to collaborate in order to correctly configure the stack, which is often an unpleasant and long winded experience of trial and error.
- The lack of a centralized diagnostic and configuration tools for Kerberos setup on SQL Server makes this tasks very tedious, particularly if you follow the limited number of online resources out there to setup Kerberos, and find that they do not apply exactly to your situation, or do not work exactly as intended after following the lengthy steps, and you are left with a very limited option in terms of diagnosing exactly what went wrong.
My last encounter with Kerberos was setting up a complicated multi-hop scenario between SharePoint, SSAS and SQL Server and SSRS, it literally took two months going back and forth with out network admins and Microsoft support in order to finalize a solution.
But this might be all changing now, Microsoft has released a Kerberos Configuration Manager for SQL Server which aims to streamline the setup and diagnosis of Kerberos on SQL Server and SharePoint services.
I have personally not tried using this Kerberos configuration tool yet, so I cannot comment on how effective it is, but I am certainly very excited about it, even if it doesn’t deal with all setup scenarios yet or work as expected, because this actually shows acknowledgement from Microsoft that Kerberos setup is not a simple task even for SQL Server experts (not that I am implying I am one, although… :)), and an initiative towards filling this gap in the process of setting up a SQL Server stack.
Just in case this tool does not help in your particular scenario, here are a few articles that helped me in the past while setting up Kerberos:
- Kerberos authentication configuration for SQL Server and Analysis Services (double-hop)
- Kerberos authentication configuration for SharePoint and Excel Services
- Kerberos authentication configuration for Reporting Services and SQL Server (double-hop)
- Kerberos authentication configuration for SQL Server Analysis Services and SharePoint
- Kerberos authentication configuration for SharePoint
Leave a Reply
Want to join the discussion?Feel free to contribute!