Introduction

When relying on Windows authentication to give your users access to SQL Server resources from Reporting Services, Active Directory will use either NTLM or Kerberos to resolve access rights.  If Reporting Services isn’t running on the same server as the resources you’re trying to access, then Kerberos is your only option to allow Windows credentials to be passed through.  If Kerberos isn’t pleased with your configuration, it will visit upon you the dreaded double-hop authentication issue.  You can resolve this issue by configuring Active Directory to appease it.  However, when Reporting Services is running on a server that’s also running IIS (Internet Information Services), things can become a bit trickier.  In this harsh environment, appeasement of Kerberos is not enough for us.  We will tame it.

Environment

In our environment we were running SQL Server Reporting Services 2005 (SSRS 2005) on a separate server that already had IIS installed.  SSRS 2005 requires IIS to be able to handle the web side of things and we didn’t want IIS on our SQL Server.  We also have SQL Server Analysis Services (SSAS) in our environment.  There are reports that use SSAS as a data source, which always requires Windows authentication.  So we had the web site for SSRS set up to run in IIS under an application pool that used a domain account while the Windows Service Identity for SSRS ran under NT Authority\NetworkService.

Of course, since the SSRS server and the SSAS server were on different machines, the dreaded double-hop authentication issue reared its ugly head.  This is caused when the Kerberos authentication protocol doesn’t have all the pieces it needs to function so NTLM is used and it doesn’t permit credentials to be passed from one server to another.  You’ll see an error that says:

“Login failed for user NT AUTHORITY\ANONYMOUS LOGON”

One of the requirements is a Service Principal Name (SPN) for the services that are communicating with each other on each server.  Depending on how each service is set up, there may not be an SPN in Active Directory for each service.  Fortunately, we were able to run commands similar to the following to appease Kerberos so it wouldn’t trouble us with double-hop authentication issues:

Setspn -a http/IISservername.FullyQualifiedDomainName domain\ApplicationPoolAccount

Setspn -a http/IISservername domain\ApplicationPoolAccount

Substituting the placeholders with an example, you’d get:

Setspn -a http/webserver.contoso.com DOMAIN\WebApplicationPool

Setspn -a http/webserver DOMAIN\WebApplicationPool

SetSPN is built into Windows Server 2008 although it can be installed on Windows Server 2003 by downloading and installing the Support Tools.  You can download these support tools here:

http://www.microsoft.com/en-us/download/details.aspx?id=15326

Our Domain Admin ran these commands so that IIS would have a service principal name in Active Directory for Kerberos to use, and then made sure that the server with IIS/SSRS was marked in Active Directory to allow delegation:

SSRS was allowed to pass the credentials of our users through SSRS to SSAS and our reports that ran on cubes worked wonderfully* for years.

*- As wonderfully as a DBA can reasonably expect with any piece of software.

The Upgrade

We had upgraded the database engine from SQL Server 2005 to SQL Server 2008 R2 and our next phase was to upgrade SSRS 2005 to SSRS 2008 R2 as well.  My “plan” for the first attempt was an in place upgrade on our development environment, followed by “mess with it to get it to work”.  I was comfortable with this approach because it’s a virtual server.  Our IT department took a snapshot, I did a backup of the SSRS database on the development SQL Server, and then I ran the upgrade.  Knowing the pass through authentication wouldn’t work, I changed the Windows Service account to run under the account we had used for the application pool in IIS.  I had hoped that the SPNs would still work since things are still working under the same account under HTTP.

As one might expect reading this far and noticing it’s not the end of the article, it didn’t work.  I knew in advance that SSRS 2008 no longer uses IIS.  It’s all handled in the SSRS service.  So when someone issues a request to the HTTP service, it goes in on port 80 and IIS handles it.  SSRS was also trying to listen to port 80.  Realizing this, I removed the virtual directories in IIS for Report and ReportService.  I then received 404 errors since IIS handles all requests on port 80.

At this point I backed out the upgrade on the development environment and discussed the issue with my manager.  I proposed the alternative of having SSRS running on the SQL Server instead.  Then we wouldn’t have a conflict on port 80.  In the end we decided to try to work it out on the web server since the production server is exposed to the web and it gives us less exposure to have fewer servers running HTTP hosts.

Upgrade #2

Attempt number two involved doing the upgrade as before but with the twist of configuring both ReportServer and ReportManager to listen on port 8080 instead of 80.  These configurations look like:

As before, I removed the directories in IIS for ReportServer and the ReportManager.  I also changed the Windows Service account to run under the account we had used for the application pool in IIS.  For reasons yet unknown I was still getting an error.  This time it was saying that:

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

After much frustration, a quick look at ...SSRSPath\Reporting Services\ReportServer\rsreportserver.config revealed that there was still an entry in there pointing to port 80 for the ReportServer path.  I fixed it, restarted the service and then everything worked!  Or so it seemed.

“Messing With It”

I was being asked repeatedly for my network credentials while browsing Report Manager and although reports that ran against the SQL Server RDBMS with SQL Server authentication worked fine, my reports that used SSAS didn’t work.  Remembering my old foe Kerberos, I did some research on the web and found that you can specify the port when creating a service principal name with SetSPN.  With this knowledge in hand, I crafted a pair of SetSPN commands for our Domain Admin to run:

Setspn -a http/IISservername.FullyQualifiedDomainName:8080 domain\ApplicationPoolAccount

Setspn -a http/IISservername:8080 domain\ApplicationPoolAccount

Substituting the placeholders with an example, you’d get:

Setspn -a http/webserver.contoso.com:8080 DOMAIN\WebApplicationPool

Setspn -a http/webserver:8080 DOMAIN\WebApplicationPool

