Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Highly Available, Scale-Out SQL Server 2008 R2 Reporting Services Farm


By:   |   Read Comments (25)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services Performance

Problem

Delivering reports is becoming more critical due to the increasing demand for business intelligence solutions. And while there are a lot of guides that walk us through building a highly available database engine, you'll rarely see one for SQL Server Reporting Services. How do I go about building a scale-out SQL Server 2008 R2 Reporting Services running on Windows Server 2008 R2?  Check out this final tip in the series.

Solution

In the last part of this series, we will be configuring view state validation, hostname and URL for the NLB cluster as well as some registry hacks to workaround the HTTP 401 error message on all of the NLB cluster nodes.

Configuring View State Validation on all of the NLB Cluster Nodes

In the previous tip, we've managed to configure SQL Server 2008 R2 Reporting Services on the second node of the NLB cluster as well as joined it to the scale-out deployment. While the web service URLs work fine after testing access, we still need to take care of a few stuff. Note that SQL Server 2008 R2 Reporting Services still uses ASP.NET in it's core platform but without IIS. We also need to remember that, as reporting services is a web application, the web is stateless. This means that once a web page is processed on the server and rendered on the browser, the server no longer remembers the page anymore. In order for end users to be able to view web pages (or, in this case, HTML reports) interactively, some form of state management must occur. This can be done by ASP.NET's view state validation. While a full discussion of ASP.NET View State is beyond the scope of this tip, we will consider a few things to move forward (you can read more about ASP.NET State Management: View State from this article.) By default, view state validation in ASP.NET is enabled and uses automatically generated keys for both validation and encryption. For reporting services, view state validation uses the identity of the Report Server web service to perform the validation. However, since we are dealing with multiple machines in a NLB cluster, there will be multiple reporting services instances, hence, multiple Report Server web service identities to deal with. This would mean that we cannot simply rely on a single process identity to perform the view state validation. In order to workaround this situation, we can manually generate both the validation and encryption keys instead of relying on the autogenerated values provided by ASP.NET. We will use these key values across all of the nodes in the NLB cluster. This can be done by modifying the web.config files for both the Report Server as well as for the Report Manager. And while Microsoft KB article 312906 describes how you can create a .NET application that will generate these key values, I'm not about to open up Visual Studio just to write my own utility. Luckily, there are a lot of utilities out there that will do this for you. I've used this machineKey generator utility from aspnetresources.com to generate the key values for me.

Here is how to update the validation and encryption keys:

  1. Using any text editor, open the web.config file for the Report Manager. By default, this can be found in \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Web.config
  2. Look for the <system.web> section paste the <machineKey> element that you generated either from the machineKey generator utility from aspnetresources.com or your very own utility. The values I generated from the machineKey generator utility from aspnetresources.com is shown below and is pasted immediately after the <system.web> section just so I can easily find it later on should I need to do some troubleshooting.

    <machineKey
    validationKey="00A2CEAEF8A91B29F63399CBEE18F272159F114991EA7CF2FD78BC5D9BB0821825C7332C4A4C1698FA58E39634365A97DA8F720377B84F471A3A166CFCDD31DF"
    decryptionKey="009CA6A1D48DC4DB59E54865C470DFB75FBC1B73AA4833523C9795B1FA88CBE3"
    validation="SHA1" decryption="AES" />

    Report Manager web.config machinekey validationkey

  3. Save the Web.config file
  4. Repeat the previous steps for the Report Server web.config file. By default, this can be found in \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\web.config

    Report Server web.config machinekey validationkey

  5. Repeat steps #1 to #4 on all of the nodes in the NLB cluster
  6. Verify that the <machineKey> element in the <system.web> section of the web.config files for both the Report Manager and Report Server are identical across all of the nodes in the NLB cluster

Configuring Hostname and UrlRoot on all of the NLB Cluster Nodes

These configuration items can be found in the RSReportServer.config file. The UrlRoot property is used by the report server delivery extensions to compose URLs that are used by reports delivered in e-mail and/or file share subscriptions. This property has to be set correctly to avoid having incorrect links generated by the reports. Since we are dealing with multiple nodes in the NLB cluster, you wouldn't want end users to see the physical computer name where the subscriptions were generated. You also wouldn't want the end user to be directed to the physical server and bypassing the NLB cluster as this would affect their session state information. We need to configure the UrlRoot property so that the subscribed reports would display the virtual server name of the NLB cluster instead of the physical server names of the NLB cluster nodes. The Hostname property defines how the network traffic to the reporting environment is directed. By default, this property is not defined and will use the physical server name. As we've mentioned in the UrlRoot property, we want the network traffic to be directed to the virtual server name so that the NLB cluster will be the one to handle and distribute the requests among the cluster nodes.

