SharePoint Adventures : Using Kerberos with the Report Server

Previous Post: SharePoint Adventures : Setting up Reporting Services with SharePoint Integration

In the previous post, I walked through getting RS 2008 R2 Integrated with SharePoint 2010. What I didn't touch on was if you wanted to get this work with Kerberos. Kerberos itself can be complicated. This is partly because you need to track so many things. And, as the deployment becomes more distributed, you have to track more things.

A while back, I posted a blog post describing my Kerberos Checklist. I'll use this as we step through my SharePoint deployment to get Kerberos working in this environment.

Before we get into the details, there is one piece I want to point out that is special with SharePoint 2010. The authentication model that you select for your site makes a big difference in whether this will work or not. SharePoint allows you to choose between Classic and Claims. If you choose to have a Claims site, you will not be able to get Kerberos to work with RS 2008 R2 when integrated with SharePoint 2010. If the site is Claims based, you won't be able to change it back either. Part of the reason why Kerberos won't work is because when we detect you are in a Claims site, we always go with Trusted Authentication from the RS Perspective. This means that a Windows Token will not be passed from SharePoint to the Report Server. An SPUser token will be passed instead. My next post will go into how you can determine if your site is Classic or Claims.

That being said, lets dive in…

For this setup, I only have 3 servers involved.

	Server Name	Service Account	Delegation Required	Custom App Settings?
SharePoint	DSDContosoSP	DSDCONTOSO\spservice	Yes	Yes
Reporting Services	DSDContosoRS	DSDCONTOSO\rsservice	Yes	Yes
SQL Server	DSDContosoSQL	DSDCONTOSO\sqlservice	No	No

SharePoint

Service Principal Name (SPN)

Because we are going to do Kerberos we are going to need some SPNs. In the table above, we know that the SharePoint Service account is DSDCONTOSO\spservice. This means that those SPN's will need to go on that user account (DSDCONTOSO\spservice) and not the Machine Account (DSDContosoSP). Had we been using LocalSystem or Network Service, the SPNs would have gone on the Machine Account. That's always how you figure out where the SPN's go. It's always based on the context that the Service is running under.

So, let's have a look at DSDCONTOSO\spservice. We'll use SETSPN to do that. Starting with Windows 2008, SETSPN ships with the operating system and is available directly from a command prompt.

We can see that there are no SPN's registered on the spservice account. You'll also notice that I can run SETSPN with or without the Domain Name. This is because I only have a single domain. If you had multiple domains, supplying the domain name tells SETSPN where you want to modify the account at. This really is helpful if you have the same account name in multiple domains. Going forward, I will leave out the domain name.

As a quick check, I also look at the Machine Account (DSDContosoSP). This is just a double check to make sure I won't run into a duplicate situation.

Because SharePoint is a web application, we are interested in the HTTP SPN. We do not see one listed on the Machine account. You can take note of the HOST SPN though. This will be found on any Machine Account. You should never see these on a User account. They get created when a Machine Account is created. The HOST SPN will cover the HTTP service if you are running within the context of the Machine Account (LocalSystem or Network Service). Had that been the case, we wouldn't have needed and HTTP SPN. But, because we are using the spservice user account, we will need to put an HTTP SPN on the spservice account.

NOTE: Domain Admin permissions are required to add (-a) or delete (-d) an SPN. Anyone can list (-l) out an SPN.

