Partilhar via

Solving the Reporting Services Login issue in the February CTP of SQL Server 2008

  • Artigo

I’ve seen customers run into authentication issues using the SQL Server 2008 February CTP of Reporting Services (you should all give it a try!). This post provides solutions and a detailed discussion of the underlying causes. The material in this post is applicable to all SSRS deployments, beyond the scope of solving the specific issue.

Symptom:

A) When you access report server or report manager, you get a login prompt multiple times and eventually you get a blank screen.

B) When you try to publish reports/models from Business Intelligence Development studio, you get access denied errors.

Solutions:

1) Remove RSWindowsNegotiate and ensure RSWindowsNTLM is specified in the file rsreportserver.config

2) OR, change the report server service account to Network Service

3) OR, configure a specific hostheader for SSRS, configure your DNS server to understand that hostheader, and configure your domain controller to have an SPN for the hostheader and the report server service account.

Clearly #1 is the easiest but not necessarily the right change.  The rest of this post attempts to explain the cause and the merits of each solution.

Cause:

In the cases I have seen, the problem is due to Kerberos authentication.  Kerberos is an authentication protocol that allows clients that create authentication tokens to associate a specific destination to that token.  In the failure case there is a mismatch between the destination specified in the token and the report server process configuration.  Due to this mismatch, the underlying Kerberos authentication scheme supported by Windows prevents report server from authenticating the user.

So how does this apply to SSRS 2008?

By default in the February CTP, setup includes RSWindowsNegotiate in the AuthenticationTypes listed in the file rsreportserver.config.  Negotiate is an authentication type that specifies the server can either accept Kerberos or NTLM authentication tokens.  The client application decides what kind of token to pass to the server.

Side note: in later builds we changed the setup behavior to add RSWindowsNegotiate only if network service is used in setup as the report server service account.

Internet Explorer (perhaps other browsers, too) uses Kerberos authentication if the server it connects to supports it.  In order to use Kerberos, IE (the client application) must specify the destination in the authentication token it creates. It does this by examining the URL and asking the Domain Controller for an SPN for the destination.  SPNs are Service Principal Names and are used to identify resources on the network.   

There are a number of default SPNs created by the Windows ecosystem.  One of these is for the Network Service account for the HTTP/Host SPN.   This is the SPN used by IE to authenticate to SSRS.  If SSRS is not running as the Network Service account the SPN may not resolve correctly and the Kerberos authentication will fail.

The default SPN HTTP/Host for Network Service account applies to SSRS because in SSRS 2008 we use HTTP.SYS to listen for network traffic.  All services that use HTTP.SYS share this SPN by default, and they all can be subject to this problem if they support Kerberos or Negotiate authentication.

If SSRS is running under the Network Service account, things just work.  However, if SSRS is running under a domain account, IE will include an SPN in the Kerberos authentication token that does not resolve to the SSRS service account.  This will cause continued failure to authentication, and thus the login prompt.

NTLM is different than Kerberos - NTLM does not include a destination in the token and it will allow report server to successfully authenticate the user.   Because Kerberos authentication specifies a destination in the tokens it uses, it is in some degrees more secure than NTLM.

So what should I do to fix it?

Firstly, you need to determine how much benefit using the more secure Kerberos tokens are in your environment.  Generally, I would recommend that you use Kerberos authentication since having destination specific tokens is a good practice. 

Secondly, you need to determine if you are OK changing the SSRS service account to Network Service.  We recommend that you use a Domain Account for the SSRS service account - this helps to isolate SSRS from other processes running on the computer.  Thereby, it helps make the computer that hosts SSRS more secure. 

Clearly the best solution security wise is to configure a hostheader and have a specific SPN. However, it is also the hardest solution to deploy.  Most computer administrators do not have the ability/permission level to make the required DNS and Active Directory SPN changes.

Is there a clear winner here?  No - Unfortunately the first and second options achieve different objectives from a security perspective.  Option 3, though advised, will not commonly be used due to complexity of configuration. For our setup experience, we decided that we'll use Negotiate (Kerberos) if in setup you select Network Service and that we'll use NTLM if you select a domain account for the SSRS service account.   This is a good compromise since it allows report server to work by default and be as secure as possible after installation.

More sophisticated IT organizations should consider option 3 for their data center deployments.

In the interest of completeness - you may encounter this issue when changing the service account through the configuration tool.   At the time of writing, you will have to ensure the Authentication Types are correct based on the service account you're using. 

Is this problem new in SQL 2008? Could I have seen it in SQL 2005?

No - the problem is not new.  Yes - you could have seen it in SQL 2005.  The problem would manifest differently.   In SSRS 2005, we set the Report Server application pool identity to Network Service.  The reason for this was the very same - otherwise there are authentication failures due to the SPN.   You can try this for yourself by creating 2 application pools in IIS one with Network Service, the other with a Domain Account and trying to authenticate to each. 

Take care and good luck,

-Lukasz

Comments

  • Anonymous
    July 18, 2008
    In a previous post , I talked about issues with repeated login prompts when trying to access either Report