SSRS (2014) Load balancing woes

Symptoms

HTTPS to reporting services only working on prd-web01b, not on 01a.  Checked all  the config etc. and re imported certs from b to a.  SChannell errors were our only clue, although William found some of these on the other server too.  Even rebooted the server for good measure.

How we fixed it

This was the clue - https://support.microsoft.com/en-gb/help/956209/ssl-no-longer-works-after-you-remove-an-ssl-binding-from-sql-server-20

We added the cert into IIS (even though we are running in native mode) , removed it from IIS and rebound it to reporting services and everything now appears ok.

Explanation

Some months ago we upgraded all our certs – I think it was to 2056 bits or such like – as the old ones were becoming invalid.

My theory is on the a server we unbound the old cert – thus removing the crucial registry setting in the above link – and then bound in the new cert.

On the b server we probably just selected the new cert.

I’m not sure who did this work but since a good manager always takes the blame for their teams actions – it was probably me.  (Lesson – sooner we go to scripted deploy the better).

However this did not fix the problem!

2’nd problem

The ever clever mr***suggested looking at the logs – and I found them – FYI there in d:\ Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles on our servers.

Found the errors –

library!ReportServer_0-36!1050!09/05/2017-08:54:29:: e ERROR: Error rending control: System.Web.HttpException: Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

http://go.microsoft.com/fwlink/?LinkID=314055 ---> System.Web.UI.ViewStateException: Invalid viewstate.

                Client IP: *****

                Port: 59222

                User-Agent: Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko

                ViewState: /wEPDwUKMTEzMjExOTUxNA9kFgQCAQ8WAh4EbGFuZwUFZW4tR0JkAgMPZBYEAgQPZBYEAgEPFgIeBVZhbHVlZGQCAw9kFgJmD2QWAmYPFgIfAWRkAgUPFCsABQ8WCh4UU2hvd1Byb21wdEFyZWFCdXR0b25oHgxTY3JvbGxUYXJnZXRkHhNQcm9tcHRBcmVhQ29sbGFwc2VkZx4QVjFTdHlsZVNoZWV0TmFtZWQeDlJlbmRlcmluZ1N0YXRlCymRAU1pY3Jvc29mdC5SZXBvcnRpbmcuV2ViRm9ybXMuUmVwb3J0UmVuZGVyaW5nU3RhdGUsIFJlcG9ydGluZ1NlcnZpY2VzV2ViU2VydmVyLCBWZXJzaW9uPTEyLjAuMC4wLCBDd

..

How we fixed it

This was easy for a web farm person like me – when you have a .net application  in a web farm you need to add in encryption keys across the farm.

First I checked the instructions as I would have thought that SSRS might do this for me as you actually configure the thing for scale out but MS aren’t that bright – yes you do need to manually put in some keys (https://docs.microsoft.com/en-us/sql/reporting-services/report-server/configure-a-report-server-on-a-network-load-balancing-cluster).

So I generated some for each environment and did this.

Explanation

I always said the old load balancer was not load balancing.  The new ones (F5) are. 

Report execution log.

This is for SSRS 2005.

If you want to see history of report running the following query in the ReportServer database helps.

set transaction isolation level read uncommitted
go
select * from
ExecutionLog el
INNER JOIN Catalog cat
ON el.ReportID = cat.ItemID
where cat.Name like '%reportname%'

Report Viewer in Azure

Kept getting an issue that when deployed to azure could not find Microsoft.ReportViewer.Common v11.0.0.0 but when I tried adding to project then this files was not listed in the extensions.  Eventually I added the dll directly from the GAC (as a file) with copy local and bingo.

Do the same for Microsoft.ReportViewer.ProcessingObjectModel.

FYI – you can see these in C:\Windows\assembly but when you come to add in visual studio you need to browse the sub-directory given in the properties – MSIL.

Report viewer over Https

This morning we had a long time customer ring to say they could not export reports to excel.  We are using the .net report viewer.  We tested the report ourselves and found no issues.

The customer was getting “cannot download reportviewer.axd from …”

A quick google found this - http://forums.asp.net/p/1415187/3316287.aspx#3316287

I’ll repeat the instructions here – which worked a treat:

1. In Internet Explorer, select "Internet Options..." from the "Tools" menu.
2. Click on the "Advanced" tab.
3. Scroll down to the "Security" section.
4. Un-tick the "Do not save encrypted pages to disk" check box.
5. Click the "OK" button.

I simply read these over the phone and the problem went away.

One to watch out for.

She had recently received a new computer.

Securing Reporting Services Web Services.

 

Many web based applications use reporting services to facilitate report production. We have used them extensively in many of our internal and external applications. The main drivers for deployment have been:

• to produce PDF and other format documents
• they are part of SQL Server
• they have a very simple and effective deployment model

Although placing reporting services behind a second level of firewalls will secure it from external attack a mechanism is required to authenticate the client – otherwise internal users will be able to run any reports. To this end we would normally create a domain or local NT account and grant that account read access to the applications. These credentials can then be used to authenticate when using web services to execute reports.

However, when using the Microsoft ReportViewer web control it is not at first clear how to set the network credentials to call reporting services.

In order to set the credentials you must first create a class that implements the IReportServerCredentials interface as follows –

[Serializable]

    public class RsCredentials : IReportServerCredentials

    {

        #region IReportServerCredentials Members

 

        public bool GetFormsCredentials(out Cookie authCookie, out string userName, out string password, out string authority)

        {

            authCookie = new Cookie();

            userName = password = authority = null;

            return false;

        }

 

        public System.Security.Principal.WindowsIdentity ImpersonationUser

        {

            get { return null; }

        }

 

        public ICredentials NetworkCredentials

        {

            get

            {

                if (ConfigurationManager.AppSettings["rptUser"] != null)

                {

                    return new NetworkCredential(

                            ConfigurationManager.AppSettings["rptUser"],

                            ConfigurationManager.AppSettings["rptPassword"]);

                }

                else

                {

                    return CredentialCache.DefaultNetworkCredentials;

                }

            }

          

        }

 

        #endregion

    }

 

And set the report viewer report server to use these credentials.

 

m_rptViewer.ServerReport.ReportServerCredentials = new RsCredentials();

 

In this particular implementation we look for a username and password in the web.config or we use the current Network Credentials of the process.

The current network credentials can be used if you are running if your asp.net worker process is running with account credentials that have access to the reports. Most internet setups do not usually use or allow domain authentication in the web servers so in practice this mechanism is rarely used.