We talk about CRM and xRM here.

Join in the conversation.

Getting Dynamics CRM and SQL / SSRS 2008 to Work Together (on Separate Servers)

Posted: March 17th, 2010 | Author: mike | Filed under: dynamics crm, howto | Tags: , , | No Comments »

So I have been working at some clients lately where they have a two server CRM / SQL + SSRS environment…

- Server 1 = CRM Application Server
- Server 2 = SQL Server 2008 + SSRS 2008 (in both client cases 64 bit ~ though don’t know if that matters)
Configuring / installing CRM and pointing to the SSRS server on Server 2 works and installs fine.  You can even view reports if you are RDC’ed onto the CRM App Server but from a regular workstation you cannot run any reports…
There is really no decent documentation out on the Internet (between MSFT Sites and blogs) that really describes what you need to do to get this to work…  Finally, I found a decent blog post that did walk through the steps and you know what???
IT WORKED!
So, rather than send you on the wild goose chase I’ve been on I figured I’d share with you what I had to do to get CRM + SSRS 2008 working together on separate servers.
First of all, this site helped me get through this -> http://sundium.wordpress.com/2009/10/05/troubleshooting-crm-data-connector/
- For my example, we initially had started with the CRM SSRS Connector installed on the CRM App Server (because we didn’t know any better to be honest)
- As I mentioned above, I was able to run reports from the CRM App server but going to another machine or workstation would give me an error which ended up being a 401 unauthorized when looking at the Event Log on the CRM App Server.
1.) I uninstalled SSRS Connector off of CRM App and verified that I could still run reports from that server (when RDC’ed onto it).  Research finally found that the SSRS connector should be on the server where SSRS is.
2.) I then got the latest .msp installer file for SSRS Connector for 64 bit (since our SQL / SSRS Server is 64 bit) -> http://support.microsoft.com/kb/948917/
3.) I saved this .msp to the CRM App Server (yes I put it there since the installconfig.xml file which we’re getting to a little later references this .MSP during install and it appears that you have to use a \\shared\path\to\file (don’t know if you could put in a drive letter path ~ C:\something)?
4.) I then copied the sample install-config.xml file from the CRM DVD (64 bit folder again, remember) and modified as shown below (highlighted in red indicates something you would have to change to recreate):
<crmsetup>
  <srsdataconnector>
    <Patch update="true">\\PATHTO\CRMAPPSERVER\SrsDataConnector_amd64.msp</Patch>
    <configdbserver>INSERTSQLSERVERNAME</configdbserver>
    <autoupdateconfigdb>1</autoupdateconfigdb>
    <reportserverurl>http://ssrsservername/ReportServer</reportserverurl>
    <autogroupmanagementoff>0</autogroupmanagementoff>
    <instancename>MSSQLSERVER</instancename> <!-- of course you would put in the actual instance if multi-instances -->
    <configsku>OnPremise</configsku>
    <!-- Set enabled = true for DB webstore integration.  Set configdb="true" for config db webstore integration-->
    <webstore enabled="false" configdb="false" />
    <monitoring>
      <!-- Monitoring service account name and password. It can not be local system or network service account -->
      <serviceaccountname></serviceaccountname>
      <serviceaccountpassword></serviceaccountpassword>
    </monitoring>
  </srsdataconnector>
</crmsetup>
5.) Finally, I ran the install (from the CRM Install Media) from the command line:
SetupSrsDataConnector.exe /Q /CONFIG c:\pathto\install-config.xml /L c:\pathto\log.txt
This is where things got kinda weird and I think the /Q flag did this (Quiet Mode?)..  The Command Prompt just immediately went to the next line as if nothing happened?
I then looked at the folder that I was dumping the log.txt file and noticed that the log kept growing and finally it stopped and the log read that it was done…
6.) I then stopped and restarted SSRS on the SQL / SSRS server
7.) I then did an IISRESET on the CRM App Server (might as well cross all T’s)
8.) I crossed my fingers and logged into another machine and was able to load the reports!
Hope this helps you all and serves as a reference moving forward…

Leave a Reply