The commands gave our Domain Admin an error when he tried to run them.  I asked if he typed them in or pasted them into the command window.  He pasted them and for some reason SetSPN doesn’t like that.  The “-“ changed in Outlook to “–“.  So he typed them out and they ran.

Now I wasn’t being constantly prompted for my credentials, but my reports that ran against SSAS still didn’t work.  I would get an error that said:

An error was encountered in the transport layer. The peer prematurely closed the connection.

It seems that the beast was still not appeased and was coming at me from another angle.  To track it, I enabled Kerberos event logging and found that Kerberos couldn’t find an entry for MSOLAPSvc.3 on the development SSAS server.  This article explains how to enable Kerberos event logging: http://support.microsoft.com/kb/262177

Hoping that SetSPN could still be used to appease Kerberos, I crafted another pair of commands to add this SPN into Active Directory:

Setspn -a MSOLAPSvc.3/SSASServername.FullyQualifiedDomainName SSASServername

Setspn -a MSOLAPSvc.3/ SSASServername SSASServername

Substituting the placeholders with an example, you’d get:

Setspn -a MSOLAPSvc.3/analysisserver.contoso.com analysisserver

Setspn -a MSOLAPSvc.3/analysisserver analysisserver

We specified the server name as the last parameter instead of an account name because SSAS is configured to run under the local system account.  Normally it should register the SPN on installation; but, for some reason it didn’t for us.  After adding the SPN, the reports that used SSAS data worked and I was rather pleased.  It almost looked like I knew what I was doing!  I presented the solution to my manager.

Uh yeah... could you just...

The only part that we weren’t sure about was the port 8080 piece.  We’d have to change all of our links to handle it and we weren’t sure if it wouldn’t cause us some network related headaches later on.  So we spoke to our network/server guys and decided to have SSRS listen on a second IP address on the server.  Setting up the second IP address was easy since the server already had another IP address on it.  Of course, pass through authentication wouldn’t work because there’s no way to add an entry in Active Directory with SetSPN to allow authentication based on which IP address was used instead of which port was used to connect to the server.  Kerberos looks at which IP address is resolved in DNS.  In the case of our server, that’d be IIS.  We needed a DNS entry so that we could configure SSRS to look at a specific host header.

Our IT department created an entry in DNS called devreport and pointed it to the IP address we decided we’d use for SSRS on our development server.  Now we were able to assign a host header to SSRS:

This needed to be done for both the Web Service URL and the Report Manager URL in Reporting Services Configuration Manager.  Next, we need to add an SPN for this host header so we tried:

Setspn -a HTTP/hostheader domain\ApplicationPoolAccount

But, for some reason that didn’t work.  We got an error that said, “Unknown parameter http/hostheader. Please check your usage.”

We checked the help on setspn, and it looked like maybe we needed to run it as setspn -U -A.  But that didn’t work either.  Then the following worked:

Setspn -S HTTP/hostheader domain\ApplicationPoolAccount

Substituting the placeholders with an example, you’d get:

Setspn -S HTTP/devreport.contoso.com DOMAIN\WebApplicationPool

Next, I logged into Report Manager successfully; however, my attempts to run a report that uses Analysis Services as a source failed.  Opening up Internet Explorer and adding this new site to my list of intranet sites then allowed it to work.  We added a redirect in IIS from the old path to the new one, then planned and implemented the same changes in production.  It went well.

Flushing out more SPN issues and learning something the hard way

Later, we added Microsoft System Center 2012 to our environment and saw SetSPN related warnings like this on our SQL Servers:

SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.

Service Account: Name of SQL Server Engine Service Account

Missing SPNs: MissingSpnList: MSSQLSvc/FQDN:1433

Substituting example names:

 MSSQLSvc/sqlserver.contoso.com:1433

They weren’t causing us any issues, but clearly it meant that when people were logging in with Windows authentication, it was using NTLM instead of Kerberos.  To try to resolve it we created an SPN using the following command:

Setspn -a MSSQLSvc/FQDN:1433 servername

It looked something like:

Setspn -a MSSQLSvc/sqlserver.contoso.com:1433 sqlserver

This caused Windows authentication on our SQL Server to fail.  By that I mean no users, nor applications worked anymore on our SQL Server!  Since we had never had any problems creating SPNs in the past, we did this during regular business hours.  There was a bit of a mad scramble for a few minutes to undo it followed by our network administrator making a vow never to modify SPNs outside of a maintenance window.

Where did we go wrong?  The last part of the command (account name) used the name of the server, which is what you should do if SQL Server is running under a local account.  However, we were using a domain account and the SPN should have been registered there.  Previously, we had set the delegation properties of our account to allow it to perform pass through authentication, which isn’t necessary for this resolution but we figured it’d be useful in case we want to access resources from another server while on the SQL Server, say over a linked server:

So what we needed was to create an SPN for the SQL Server service account:

Setspn -a MSSQLSvc/FQDN:1433 serviceaccount

Substituting some sample data it looked like:

Setspn -a MSSQLSvc/sqlserver.contoso.com:1433 DOMAIN\sqlservice

We did this on the domain controller and within a few minutes it had replicated across the network.   We checked this by running:

Setspn -L domain\sqlservice

After making sure that the applications still worked, we forced System Center to update the status of the service principal name alert.  The alert didn’t return.  We noticed that logins to the SQL Servers were quicker.  This is most likely due to Kerberos being more efficient and requiring less network traffic than NTLM.  The beast was finally tamed.

Summary

Although one may run into issues getting pass through authentication to work properly for Reporting Services on a server where it’s cohabitating with IIS, it’s certainly doable.  One needs to use a separate host header and create a service principal name for that host header.  Further, looking into Kerberos related warnings and errors can give you opportunities to improve the performance and security of your connections.  Just be sure to do it during maintenance time since incorrect configuration can lead to problems.