We added two SPNs to the spservice account. HTTP/dsdcontososp and HTTP/dsdcontososp.dsdcontoso.local. HTTP SPNs are based on the URL that you are going to use. In this case we are just using the machine name for the URL (https://dsdcontososp). Internet Explorer will convert this to the Fully Qualified Domain Name (FQDN) when it builds out the required SPN. So the SPN request for that URL would be HTTP/dsdcontososp.dsdcontoso.local. And we have just added that.

NOTE: HTTP SPNs should NOT have a port listed. They are purely based on the host within the URL without the port number. This means that you have two sites running on different ports, you should use the same Service Account for both as you will have a shared SPN between the two. For example: https://dsdcontososp & https://dsdcontososp:5555 both use the following SPN: HTTP/dsdcontososp.

What about the Netbios SPN? Well, I always add that for good measure. Hopefully it will never be needed. But, on the off chance that the name lookup fails, we will be covered. When we do the reverse name lookup to get the FQDN, we have to go out to the DNS Server to do that. If for whatever reason the DNS Lookup fails, we will just use the netbios name. So, the SPN would look like HTTP/dsdcontososp. The fact that we added both means I won't be hit by intermittent DNS issues and end users won't be interrupted unnecessarily. So, my take on it is to always add both the NETBIOS and FQDN SPNs.

If we look at the spservice account now, we will see both SPNs that we added.

Delegation

We know that we are going from the SharePoint server to the Report Server. In order for credentials to be forwarded from SharePoint to RS, we need to give the Service Account permission to delegate. By default, this is disabled.

NOTE: Domain Admin permissions are required to modify delegation settings on an account.

NOTE: The delegation tab will only be visible if SPN's are present on that account.

The Delegation Tab of the account is where we will find these settings. Here is how the options break down:

Do not trust this user for delegation	No Trust. We cannot delegate.
Trust this user for delegation to any service (Kerberos only)	Full Trust. We can delegate to any service.
Trust this user for delegation to specified services only	Constrained Delegation.  Requires you to list the services that we can delegate to in the list below the radio dials.
Use Kerberos Only	Constrained Delegation with Kerberos Protocol only
Use any authentication protocol	Constrained Delegation with Protocol Transitioning.  Useful on the Claims side of things.

For this example, I'm not going to go into the Constrained Delegation side of things. I will do that in a later post. We will just stick with Full Trust.

So, I select "Trust this user for delegation to any service (Kerberos Only)". Please take into account that Constrained Delegation is the more secure option. But, it also presents its restrictions as a result. Stay tuned for more information about that.

SharePoint Settings

The SPN and delegation settings are really the basic Kerberos settings needed for any application. However, SharePoint has some app specific settings that we need to pay attention to. For this, we will head over to SharePoint's Central Admin Site.

We will go to Application Management and then to Manage Web Applications.

You will select the site you are interested in, in my case it is the SharePoint - 80 site, and then click on Authentication Providers.

Click on Default.

We want to choose "Negotiate (Kerberos)" and then hit "Save".

This configures that SharePoint site to use Negotiate. Negotiate will always attempt to use Kerberos first if an SPN is available to use. We can test to see if this is working properly by going back to the SharePoint site. It should come up as normal without any prompts for credentials or 401.1 errors. If you encounter that, something isn't right.

However, at this point our reports should no longer work. The underlying error here will be a 401.1 against the Report Server because it hasn't been setup for Kerberos.

In the SharePoint ULS Log we will see:

02/21/2011 08:16:46.68 w3wp.exe (0x0F44) 0x0F68 SQL Server Reporting Services UI Pages aacz High Web part failed in SetParamPanelVisibilityForParamAreaContent: System.Net.WebException: The request failed with HTTP status 401: Unauthorized.
at Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods()
at Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.IsSecureMethod(String methodname)
at Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.SetConnectionSSLForMethod(String methodname)
at Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)
at Microso... 23c6017c-3d37-4b70-b378-d5dd875518f6

Which brings us to the next stop in our journey…

Reporting Services

Service Principal Name (SPN)

Our service account for Reporting Services is rsservice and not Network Service, so the SPN's will go on the rsservice account itself. Also, Reporting Services is a web application, so we are still sticking with an HTTP SPN. Lets check out what is on the Service Account and the Machine Account.

Everything looks good here. Again, HTTP SPN's are URL based. So, we are going to create the SPN based on the url which you can get from the Reporting Services Configuration Manager under the Web Service URL Tab.

Based on that, our SPNs will be the following - HTTP/dsdcontosors and HTTP/dsdcontosors.dsdcontoso.local

And doing a listing of the rsservice account, we should see two SPNs on it.

Reporting Services Settings

I'm doing Settings first instead of Delegation to show that Delegation may not be needed. However, there is a setting for Reporting Services that is needed in order for Kerberos to work successfully against Reporting Services. This setting resides in the rsreportserver.config file which by default should be found at :

C:\Program Files\Microsoft SQL Server\MSRS10_50.<instance name>\Reporting Services\ReportServer

The setting that we are interested in is Authentication Type. If you look at the current setting, you may see different results.

<Authentication>
<AuthenticationTypes>
<RSWindowsNTLM/>
</AuthenticationTypes>
<RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
<EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

For mine, I see RSWindowsNTLM under the Authentication Types. This is because when I first setup Reporting Services, I used a Domain Account instead of the default Network Service. When you do this, it will default the setting to RSWindowsNTLM. If I would have chosen Network Service as the Account to use during setup, this setting would have reflected RSWindowsNegotiate. And then you could later change it to a Domain Account without this setting changing.

All I need to do for mine to get Kerberos working is to change it over to RSWindowsNegotiate. You can either add it on top of RSWindowsNTLM or replace RSWindowsNTLM.

NOTE: RSWindowsNegotiate is specific to Internet Explorer. Other browsers may need RSWindowsKerberos instead. You will need to test that to see what works best for your configuration.

In my case, I just added it on top of RSWindowsNTLM

<Authentication>
<AuthenticationTypes>
<RSWindowsNegotiate/>
<RSWindowsNTLM/>
</AuthenticationTypes>
<RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
<EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>

At this point, my Hello World report should come up ok as I'm not using any data sources for it.

However, the report where I do have a data source will fail. However, it is with a different message this time.

In the ULS log, we won't see an error by default, because the Reporting Services Monitoring trace points have not been enabled within Central Admin. The error itself will be a "Login failed for user 'NT AUTHORITY\ANONYMOUS'". That error comes directly from SQL Server. Whereas the 401.1 errors were Web related errors.

Delegation

