Blog do Ezequiel SQL Server Insights
Last week I had a situation that was tricky to solve and involved Kerberos! At this time you are thinking: “Oh no, here comes big trouble!”. And you are right, it was difficult to understand what was happening but the solution was simple.
Let’s get started!
The environment setup was the following:
UserA logins in ServerX and logs in into SharePoint site. Goes to SSRS library and choose a report to render. When UserA clicks the report to execute SharePoint gives a beautiful 401 Unauthorized!
First cause would be to review all SQL service SPNs and constrained delegations between SSRS service account and report data sources. But there was a strange behavior going on: Internet Explorer (IE) was asking for credentials to enter SharePoint site.Since Kerberos uses integrated authentication I checked IE configuration and found that in ServerX, IE was not properly configured for Intranet zone.
To support my theory I went to the Configure Kerberos authentication for SharePoint 2010 Products (white paper) document in this link and followed the steps described in page 59.
Verified that Integrated Windows authentication was enabled in the browser (Under Internet Options in the Advanced tab, make sure Enable Integrated Windows Authentication* is enabled in the Security section). Now, in Local intranet section it must be configured to automatically logon clients. Under Internet Explorer options, in the Security tab, select Local Intranet and click the Custom level button. Scroll down and make sure that Automatic logon only in Intranet zone is selected.
Scroll down and make sure “Automatic logon only in Intranet zone” is selected:
Ensure that Automatically detect intranet network is selected in Internet options -> Security -> Intranet Zone ->Sites.
If you are using fully qualified domain names to access the SharePoint Server web applications, ensure that the FQDNs are included in the intranet zone, either explicitly or by wildcard inclusion (for example, “*.vmlab.local”). This was my case so I added the site FQDN: mysharepointsite.contoso.local.
I closed all windows and saved IE definitions. I reopened a new browser session and tried to access SharePoint site again. At this time I was not prompted for credentials and the site was rendered correctly, so I assumed that Integrated Windows authentication was now working properly.
Did the test to render the report and the error still happened! What about rendering the report directly from ServerA where SharePoint site is installed? I repeated previous steps in ServerA and everything was working properly and the report rendered without the 401 error.
Strange?!? So we can assume that something was going on between ServerX and ServerA. With this symptom I went to Server’s A Event Viewer and in Security logs I looked for events with ID 4624 (Audit Success in Logon) while accessing from ServerX.
As we can see here authentication is NTLM. But I expected to see Kerberos! This lead to another clue. Let’s see how are SharePoint SPNs configure for host header.
I ran in a command line: setspn -q mysharepointsite.contoso.local. And got the following answer from DC:
Checking domain DC=contoso,DC=local
CN= MOSSAccount,OU=Service Accounts,DC=contoso,DC=local
Now we were getting somewhere! As we can see we had two SPNs HTTP/mysharepointsite.contoso.local registered under two different service accounts. The one I need was MOSSAccount since this was the SharePoint application pool service account running my SharePoint site.
We removed the duplicated SPN and voilá! Everything started working properly and in ServerA Event Viewer we could see the logon using Kerberos and Reporting Services reports executed with no errors and no 401 error.
From this situation we can conclude that we had the same SPN registered twice for two different service accounts. When a user logged in into SharePoint site the ticket being granted was not valid and authentication was failing back to NTLM.
In SharePoint we have the application pool set to Negotiate which causes the authentication to be done first by using Kerberos and if it is not succeeded it fails back to NTLM. Since users where being authenticate using NTLM, Reporting Services service was not able to get the correct user’s credentials and failed with an unauthorized message.
Validate that you have IE configured properly for integrated authentication and check if no duplicate SPN are found in your domain.
See you next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.