Maria's two cents

Building a script to restart REPORTING SERVICES automatically

2013-05-14T08:37:00Z

Sometimes it can be useful to restart Reporting Services automatically using a script to avoid the initialization time it takes to start Reporting Services the first time it is being accessed, or to control the recycle process of the web service.

The following article explains how Reporting Services performs the recycle operations (planned or unplanned):

Application Domains for Report Server Applications

http://msdn.microsoft.com/en-us/library/bb934330(v=sql.105).aspx

Additionally it could be possible that the recycle process happened when the memory pressure is high. More information in the following article:

Configuring Available Memory for Report Server Applications

http://technet.microsoft.com/en-us/library/ms159206(v=sql.105).aspx

IN SUMMARY:

Reporting Services performs a recycle of the Report Server service every 12 hours (by default) depending on the value RecycleTime in the configuration file.
It is not possible to know the time in which the next recycle is going to happen.

An alternative could be implementing a script that restarts Reporting Services (for example every night at the same time) and, once restarted, accesses the Report Server to refresh the cache.

BUILDING A SCRIPT TO RESTART REPORTING SERVICES:

A good approach would be to control the recycling process and avoid it from happening during the execution of your reports/subscriptions/scripts would be to create a script
in PowerShell that:

Restarts the Reporting Services Service
Then wakes up the Report Manager to avoid the extra time it takes to load the page the first time is used.

The script could look like this (in PowerShell):

Stop-Service "SQL Server Reporting
Services (MSSQLSERVER)"

Start-Service "SQL Server Reporting
Services (MSSQLSERVER)"

$wc = New-Object system.net.webClient

$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials

$wc.Credentials = $cred

$src = $wc.DownloadString("http://localhost/Reports/Pages/Folder.aspx")

Note: You need to substitute localhost by the name of your server

You will run this script at the same time every day.

To automate the PS script to run periodically we can use the follow command: schtasks

You can find more Information at: http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/schtasks.mspx?mfr=true

Or simply create a job in SQL Server to run the script.

PERMISSIONS:

In the following link you can see the necessary permissions by the account that runs the rs script: (http://msdn.microsoft.com/en-us/library/ms162839(v=sql.105).aspx)

I hope it helps,

Maria Esteban

Reporting Services Support Engineer

Error 401 accessing the Report Manager in an environment configured in Scale Out

2013-01-23T14:09:52Z

When using a load balancer with several nodes in Reporting Services, you might notice that the Report Server (http://<machinename>/Reportserver)
is accessible but Report Manager (http://<machinename>/Reports) produces the following error:

The request failed with HTTP status 401: Unauthorized.

1. First of all you need to make sure that you configured the scale out of Reporting Services correctly by following the indications from the article below:

Configuring a Report Server Scale-Out Deployment

http://msdn.microsoft.com/en-us/library/ms156453(v=SQL.90).aspx

2. After that, if you have problems accessing the Report Manager locally, you can try disabling the loopbackcheck (steps in the link: http://support.microsoft.com/kb/896861)

3. If you still have problems accessing the Report Manager remotely try modifying the <ReportServerUrl> in the RSReportServer.config file with the url of the reportserver of the node.

The URL of the report server site for which the Report Manager tool searches is specified in the ReportServerUrl tag in the file RSWebApplication.config file. By default, if you leave this tag blank, the Report Manager tool uses "Localhost" as the server URL.

Maria Esteban

Reporting Services Support Engineer

Configuring Reporting Services to send email subscriptions outside the organization

2013-01-04T11:17:47Z

It is possible to send a email subscriptions in Reporting Services to recipients that are outside the domain (on the internet). You would need to setup the following configurations:

First of all, you might receive the following error when the e-mail subscription is running:

ERROR 1:

Subscription Error: "The e-mail address of one or more recipients is not valid."

This error usually occurs when the SMTP server is not configured for "email relay". SMTP relay between the Reporting Services server and the SMTP server should be enabled.

Ones the replay has being configured, you might receive the following error:

ERROR 2:

Failure sending mail: The server rejected the sender address. The server response was: 550 5.7.1 Client does not have permissions to send as this sender

There are two more settings to configure apart from the Relay in the SMTP server:

With authentication:

Configure the option <SMTPAuthenticate> to 2 in the rsreportserver.config
file to specify that the report server connects to the remote SMTP server in an
authenticated way. More info:

Configuring a Report Server for E-Mail Delivery

http://msdn.microsoft.com/en-gb/library/ms159155(v=sql.105).aspx

2. If you want to configure the relay authenticated, you need to give “Send As” permissions Reporting Services account in the SMTP server. “Send As” allows one user to send an email as though it came from another user. More info:

How to Manually Grant Send As Permissions to a User with Full Mailbox Access

http://technet.microsoft.com/en-us/library/bb125118(EXCHG.65).aspx

Anonymous:

Configure the option <SMTPAuthenticate> to 0. More info:

Configuring a Report Server for E-Mail Delivery

http://msdn.microsoft.com/en-gb/library/ms159155(v=sql.105).aspx

2. Create a receive connector to accept the anonymous relay and add the IP of the Reporting Services machine to the list of IPs of the connector.

Now you should be able to send emails outside the organization without errors

Maria Esteban

Reporting Services Support Engineer

Reporting Services service doesn't start after the installation of MS12-070 security patch

2012-11-12T12:48:00Z

If you are using Reporting Services and Integration Services and you noticed that the service couldn't start after the installation of the MS12-070 security patch (http://support.microsoft.com/kb/2716429) you can use the following 2 workarounds below.

(the issue already appeared in the past and was solved by http://support.microsoft.com/kb/936707/en-us)

Why is this happening?

When a .NET application startup the .NET Framework it tries to check if the Microsoft assemblies are right and have not been modified, this is done connecting to a server that has a revocation list in internet. When the framework tries to connect to the server to check if it has access to internet with an elevated timeout, this makes that any other .NET application that has less timeout setting fails (as SSIS or SSRS) with the error message you are getting.

The error message you are getting when you try to start SSRS:

"The request failed or the service did not respond in a timely fashion."

Means that the SSRS timeout is lower than the timeout of the connection to the revoke list server. Some recent windows updates have introduced this issue after you installed SP2 of SSRS 2008 R2. We are aware of this problem and we have already provided several workarounds.

This issue affects to all applications that runs on .NET framework 2.0 and there are several workarounds for this problem:

1st workaround:

Modify the registry to increase the default time-out value for the service control manager. To increase this value to 60 seconds, follow these steps:

1. Click Start, click Run, type regedit,and then click OK.

2. Locate and then click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control.

3. In the right pane, locate the ServicesPipeTimeout entry.

Note If the ServicesPipeTimeout entry does not exist, you must créate it. To do this, follow these steps:

On the Edit menu, point to New, and then click DWORD Value.
Type ServicesPipeTimeout, and then press ENTER.
Right-click ServicesPipeTimeout, and then click Modify.
Click Decimal, type 60000, and then click OK.
This value represents the time in milliseconds befor

Restart the computer for the changes to take effect.

Check that SSRS and SSIS services can be started now...

2nd workaround:

If the first action plan didn't work, try disabled the certificate checking in the ReportingServicesService.exe.config file:

Edit the ReportingServicesService.exe.config file usually located in this folder: C:\Program Files\Microsoft SQL Server\<RS Instance>\Reporting Services\ReportServer\bin
Add the "<generatePublisherEvidence enabled="false"/>" within the <Runtime> tag
Patch the server with the KB.

If you want to distribute this workaround to many users you can créate a script in PowerShell

3rd workaround:

Another option would be disabling the global checking of the certificated in the machine following the steps below:

o   Start -> Control panel
o   Internet Options
o   Advanced
o   Uncheck "Check for publisher’s certificate revocation"

If the services are running under the Network Service account the change to the revocation in internet options is not working for them. In that case, you can perform the following changes for the SSRS service account:

Modify the following setting in the registry:

HKEY_USERS\<SID>\Software\Microsoft\Windows\CurrentVersion\WinTrust\TrustProviders\Software

Where the default value for State key is 0x00023c00

And you will need to change it to 0x00023e00

(More information in: http://support.microsoft.com/kb/2715304)

In order to get the Windows SID for this user and be able to find out the previous registry key, please follow the below instructions.

Download SPGETSID tool from http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx
Once it is installed and save to a local file, open a cmd window (with administrator privileges ) and navigate through the cmd to the folder where this spgetsid.exe is located.
Then type the following command line: spgetsid domain\user (where the domain\user is your SSRS service account)
Once get the SID please go to the previous registry key for this user and apply the change.

S-1-5-20 is the SID for Network Service, and for other SIDs: http://support.microsoft.com/kb/243330

SID: S-1-5-18

Name: Local System

Description: A service account that is used by the operating system.
SID: S-1-5-19

Name: NT Authority

Description: Local Service
SID: S-1-5-20

Name: NT Authority

Description: Network Service

Note: Check workarronds in http://support.microsoft.com/kb/2745448 too

Maria Esteban

Reporting Services Support Engineer

The HTTP service located at http://MACHINE:32843/a49407bfb2c3432e9953dc831d8b0a0f/ReportingWebService.svc is too busy

2012-10-30T18:05:42Z

Playing around with the SharePoint integration with the new Addin of Reporting Services 2012 I suddenly found myself with the following error while running a report that used to work perfectly before:

Error

The HTTP
service located at http://MACHINE:32843/a49407bfb2c3432e9953dc831d8b0a0f/ReportingWebService.svc
is too busy.

The HTTP
service located at http:// MACHINE:32843/a49407bfb2c3432e9953dc831d8b0a0f/ReportingWebService.svc
is too busy.

The remote
server returned an error: (503) Server Unavailable.

I know the password of my account had changed by after correcting it in the application pools in IIS and in Central Administration under managed accounts, the error persisted and I couldn’t get it to work again.

I finally fixed it by re-provision the Reporting Services Service Application.

I am sharing the steps I used in PowerShell:

First of all you need to open the SharePoint Management Shell to be able to run cmdlets:

(From) http://msdn.microsoft.com/en-us/library/gg492249.aspx

Type the following command from the SharePoint 2010 Management Shell:
Click the Start button
Click the Microsoft SharePoint 2010 Products group
Click the SharePoint 2010 Management Shell.

These are the commands run in SharePoint Management Shell console:

1. To Get all the Reporting Services service applications run the following command:

Get-SPRSServiceApplication

In my case, I only had one that corresponded to the ID of the error, although you could have several

2. Then run the two commands:

$sts = Get-SPRSServiceApplication

$sts.Provision()

And the report started working again!!!

Maria Esteban

Reporting Services Support Engineer

"Arithmetic operation resulted in an overflow" Error running a report that uses a connection data source to Oracle 10

2012-07-05T12:36:08Z

If you are using Reporting Services to create reports that connects to your Oracle 10 server, you might find yourself with the following error:

"Arithmetic operation resulted in an overflow"

The StackTrace would be the following:

System.Data.Odbc.OdbcStatementHandle.RowCount(System.Data.Odbc.SQLLEN ByRef)
System.Data.Odbc.OdbcDataReader.GetRowCount()
System.Data.Odbc.OdbcDataReader.FirstResult()
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(System.Data.CommandBehavior,System.String, Boolean, System.Object[], SQL_API)
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(System.Data.CommandBehavior,System.String, Boolean)
System.Data.Odbc.OdbcCommand.ExecuteReader(System.Data.CommandBehavior)
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(System.Data.CommandBehavior)
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(System.Data.CommandBehavior)
Microsoft.ReportingServices.DataExtensions.CommandWrapper.ExecuteReader(Microsoft.ReportingServices.DataProcessing.CommandBehavior)
Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQuery()

After struggling to find the cause of this we found that it was caused by a bug in the Oracle ODBC driver. It affects the versions: 10.2.0.4 to 11.2.0.1 of Oracle and only the 64 bit driver (not the 32 bit driver).
This is why your 64 bit server might be affected but your 32 bit machine would not.

The corresponding bug in Oracle that causes this behavior is:

Bug 9793452 - EXECUTEREADER FAILS WITH OVERFLOW: SQLROWCOUNT RETURNS INCORRECT VALUE

You would need to install the following Oracle patches

For 10.2.0.4.0, apply Patch 10084982 or later.

For 10.2.0.5.0, apply Patch 10155844 or later.

For 11.2.0.1.0, apply Patch 9966926 or later.

Fixed also in 11.2.0.2.0 Patch 10098816

References

BUG:9793452 - EXECUTEREADER FAILS WITH OVERFLOW: SQLROWCOUNT RETURNS INCORRECT VALUE

More generic information about hte bug:

Retrieving data using a .NET ODBC application may fail with: 'Arithmetic operation resulted in an overflow'. This can occur with a call to ExecuteReader on the OdbcCommand object, the fill method of the OdbcDataAdaptor object and others.

Maria Esteban

Reporting Services Support Engineer

How can I check the version of the Add-In of Reporting Services for SharePoint I have installed?

2012-06-13T10:11:01Z

Where is my Add-in of Reporting Services for SharePoint? I need to find out which version I installed. Did I install the riht version or the Service Pack?

You can check this in a very easily in the installed programs list:

Open:
Control Panel / Programs And Features
Select
“Microsoft SQL Server 2012 RS Add in for SharePoint”

You can see the version at the bottom. See bitmap below:

Maria Esteban

Reporting Services Support Engineer

Reporting Services 2012 Inter-Version Supportability

2012-04-25T16:05:00Z

Do you want to install/upgrade to the new version of Reporting Services 2012?

You can see below the different combinations that are compatible with this new version concerning databases, BIDS, Report Viewer, SharePoint and PowerPivot.

It is mostly compatible with prior versions, incompatibilities revolve around loss of support for SQL Server 2000, BIDS 2000 and Report Builder 1.0

RS databases, BIDS & Report Viewer

Create ReportServer database on SQL 2000	Not supported
Create ReportServer database on SQL 2005 and later	Supported
Upgrade ReportServer 2000 database on SQL 2000	Not supported
Upgrade ReportServer 2000 database on SQL 2005	Supported
Upgrade ReportServer 2005 and later database on SQL 2005 and later	Supported
Deploy from BIDS 2000 to Reporting Services 2012	Unsupported
Deploy from BIDS 2005 and later to Reporting Services 2012	Supported
Deploy Report Model projects to Reporting Services 2012	Not supported
Upgrade BIDS 2000 and later projects in BIDS 2012	Supported
Upgrade Report Model Projects in BIDS 2012	Not supported
Use ReportViewer 8.0 and later against Reporting Services 2012	Supported
Use ReportViewer 11.0 against Reporting Services 2000 or 2005	Not supported
Use ReportViewer 11.0 against Reporting Services 2008 and later	Supported

Reporting Services, SharePoint & Add-in, Data Alert and Power View reporting features

SharePoint Version	Report Server Add-in	Report Server Back-end	Power View and Alerting Available
Windows SharePoint Services 3.0	2005	2005	No
Microsoft Office SharePoint Services 2007	2005	2005	No
Windows SharePoint Services 3.0	2008	2008 or 2008 R2	No
Microsoft Office SharePoint Services 2007	2008	2008 or 2008 R2	No
SharePoint Foundation 2010	2008 R2 or 2012	2008 SP2 or 2008 R2	No
SharePoint Foundation 2010	2012	2012	No
SharePoint Server 2010 Standard Edition	2008 R2 or 2012	2008 SP2 or 2008 R2	No
SharePoint Server 2010 Standard Edition	2012	2012	No
SharePoint Server 2010 Enterprise Edition	2008 R2 or 2012	2008 SP2 or 2008 R2	No
SharePoint Server 2010 Enterprise Edition	2012	2012	Yes ¹

SharePoint, databases, PowerPivot

SharePoint Version	SQL 2012Catalog	PowerPivot
SharePoint Foundation 2010	No	No
SharePoint Server 2010 Standard Edition	No	No
SharePoint Server 2010 Enterprise Edition	No	No
SharePoint Foundation 2010 Service Pack 1	Yes ²	No
SharePoint Server 2010 Standard Edition Service Pack 1	Yes ²	No
SharePoint Server 2010 Enterprise Edition Service Pack 1	Yes ²	Yes ³

Note:

Power View and Data Alerts require the new Business Intelligence Edition or the Enterprise Edition of both SQL Server Reporting Services and SharePoint Server 2010. These features are also available, for testing purposes, in Developer and Evaluation editions.
If you want to use PowerPivot 2012and/or SQL Server 2012 for the SharePoint Catalog database, then you must install Service Pack 1 for SharePoint 2010. These features do not work with the RTM version of SharePoint 2010.
PowerPivot only comes with the Enterprise Edition of SQL Server and requires the Enterprise Edition of
SharePoint Server 2010 (SP1).

Note:

The BI Edition contains all the Enterprise features of Reporting Services and Analysis Services, including Data-driven Subscriptions, Data Alerts, Power
View, and PowerPivot. It comes with the Standard Edition equivalent of the SQL Server database engine, though.

Improving performance of reports (for Reporting Services 2008 & 2008 R2) integrated with SharePoint 2010 by using the SQL Server 2012 Add-in

2012-04-23T16:18:08Z

I discovered short time ago a simple way to improve performance of certain Reports (RS 2008 & 2008 R2) running in integration with SharePoint 2010.

Imagine the following scenario:

You are using SQL Server Add-in 2008 or 2008 R2 to integrate Reporting Services with SharePoint 2010. You observe that there is a Big different on the time shown in the execution of the report in the ExecutionLog and the real time the report takes from SharePoint.

In the following link you can see the supported combinations of SharePoint and Reporting Services Components:

Supported Combinations of SharePoint and Reporting Services Components

http://msdn.microsoft.com/en-us/library/gg492257.aspx

You can observe that:

Report server	Add-in	SharePoint version
SQL Server 2012	SQL Server 2012	SharePoint 2010
SQL Server 2008 R2	SQL Server 2012	SharePoint 2010
SQL Server 2008	SQL Server 2012	SharePoint 2010

SQL Server 2012 Add-in can be used for the versions RS Server 2008 and RS 2008 R2 as well as for RS 2012 when integrating with SharePoint 2010.

I suggest you to try using the SQL Server 2012 Reporting Services Add-in for SharePoint 2010 as it includes several improvements and it is compatible with RS 2008 and 2008 R2. You might get surprised and see that some reports might speed up. (Needles to say, always using a test environment first).

You can download it from:

Microsoft® SQL Server® 2012
Reporting Services Add-in for Microsoft® SharePoint® Technologies 2010

http://www.microsoft.com/download/en/details.aspx?id=29068

Maria Esteban

Reporting Services Support Engineer

ERROR: Execution '' cannot be found (rsExecutionNotFound)

2012-03-21T09:53:00Z

I would like to share with you usefull information to troubleshoot the following error in Reporting Serices native or integrated with SharePoint mode, in case you find it in your environment. Sometimes, the error can be random, and sometimes, caused by the use of an NLB.

Execution '<ID>' cannot be found (rsExecutionNotFound)

TROUBLESHOOTING:

1. First of all, check the following article, in case you have Exchange 2007 installed and it is causing it:

Error message when you view a report from Report Manager, ReportViewer, or a SharePoint site: "Execution '<SessionID>' cannot be found (rsExecutionNotFound)"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;972328

2. The cause of this error could be that the credentials of the user do not get correctly impersonated across to Reporting Services for one of the web method calls. This causes the stored procedure 'GetSessionData' to return no data since the user account that created the session is not the one being passed in.

This may occur on 64bit systems. And it reproduces when the account that runs the report is the not the same as the application pool identity.
Try changing the following stored procedure: 'GetSessionData' like this:

--***** BEGIN
UNSUPPORTED WORKAROUND *****

IF (@OwnerName
= 'NT
AUHTORITY\NETWORKSERVICE') BEGIN

SELECT

@OwnerID = SE.OwnerID

FROM

[ReportServerTempDB].dbo.SessionData AS SE WITH (XLOCK)

WHERE

SE.SessionID
= @SessionID

END ELSE BEGIN

EXEC
GetUserID @OwnerSid,
@OwnerName, @AuthType,
@OwnerID OUTPUT

END

--***** END
UNSUPPORTED WORKAROUND *****

-- Previous
value for this section

-- EXEC
GetUserID @OwnerSid, @OwnerName, @AuthType, @OwnerID OUTPUT

And see if the problem disappears.

If the problem doesn’t disappears, revert back the changes

3. If Reporting Services is installed in an NLB. Check if the configuration of the Network load Balancer and the Session affinity of the load balancer are correct. See below:

How to: Configure a Report Server on a Network Load Balancing Cluster

http://technet.microsoft.com/en-us/library/cc281307(v=sql.100).aspx

4. Chacek the configuration of the <machinekey> tag in the configuration files of the load balancer as explained in the following article:

Configure a Report Server on a Network Load Balancing Cluster

http://msdn.microsoft.com/en-us/library/cc281307(v=SQL.100).aspx#ViewState

How to Configure View State Validation

---------------------------------------------------------

To run a scale-out deployment on an NLB cluster, you must configure view state validation so that users can view interactive HTML reports. You must do this for the report server and for Report Manager.

View state validation is controlled by the ASP.NET.
By default, view state validation is enabled and uses the identity of the Web service to perform the validation. However, in an NLB cluster scenario, there are multiple service instances and web service identities that run on different computers. Because the service identity varies for each node, you cannot rely on a single process identity to perform the validation.

To work around this issue, you can generate an arbitrary validation key to support view state validation, and then manually configure each report server node to use the same key. You can use any randomly generated hexadecimal sequence. The validation algorithm (such as SHA1) determines how long the hexadecimal sequence must be.

Generate a validation key and decryption key by using the autogenerate functionality provided by the .NET Framework.

You can also use this tool to generate validation key: http://aspnetresources.com/tools/machineKey

5. Configure session affinity in the DNS of the load balancer:

If you are using a sticky load balancer for the web farm, this requirement is not necessary. When the user connects to the balancer, it ensures that the user is always redirected to the same server during the same session. In other words, "sticky session" (or also called "session affinity") leads to that the user should be allocated to a particular server and that of its data is stored on the same server for the duration of its session.

On the following
link you have more information on what you meant by the term "sticky"
under the section "persistence"

(http://en.wikipedia.org/wiki/Load_balancing_(computing)

6. Check that the value of the property EnableAuthPersistance from the RSReportServer.config file is set to False

Additional information about this property:

RSReportServer Configuration File

http://msdn.microsoft.com/en-us/library/ms157273.aspx

EnableAuthPersistance

Determines whether authentication is performed on the connection or for every request.

Valid values are True (default) or False. If set to True, subsequent requests from the same connection assume the impersonation context of the first request.

This value must be set to False if you are using proxy server software (such as ISA Server) to access the report server. Using a proxy server allows a single connection from the proxy server to be used by multiple users. For this scenario, you should disable authentication persistence so that each user request can be authenticated separately. If you do not set EnableAuthPersistance to False, all users will connect using the impersonation context of the first request.

7. If you are using Reporting Services 2005, install Service Pack 4

Microsoft SQL Server 2005 Service Pack 4 RTM

http://www.microsoft.com/download/en/details.aspx?id=7218

I hope this helps,

Maria Esteban

Reporting Services Support Engineer