In order for Reporting Services to forward credentials to a back end data source, we need to enable delegation permissions on the Service Account. The data sources are process within the Report Server Windows Service and not SharePoint, so the SharePoint settings don't help us here.

We will do what we did with the SharePoint Account and enabled Full Trust for the Reporting Services Account.

This in itself is not enough to get our Report with the Data Source working though. This just allows Reporting Services to forward the user's credentials to another Service. That service we are forwarding to still needs to be setup properly. In this example it is SQL Server we are forwarding to and it does not have it's SPN configured yet. So, we will still fail with a Login Failed message from SQL. Reporting Services at this point should be good to go though.

Which brings us to the last stop in our journey…

SQL Server

Service Principal Name (SPN)

I have previously written a blog post concerning the SQL Server SPNs. What SPN do I use and how does it get there?

It goes through how SQL Server can make use of it's ability to manage the SPN for you, and which SPN is needed based on which protocol you are trying to connect with. I won't go through all the details again here, so I will make a few assumptions.

First, that the ability for SQL to manage it's SPNs is not working because I'm using a Domain Account and I haven't given it the permissions necessarily for that to occur. You can also verify this in the SQL ERRORLOG:

2011-02-21 08:58:01.40 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Second, that I'm going to be connecting with the TCP protocol and not Named Pipes.

The SQL Service is using the sqlservice account. And because we are using the TCP Protocol, the SPN will need the port number. In this case, it is a default instance, so we know the port will be 1433. So, our SPN will look like the following for SQL - MSSQLSvc/dsdcontososql:1433 and MSSQLSvc/dsdcontososql.dsdcontoso.local:1433. You'll notice I'm doing both the NETBIOS and FQDN SPNs here. It is the same reason as with the HTTP SPN. In this case, the SQL Client connectivity components will do a reverse lookup on the server name to try and resolve the FQDN. So, with everything working as it should, it should always try to get the FQDN SPN even if you supply the NETBIOS server name in the connection string.

The SPNs for SQL Server are derived from the Connection String that the client is using. The client in this case being Reporting Services. Reporting Services is a .NET Application, so it is using SqlClient to connect to SQL.

We can see that there are no SQL SPNs registered on the service account or the machine account

So, lets go ahead and add the SPNs.

Everything looks good on the SPN front. For good measure, you may want to use the setspn tool to search for duplications. It is a new feature of SPN that was added in Windows 2008. It is the -X command. It will search the entire domain for duplicates. You should never have a duplicate as it will cause an error.

Looks like we do not have any duplicate SPNs. At this point the Report that we have with a data source to the SQL Server should run ok as there are no application specific settings that needs to be set for SQL Server outside of the SPN.

NOTE: Depending on how you have approached the setup, you may still encounter an error due to the fact that the failed Kerberos requests may still be cached. You can either wait for cache to clear out, or you can restart the services to get it going. I had to recycle SharePoint and Reporting Services for it to start working on my box, as well as log off and back in (or just run klist purge on the client).

Delegation

For your back end server, you may not need to enable delegation. If the hops stop with this server, then we are done and do not need delegation. However, if this backend server will be continuing on to another service, then delegation will be necessary if it will try to forward the windows user credential.

A great example of this with SQL Server is the use of a Linked Server. However, just the fact that you have a Linked Server doesn't mean that you need delegation. It is dependent on how you configure authentication on the Linked Server.

If "Be made using the login's current security context" is selected for the Linked Server, then we will need to enable delegation for the SQL Service account.

There are also other things that may require delegation from SQL. SQLCLR is one that might depending on what you are doing. The general rule of thumb is that if anything within SQL is trying to reach out to another resource and will need to send the current user's credentials, than you will need Delegation enabled on the SQL Service Account.

In my case I'm not, so I'm going to leave it alone.

Summary

So, that's it. We went through each stop along the communication path (SharePoint, RS and SQL), and we validated the settings for each one as we got there. We also saw that certain things began to work as we enabled items. The Report without the data source started working with SQL being setup because we weren't reaching out to SQL. And, we also looked at when you need to enable delegation or not depending on whether that service needed to reach out to another service. For Reporting Services, had we not been hitting a data source, we may not have needed to enable Delegation on the rsservice account as I showed with the HelloWorld report. But when we need to access data, we then need to have it if we want to use Kerberos. The other option would be to store the credentials within the data source.

Hopefully this helps someone when trying to setup this type of deployment, or any deployment that requires Kerberos in order to work.

Adam W. Saxton | Microsoft SQL Server Escalation Services
https://twitter.com/awsaxton

Comments

• Anonymous
  March 18, 2011
  Excellent post.Thank you very much for sharing this valuable information.

• Anonymous
  September 24, 2013
  Even though this was written 2 years ago, it's still very valid. I was struggling for 2 weeks with an issue and missed the  <RSWindowsNegotiate/> step which caused some big issues. Anyways, thank you very much for a great article!

• Anonymous
  August 04, 2014
  The comment has been removed