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!

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

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.

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

Report Server Configuration Changes

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

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.

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.

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.

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.

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:

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.

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