Archive for the ‘SSRS’ Category

Scenario: SSRS, in SharePoint integrated mode, is calling a custom web service located in the SharePoint ISAPI folder access through _vti_bin.  This service is using LINQ 2 SharePoint to query multiple lists in the SharePoint WFE.

A layout of the set up can be seen below. Okay, network diagrams is not my strong point! 

image

Because the authentication is being passed over more that 2 boundaries it looses the user and passes null and the only way to fix this is to use Kerberos.

So here’s how I went about setting it up:

Service Principal Names (SPN) for Service Accounts   

In order pass the Kerberos token you need to set up SPN’s. 

Note: Although I’ve not found confirmation of this SPN’s appear to be case sensitive

setspn.exe -A HTTP/<SSRS_FQDN> SSRSService
setspn.exe –A HTTP/<SSRS_NetBIOSName> SSRSService

Set the <SSRS_FQDN> to the FQDN of the server hosting the SharePoint Integrated SSRS and the <SSRS_NetBIOSName> as the Server name.

These entries can be confirmed by running

setspn.exe –L SSRSService

This should give an output similar to below.

image

The onto the WFE’s

setspn.exe -A HTTP/<SP_WFE_FQDN> SPService
setspn.exe -A HTTP/<SP_WFE_NetBIOSName> SPService

Set the <SP_WFE_FQDN> to either: the name of the server hosting the Sharepoint WFE or if this is an NLB cluster use the cluster name and the same goes for the <SP_WFE_NetBIOSName>

setspn.exe -A MSSQLSvc/<SQL_FQDN>:1433 SqlDbService
setspn.exe -A MSSQLSvc/<SQL_NetBIOSName>:1433 SqlDbService

Set the <SQL_FQDN> to either: the name of the server hosting SQL or if this is a cluster use the cluster name and the same goes for the <SQL_NetBIOSName>

Active Directory Users and Computers

Next, Open Active Directory Users and Computers and change each of the 3 accounts, selecting the Trust this user for delegation to any service (Kerberos Only) option on the delegation tab. 

image

SSRS App Server Changes

On the SharePoint Application Server which is hosting SSRS Open the Local Security Policy and Go to User Management Rights. Change to  “Act as a part of Operating System and “Impersonate a client after authentication” to include the users for both the WFE’s App Pool and SSRS Service Account

image

Report Server Configuration Changes

Open the RsReportServer.config file and locate the <AuthenticationTypes> section. Add <RSWindowsNegotiate/> as the first entry in this section.

Central Admin Changes for Web Application

Next, open Central Admin and Navigate to Application Management –> Manage Web Application

image

Then, select the application, then the authentication providers

image

In the pop-up click on the “Default” link in the Edit Authentication window scroll down to IIS Authentication Settings and choose Negotiate.

image

Scroll Down and Save.

Give this a minute to propagate to the other Servers in the farm and you should now be able to access the Reports which call the web service. 

Came across the above error on all the reports served through SharePoint in integrated mode. Not sure why this happen, but you seem to be able to resolve it by restarting the reports service.

Go to the Reporting Services Configuration Manager ( seen in figure below), connect to the server, then stop and start the service

image

I received the above message on some SSRS reports when the reports were using a shared data source and accessing a custom built, SharePoint, web service, in the ISAPI folder, . However, the following error left me very confused when some of the web methods were returning the data okay and the rest were returning this error.

Error while reading xml response.
DTD is prohibited in this XML document.

image

To try and figure out what was going on I built a window forms test harness to allow me to test the service in isolation of SSRS and to measure the time taken for each call.  My findings were that the response error was not caused by the the XML returning from the service but, by a redirection to a error.aspx page on timeout. Firing up Fiddler also confirmed this as can be seen in the html below.

<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="http://sharepoint/hr/_layouts/error.aspx?ErrorText=Request%20timed%20out%2E">here</a>.</h2>
</body></html>

The other purpose of the windows form was to measured the time before the failure.  My findings were that this was approximate 2 minutes.

After some more Google-ing about I discovered the problem:

The time-out of a .NET web service is 110 seconds by default. To fix this I simply had to increase the executionTimeout element of the httpRuntime Element (ASP.NET Settings Schema) in the web.config on all of the SharePoint WFE’s of the port I was accessing the _vti_bin/Service.asmx through.

Replace this:

<httpRuntime maxRequestLength="51200"/>

with this:

<httpRuntime maxRequestLength="51200" executionTimeout="600"/>

After increasing the timeout on all 3 WFE’s and hey presto, my data returned.

It took a while but, as always, the solution was staring me in the face. I hope this blog post helps someone avoid the grief I had figuring it out.

PS I also change the data-retrieval-services-timeout –propertyvalue but it never changed anything that i seen. I case you need reference to it use:

stsadm.exe -o setproperty -propertyname data-retrieval-services-timeout –propertyvalue 600

While deploying reports to SSRS  through SharePoint, in integrated mode I received the following error ,seen in the screen shot below:

An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset StaffByDepartments. (rsErrorReadingNextDataRow)
Exception from HRESULT: 0x80131904

An error has occurred during report processing. (rsProcessingAborted)

After Google-ing about, and finding no real answer I decided to recreate the data source as I had read that it might be the problem.

After creating a carbon copy of the data source and reassigning the DataSets to it, I redeployed….

It worked! It must have got corrupt in the content database somewhere, but it works now!!! Smile

After spending quite some time trawling the internet to find that answer to the above how to replace the $ dollar sign with a £ pound sign. I found that none of the solutions fully described how to do this.  Admittedly, it might just be me that’s thick though. Anyway, here’s how you go about it.

Firstly, to get your value into currency, you need to wrap your report value in the FormatCurrency function  in the expression box, as can be seen below.

FormatCurrency function in the Expression box

Next, is the part I had difficulty with! Every solution I found told me to set the default language of the report to United Kingdom. However, none of them told me explicitly how to do this.

So, what you need to do is: Changing the Language of a SQL Report

  • Click on the design surface of the report, outwith the actual reports.
  • Now, you will have access to the properties of the report where you can change the language as can be seen in the screen shot opposite.
  • Choose en-GB, deploy and that should be it fixed.

 

Easy when you know how, but, when your not from a Reporting background,  trying to access the properties caused me some confusion. 

Hopefully this helps someone else.

Problem:

When using SQL Server Reporting Service SSRS in integration mode with MOSS you come across the following error when trying to deploy reports using BIDS to a Document Library:  “The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel”

This problem is cause by the fact that on a default install of SSRS 2008 R2 in SharePoint integration mode the SecureConnectionLevel element of the RSReportDesigner Configuration file is set to a value of 2.

 

Solution:

For the current configuration to work you need a SSL certificate. However,  if your in an intranet environment, as we were, you can get your reports deployed by setting the value to “0” and try again

<Add Key="SecureConnectionLevel" Value="0" />

Okay, I can’t claim full credit for this find, but I’m sure my colleague won’t mind me blogging this to save trawling Google again!