Here is how to configure the Hostname and UrlRoot properties:

  1. Using any text editor, open the RSReportServer.config file. By default, this can be found in \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config.
  2. Look for the <Service> section, and add the following information to the configuration file. Use the Hostname value of the virtual server name of your NLB server. For this example, we'll use SSRS2008R2NLB.TESTDOMAIN.local. Similar to what I've done in the <machineKey> element in the web.config file, I pasted it immediately after the <Service> section just so I can easily find it later on should I need to do some troubleshooting.

    <Hostname>SSRS2008R2NLB.TESTDOMAIN.local</Hostname>

    rsreportserver.config hostname

  3. Look for the <UrlRoot> element. By default, this has no value but the default value used is in the format http:// or https://<physicalServerName>/<reportserver>, where <reportserver> is the virtual directory name of the Report Server Web service.
  4. Type a value for UrlRoot that includes the virtual server name in this format: http:// or https://<virtualServerName>/<reportserver>. For this example, we will use http://SSRS2008R2NLB.TESTDOMAIN.local/ReportServer

    <UrlRoot>http://SSRS2008R2NLB.TESTDOMAIN.local/ReportServer</UrlRoot>

    rsreportserver.config URLRoot

  5. Save the RSReportServer.config file
  6. Repeat steps #1 to #6 on all of the nodes in the NLB cluster
  7. Verify that the <Hostname> and <UrlRoot> elements in the <Service> section of the RSReportServer.config files are identical across all of the nodes in the NLB cluster

Workarounds for the HTTP 401 Error Message

At this point, you may you may be tempted to test access to the reporting services instance via the virtual server name. However, if you do so, you may get a HTTP 401 error similar to the one below.

The request failed with HTTP status 401: Unauthorized.

If you look closely, you will notice the user interface associated with SQL Server Reporting Services which tells us that the reporting services instance is working except that it encounters a HTTP 401 error. According to Microsoft KB article 896861, this behavior is by design if the fully qualified domain name or the custom host header does not match the local computer name, which is the case in a reporting services scale-out implementation. And while the Microsoft KB article describes the behavior for a web application running on IIS, it does apply to our scenario. You can implement any of the methods described in the KB article but Method 1 is the Microsoft recommended approach. You would need to reboot your server in order for the changes to take effect immediately as you do not have IIS on the NLB cluster nodes. Your BackConnectionHostNames key would look something like this for one of the nodes, considering to include both the hostnames and the fully qualified domain names for the physical computer name as well as the virtual server name.

SSRS2008R2A
SSRS2008R2NLB
SSRS2008R2A.TESTDOMAIN.local
SSRS2008R2NLB.TESTDOMAIN.local

Registry BackConnectionHostNames

Make sure you make this registry change across all of the nodes in your NLB cluster, making the necessary changes to the Value Data box to reflect the corresponding physical server name for each node. Once all of the nodes in the NLB cluster have been rebooted, you should now be able to access the reporting services instance using the virtual server name.

Access Reporting Services using the virtual server name

Congratulations! After going through all of the process outlined in the previous tips including this one, you now have a highly available, scale-out SQL Server 2008 R2 Reporting Services Farm running on a two-node Windows Network Load Balanced cluster.

Next Steps


Last Update:






About the author





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, October 31, 2016 - 11:01:11 AM - Francis Back To Top

@Darren

Your solutiuon to "Deleted the lines in the rsReportServer.CONFIG referring to HOSTNAME and URLROOT" worked! I also struggled with this for days without a solution. Thanks so much for gettting this to work!!

Francis


Monday, May 11, 2015 - 4:54:56 PM - bill Back To Top

 

Thanks for article! It help me to setup a SSRS farm. Recently I had to add another node to the farm. But I get the HTTP 401 error using the same instructions.  When I try to access the webportal directly using the htt://servername/report url outside the server, it failed to open.  The webportal open without issue within the server.  I did had to add entries in the hosts file. The other nodes are working just fine.. 

Any thoughts what may be causing the problem?


Wednesday, March 19, 2014 - 10:55:36 AM - Darren Back To Top

**RESOLVED**

1) MachineKey is def required in web.CONFIG files

2) Deleted all entries of BackConnectionHostNames in the registry on all nodes, and reboot

3) Deleted the lines in the rsReportServer.CONFIG referring to HOSTNAME and URLROOT as per article above - this appeared to be main culprit - weird, but true

All nodes now displaying NLB URL correctly, and load balancer is shifting connections across the nodes as it should.

Thanks for the help team

Darren

 

 


Tuesday, March 18, 2014 - 11:08:40 AM - Darren Back To Top

Thanks bass_player

Yip, opening the URL from another server outside the cluster also shows the 401 Unauthorized error

Thank you for the MS link - however I have already implemented this registry change to no effect. perhaps you can confirm if this reg entry should include the hostname and FQDN for EACH host in the cluster, or just the local Reporting Server? (I have tried both ways anyway, and it still fails)

