Reporting Services Windows Service wouldn’t start

I created a new differencing disk of a CRM virtual machine today and I had some troubles getting Reporting services up and running. The service simply wouldn’t start. I had previously used the same virtual harddisk base for other systems so I found it a bit strange, it should work.

When I looked in the event viewer it showed the error “Report Server (MSSQLSERVER) cannot connect to the report server database.”.

I looked around a bit on the net and found a reference to the following which helped:

http://support.microsoft.com/kb/839803

I concernings increasing the timeouts of pipes in the system to 60 seconds instead of 30. This might be needed on VPC:s that are not so fast and might not get the service up and running fast enough.

Gustaf Westerlund
Microsoft Dynamics CRM Chief Architect

Logica
www.logica.com

Force update of Report

The project I am currently involved in uses Reporting Services reports that are shown as PDF:s as quotes that are sent to the customer. It is implemented by creating a custom report that takes the object id (like quote) which is called from an isv.config button and by using querystring parameters, the output is directed to PDF.

However, I got a puzzeling bug. The data in the quote is of course based on data in some CRM entities and when a quote was generated and the user found something was wrong, changed this in CRM, saved and then tried to regenerate the PDF, the change didn’t show. I restared Internet Explorer, and regenerated the quote-pdf and now it showed correctly. Hence some sort of caching error.

I looked around the internet a bit and found that by adding the following parameter to the querystring, I could refresh the sessions variables and I would get a fresh and correct report.´

&rs:ClearSession=true

So, the entire url was (except for the report id etc.):

&rs:Command=Render&rs:ClearSession=true&objid={D1F1FD5D-D7F8-DD11-967B-005056AA29F5}&rs:Format=PDF

So, if you get a similar error, just try setting this.

It is also possible that IE caches the pdf and then you’ll have to add some variable to the querystring, like a tick or datetime that is unique for every call (from that client) and that will bypass IE:s caching.

Gustaf Westerlund
Microsoft Dynamics CRM Architect

Logica
www.logica.com

How to get the URL to the reportserver programmatically

I was searching the net the other day to try to find a way to programmatically get the reporting server url from CRM. I thought that there must be some way to find it using the standard CRM webservice and I really looked throught the SDK and all the blogs I could find to see if anyone knew.

Finally I gave up and tried to find some other way to get it and I remembered that it is set in the registry so I wrote some code to get it from there and here it is in all it’s simple glory:

RegistryKey regkey = Registry.LocalMachine.OpenSubKey(@”SOFTWAREMicrosoftMSCRM”);
string reportserver = regkey.GetValue(“SQLRSServerURL”).ToString();

And it worked but only just since it uses the server name and my VPN connection to the customers environment didn’t bother with sending it to me why I have to manually add it to the hosts file.

The host file can be found at the path: C:WINDOWSsystem32driversetchosts

and I added the row:

192.168.0.100 CRMTESTSRV

Now my button which point to my aspx that redirects to a the report in pdf-format works just fine and is independant of the CRM server it is installed on.

The solution isn’t supported as far as I know since you never know if they might change the registry value in the future (not very likely in an update but might happen in an upgrade). If anyone has any supported way of getting this URL, please comment this post.

This could also be done using javascripts but I find it easier to manage server side code and it doesn’t add that much overhead considering the report has to be generated independantly of if I use aspx or javascript to open the report.

Gustaf Westerlund
Microsoft Dynamics CRM Architect

Logica
www.logica.com

Adding SQL Reporting Services reports to CRM

As you are probably aware, Microsoft CRM 3 and 4 uses SQL Reporting Services for creating reports. If you are familiar with this tool from non-MS CRM implementations, there are a few tricks for getting the reports to work correctly. Please read the CRM SDK and the report writers guide for specific details on parameter naming and some other stuff.

The part that is the major deviation from normal SQL RS work, is how to deploy reports. Even though normal deployment might seem to work, the report won’t be recognized by CRM properly.

