by Gustaf Westerlund | Mar 1, 2010
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
by Gustaf Westerlund | Jun 5, 2007
I was working with setting up a demo/lab environment with SQL 2005, MOSS Ent 2007 and MS CRM 3, Office 2007 Ultimate.
However, I had problems getting the reports to work, when trying to access the reportpage, I got the error: “Reports.config has invalid schema”.
I looked around a bit on the net and found that one of the blogs I suibscribe to actually discussed the problem at hand.
http://blogs.msdn.com/mscrmfreak/archive/2006/05/17/599371.aspx
It seemed that the reason for the error was that .NET 2.0 had been defaultet for the CRM 3 website (and we all know it is .NET 1.1, right!). So, tried to run “aspnet_regiis -r” as described but I got the same error as the comment by “Matt Thomas“, my CRM just stopped working.
So, what to do. I found that when you run “aspnet_regiis -r” it resetts all websites to the version of .NET that you run it from (ex. C:WINDOWSMicrosoft.NETFrameworkv1.1.4322aspnet_regiis -r, will set all websites to use .NET 1.1). This is not desirable since my MOSS 2007, SQL 2005 RS and all MOSS admin sites (central admin, my site, SSP) all should run .NET 2.0. So, I thought it better to reset all sites to .NET 2.0 and then manually set the CRM site to 1.1. And so I did, and CRM just stalled when loading. No error message, no nothn’!
This got me thinking that you should be able to run one version of aspnet_regiis for just one website. So, by trying the all-usefull “/?” parameter I found that using “aspnet_regiis -lk” will list all different IIS-paths and show which version they were running. This was what I found:
C:WINDOWSMicrosoft.NETFrameworkv1.1.4322>aspnet_regiis -lk
W3SVC/ 2.0.50727.42W3SVC/1/ROOT/ 2.0.50727.42
W3SVC/1/ROOT/_layouts/images/ 2.0.50727.42
W3SVC/1/ROOT/_layouts/inc/ 2.0.50727.42
W3SVC/131316714/root/ 2.0.50727.42
W3SVC/131316714/root/Reports/ 2.0.50727.42
W3SVC/131316714/root/ReportServer/ 2.0.50727.42
W3SVC/1720207907/root/ 2.0.50727.42
W3SVC/2/ROOT/ 1.1.4322.0
W3SVC/2/ROOT/MSCRMServices/ 2.0.50727.42
W3SVC/2075257487/Root/ 2.0.50727.42
W3SVC/2075257487/Root/_layouts/images/ 2.0.50727.42
W3SVC/2075257487/Root/_layouts/inc/ 2.0.50727.42
W3SVC/425288717/Root/ 2.0.50727.42
W3SVC/425288717/Root/_layouts/images/ 2.0.50727.42
W3SVC/425288717/Root/_layouts/inc/ 2.0.50727.42
W3SVC/493651791/Root/ 2.0.50727.42
W3SVC/493651791/Root/_layouts/images/ 2.0.50727.42
W3SVC/493651791/Root/_layouts/inc/ 2.0.50727.42
Aha! Can you see what is wrong? Well, when I manually set CRM to .NET 1.1 it didn’t do that recursivly! The CRM webservice is running as a separate application, and I hadn’t changed that. This is the row that shows what’s wrong:
W3SVC/2/ROOT/MSCRMServices/ 2.0.50727.42
So, I checked what parameters you could use with aspnet_regiis and found that “-s ” seemed like a good idea. Hence, I ran:
aspnet_regiis -s W3SVC/2/ROOT
I think it might just have worked with going in to the CRM site and changing it for the MSCRMService virtual directory, but I thought it better to set it to the entire MS CRM 3 website recursivly to make sure everything was ok. When it had completed (takes a minute or two), I ran “aspnet_regiis -lk” again to check the current status:
W3SVC/ 2.0.50727.42W3SVC/1/ROOT/ 2.0.50727.42
W3SVC/1/ROOT/_layouts/images/ 2.0.50727.42
W3SVC/1/ROOT/_layouts/inc/ 2.0.50727.42
W3SVC/131316714/root/ 2.0.50727.42
W3SVC/131316714/root/Reports/ 2.0.50727.42
W3SVC/131316714/root/ReportServer/ 2.0.50727.42
W3SVC/1720207907/root/ 2.0.50727.42
W3SVC/2/ROOT/ 1.1.4322.0
W3SVC/2/ROOT/MSCRMServices/ 1.1.4322.0
W3SVC/2075257487/Root/ 2.0.50727.42
W3SVC/2075257487/Root/_layouts/images/ 2.0.50727.42
W3SVC/2075257487/Root/_layouts/inc/ 2.0.50727.42
W3SVC/425288717/Root/ 2.0.50727.42
W3SVC/425288717/Root/_layouts/images/ 2.0.50727.42
W3SVC/425288717/Root/_layouts/inc/ 2.0.50727.42
W3SVC/493651791/Root/ 2.0.50727.42
W3SVC/493651791/Root/_layouts/images/ 2.0.50727.42
W3SVC/493651791/Root/_layouts/inc/ 2.0.50727.42
As you can see, the MSCRMService is now also .NET 1.1.
After this I restarted the IIS with “iisreset”, and tried to run CRM and the reports;
Everything worked as it should!
Gustaf Westerlund
CRM and SharePoint Consultant
Humandata AB
www.humandata.se
by Gustaf Westerlund | Jun 5, 2007
Microsoft are planning to release a new version of SQL Server next year that will be called Microsoft SQL Server 2008.
It hosts lots of new features, for instance the ability to work directly with entities in the database instead of tables and views. An interesting feature, I wonder how good it will be.
SQL Reports will also be better and the integration with MS Office system (=SharePoint?) has be enhanced.
Have a look at Microsofts website for SQL Server 2008 for more information and some videos.
http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
Gustaf Westerlund
CRM and SharePoint Consultant
Humandata AB
www.humandata.se
by Gustaf Westerlund | May 30, 2007
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
Recent Comments