I was experiencing some joy with the URL yesterday when powering all BUT ONE of the cluster nodes OFF, and then accessing the NLB URL, but today, it just fails everytime, whether one node is up, or all nodes are up - same 401 Unauthorized msg.

Have spent days on this, and viewed hundreds of web forums, and tried loads of suggested fixes - am running out of time. Will try roll back my most recent changes to things like the rsReportServer.CONFIG file, and MaxTokenSize reg key, and BackConnectionHostNames reg key to see if I have any joy

Any other suggestions would be appreciated

Thanks for reading

D

 


Tuesday, March 18, 2014 - 10:45:23 AM - bass_player Back To Top

Darren,

Have you tried accessing the Reporting Services load balanced URL outside of the cluster itself? You can use a workstation and access the Reporting Services load balanced URL via a browser. You can refer to this KB article for reference http://support.microsoft.com/kb/956158


Tuesday, March 18, 2014 - 8:02:57 AM - Darren Back To Top

Hi Edwin, thanks for the article. this explains exactly the scenario I am currently experiencing. Scale-Out Reporting Services farm (6x nodes), and currently receiving the Error 401: Unauthorized.

Unfortunately, I have been troubleshooting this issue for days now without success. I have run through all your suggested workarounds/solutions exactly as suggested and rebooted all the nodes, and the IIS server, but am still getting the 401 error when entering the NLB URL for the reports server.

BackConnectionHostNames = done

MachineKey = done

ReportServer URLroot and Hostnames = done

The load balancing is done by F5 network hardware provided by a managed service company. Pool members and sticky sessions are all configured correctly.

SSRS server nodes are SQL2012

Any further advice you can offer would be appreciated - I have 1 week left to fix this before my deadline!

Thanks

Darren


Tuesday, February 11, 2014 - 2:04:30 PM - bass_player Back To Top

These apply to SQL Server 2012 too. You will see the usual UI when you open the Reporting Services Configuration Manager tool. The only difference is that you are accessing it via the load balanced URL rather than the hostname


Tuesday, February 11, 2014 - 11:42:26 AM - Tarek Altouny Back To Top

Great articles .. Are these group of articles applicable to SQL Server 2012 too ??

I have a question .. after applying all these steps when I open the reporting services configuration manager what would I see ??

 

Thanks ..

 


Tuesday, November 12, 2013 - 6:34:46 AM - Stefan Boychev Back To Top

Hi Edwin,

Thanks for getting back on this. I tried adding all the hosts to BackConnectionsHostnames key in registry but that didn't worked. I also tried completely disabling the LoopbackCheck by creating a new key and setting value to 1, after that I rebooted the servers but that didn't work either. Adding hostnames to the hosts file did the trick for me.

May be other users can share their experience on this too.


Friday, November 08, 2013 - 10:41:38 AM - bass_player Back To Top

Thanks, Stefan, for the recommendation. The BackConnectionsHostnames registry key should resolve the 401.1 error, similar to the effect of adding the HOSTS file entries on all of the report servers


Friday, November 08, 2013 - 7:28:48 AM - Stefan Boychev Back To Top

I had the same issue - couldn't open the Reports web site using SQL 2012 Scale-out solution, just the ReportServer where reports are stored. I tried disabling the loopback check but that didn't help. I found the answer here - http://www.bigresource.com/MS_SQL-Scale-Out-Web-Farm-401-1-Unauthorized-gFyDYFGi.html


Sunday, December 30, 2012 - 3:45:39 PM - bass_player Back To Top

The SSPI context error message is caused by some authentication issues. There are a lot of resources pointing to the culprit, most of which are very specific to your environment. Check out this blog post to get you started on the troubleshooting.

http://www.pythian.com/news/28861/troubleshooting-sspi-handshake-error/

If you've managed to connect to the report server database prior to configuring the XML config files, it could be that this is an intermittent issue. You have to discuss this further with your domain administrator


Saturday, December 29, 2012 - 10:13:38 PM - staggerlee Back To Top

Hi thanks for the posts on this, seems theres a lot of articles about it but not many (that i could find anyone on how to do it!)

I had everything working as you said it would until i got to xml changes / regedits. Now when i try to open reportserver on either server i get the error: 

 

The report server cannot open a connection to the report serer database... 

Cannot generate SSPI context

 

Any ideas whats happened?!


Wednesday, November 21, 2012 - 3:53:27 PM - bass_player Back To Top

The first thing that you need to check is to make sure that you can access the SSRS URL of the individual nodes. It should give you a response with the SSRS user interface but with a HTTP 401 error, similar to the screenshot you see in this article. If you're not even getting that response, then, there must be something wrong with the installation. 


Wednesday, November 21, 2012 - 1:20:48 AM - spiralcb Back To Top