So, what do you need to do?
1. Create a report that does NOT use a Shared Datasource. Instead create an embedded datasource called CRM that connects to the CRM database server. If you are going to use the Filtered Views, you have to use Windows Authentication. Save and build the new report (DO NOT deploy to the server).

2. Open CRM, Go to “Workplace” and select “Reports”. Click “New” in the list. You will see a form where you can select the rdl-file and also select where in CRM the report will be available. Select the rdl-file that resides in the VS Project folder. Press Save.

If, at this point, you had used a Shared Data Source, you would be getting a very complicated error, as described by Menno here: http://blogs.msdn.com/mscrmfreak/archive/2006/04/27/584595.aspx

If all went well, the report will be uploaded correctly.

When uploading the report, CRM will replace the datasource in the rdl with the standard shared datasource. So, your report will still be movable.

Also, when uploading reports like this, CRM will hide all parameters starting with “CRM_”. There are several parameters that CRM will fill with data for you if they exisit. For instance the parameter “CRM_URL” will be set to “http:///CRMReports/viewer/drillopen.aspx”. This enables the report to create drill-down functionality that loops back into CRM (since there is very good support for URL-addressability in CRM).

Please note that all deployment of reports to CRM should be done in this manner and never directly from Report Designer. This is a bit of a hazzle since it is a bit tedious compared to just deploying from the Report Designer.

In CRM 4 there is a very nice wizard in the application for generating reports, even though it is not nearly as advanced as the report designer.

Gustaf Westerlund
Microsoft Dynamics CRM Consultant

WM-Data/Logica CMG
www.logicacmg.com

Kerberos / Trust Delegation

As some of you might have experienced the easiest way to deploy MS CRM 3 is to have SQL Server and SQL RS on the same machine, this will let you duck from a lot of problems, so if you don’t know better and have no reason not to, choose this setup.

However, this is not always what is best. The most common scenario when this is not the case is when a company has a large SQL Cluster (or just a dedicated SQL Server) and for several reasons (mainly licensing and maintance) want to have the CRM database and CRM reports on the central SQL server. Now, I am not an expert at this, but I have tried to dive in to it a bit and I have found the following:

If you are running the application pool of CRM, application pool of SQL RS and the main SQL server as Network Service, you will usually be fine, it will probably work more or less out of the box.

However, if not, you will get a warning in the installation of CRM saying something like “SPN not set correctly” for SQL RS. You can, if you like, install CRM anyway, but reports will only work when running them directly using Report Manager/Report Server or when running IE from the CRM server. Since it is not best practice to have the CRM server double as a terminal server, this is not often very nice.

So, what’s wrong? The problem lies in that all requests from SQL RS to the SQL database, are done using the technique “impersonation” which means that all requests in the database are run as the “current user”/the user that is running IE. This is a very good feature since it among other things enables the Filtered Views in the SQL database the only returns the items that the current user has access to. The problem is that CRM is acting HTTP proxy for the SQL RS and as such cannot send the current users credentials to the Report Server.

What needs to be done? Well, the CRM server user must be set up to enable trust delegation (i.e. forwarding of credentials) and the service users running the CRM apppool, SQL RS app pool and SQL must have the correct SPN:s set for them.

There are several documents and blogs describing how this can be set up, Sonoma Partner has one and the document with the short title: Additional Tasks When Microsoft SQL Reporting Services is installed on a separate server from Microsoft CRM or Microsoft SQL Server by Microsoft is also a good help.

The biggest problem usually is that you will have to run the “setspn” tool on the service account that is running the SQL Cluster/dedicated SQL machine and even though that shouldn’t cause any problems, if it does, and you are running x number of business critical applications on the SQL server, you might be in for a long night…

My suggestion is that you first try to set this up in a demo environment and when you got it working, then redo it on the live environment on a late friday afternoon, and make sure you havn’t got anything planned for the weekend…

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se