Hello,

I'm in the same case as a previous post.

We are implementing a scale-out setup using a hardware load balancer.

But I have the same error.

Is it possible to know if the personn who write the previous post find a solution ?

(Thursday, August 16, 2012 - 8:48:34 AM - Hi Edwin )

Thanks a lot


Saturday, August 18, 2012 - 9:22:54 PM - bass_player Back To Top

Great to hear that your issue is resolved. I forgot that you do need the BackConnectionHostNames hack. My apologies for the confusion? And thanks for pointing out the relevant Microsoft KB article. While the one I referenced works well, it doesn't say that it is for SSRS and it becomes confusing


Saturday, August 18, 2012 - 8:44:21 PM - Zhen Back To Top

Thanks, Edwin, for your reply. The BackConnectionHostNames trick actually solved my 401 error while I access the URL from outside of the NLB nodes. Here's what happend: 1) Before I added Hostnames and UrlRoot in the config file, accessing the url using NLB name from a laptop works fine. 2) After I added Hostnames and UrlRoot to the config file, I got 401 error accessing the url using NLB name from a laptop. 3) After I added BackConnectionHostNames, 401 error resolved. This artile http://support.microsoft.com/kb/956158 seems more relevant to SSRS.


Thursday, August 16, 2012 - 11:15:43 AM - bass_player Back To Top

Since you are running a hardware load balancer, can you access the URL of the SSRS running on one of the nodes without getting a 401 error? If not, start by troubleshooting that first. Have you tried rebooting the services after making the registry change?


Thursday, August 16, 2012 - 8:48:34 AM - Hi Edwin Back To Top

We are using a hard ware load balancer solution rather than NLB cluster.  We have implemented all the suggestions including the registry changes, i have also tried implementing the dissable loop back check.  We have an spn registered against the vitual server name of the load balanced address  in the form of http:// server name> .  Where the domain user account is the service account running the web services on all SSRS nodes.

 

WE are still getting the 401 erroe even after implementing all suggestions.

anyone seen this happening and have any suggestions?

thanks,  Matthew


Monday, August 13, 2012 - 1:59:19 AM - bass_player Back To Top

Zhen,

The BackConnectionHostNames trick is only used if you want to access the URL from within your NLB nodes. Otherwise, you don't need to. View State Validation is used to maintain state across web pages during requests. The reason why we configure this on the config files is because each NLB node is a different machine and would generate a different view state value. We can't afford that since a request might hit the NLB node 1 on first request while hitting the NLB node 2 on another request - all depending on how the load balancer is redirecting requests to the reporting services instance. Assigning the same validation key across all of the NLB nodes will guarantee the same view state value per request.


Thursday, August 09, 2012 - 2:48:45 AM - Zhen Back To Top

Edwin, sorry, just found out my email address has a typo. It's corrected this time.

Thanks. I am looking forward to your advice.


Thursday, August 09, 2012 - 2:45:57 AM - Zhen Back To Top

Edwin, very useful info from your articles. I am implementing a scale-out SSRS 2008 R2 on a NLB cluster with teo nodes. I have some questions regarding the last two steps 1) Configure View State Validation 2) Configure Hostname and UrlRoot.

Before I comeplete the two last steps, I can use NLB name in the URL to open Report Manager and Report Server on the web. I don't have 401 unauthorixed error. After I completed the two steps, Report server is still OK //nlbname.domain/reportServer, but Report manager //nlbname.domain/Reports generates the 401 error. I haven't tried the fixes you mentioned in your article to add BackConnectionHostNames.

I have the following questions before I try the fix.

1. Without adding the View State Validation, I already can run the Report Manager and Report Server on the web using NLB name. What is the difference after view state validaton is added? How can I test to show adding view state validation is a must for a scale-out deployment to work on a NLB cluster?

2. Why 401 error didn't occur before I added the hostname and UrlRoot but occured afterwards? Will the fix guarantee to solve the issue? The cause described by the KB article 896861 seems related to something else? If the fix can't solve the 401 error, what else can I do?

Thanks a lot for your advice.


Thursday, November 17, 2011 - 2:17:36 AM - bass_player Back To Top

Note that this is not a database load balancing option but rather the web application - in this case, SQL Server Reporting Services - being load balanced. If you want to implement a load balancing methodology for your databases, you might want to have a look at peer-to-peer replication. This, however, requires that your database objects are specifically designed for replication


Tuesday, November 15, 2011 - 10:02:21 PM - Sherlee Back To Top

Hi Edwin,

Nice topic something I need for my future projects.

I have a question, can I also use this tip if our DB admin uses load balancing in our servers?

 


Tuesday, November 15, 2011 - 8:30:07 AM - Tony Ash Back To Top

Good group of articles.

Tony


Learn more about SQL Server tools