locked
Microsoft SQL Server 2005 and CRM 4 upgrade RRS feed

  • Question

  • Hi All,

    I have hit a brick wall when trying to upgrade Microsoft CRM from version 3.0 to 4.0.  In a nutshell the original setup was:

    - A SQL Server 2000 installation containing the MSCRM and METABASE tables

    - A CRM Sever, which also has the reporting services on

    When I came to upgrade I used the following path:

    - Upgraded to SQL Server 2005 and installed SP3.  At this stage I created a new reportserver and reporting database, which was compatible with the new version of SQL (this was done because the existing reportserver had problems upgrading).

    - Completely removed CRM3 and the reporting services from the CRM box and reinstalled CRM using the existing MSCRM and METABASE tables and the reporting services that I created on SQL Server 2005.  I tested the reporting services worked at this stage

    - Ran through the upgrade process for CRM 3 to 4. 

    - Installed the SRS data connector on the SQL box.

    This all seems to work correctly.  HOWEVER, when I try to run a report I get the following error message:

    Error Description:
    The request failed with HTTP status 401: Unauthorized.
    
    Error Details:
    The request failed with HTTP status 401: Unauthorized.
    
    Full Stack:
    [WebException: The request failed with HTTP status 401: Unauthorized.]
       at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
       at Microsoft.Crm.ReportingServices2005.ReportingService2005.ListExtensions(ExtensionTypeEnum ExtensionType)
       at Microsoft.Crm.Reporting.ReportServer.IsDataExtensionInstalled(String extensionName)
    
    [CrmReportingException: The request failed with HTTP status 401: Unauthorized.]
       at Microsoft.Crm.Reporting.ReportServer.IsDataExtensionInstalled(String extensionName)
       at Microsoft.Crm.Reporting.ReportServer.get_IsDataConnectorInstalled()
       at Microsoft.Crm.Web.Reporting.CrmReportServerCredentials.get_ImpersonationUser()
       at Microsoft.Reporting.WebForms.ServerReport.OnCredentialsChanged(IReportServerCredentials credentials)
       at Microsoft.Reporting.WebForms.ServerReport.set_ReportServerCredentials(IReportServerCredentials value)
       at Microsoft.Reporting.WebForms.ReportViewer.CreateServerReport()
       at Microsoft.Reporting.WebForms.ReportViewer..ctor()
       at ASP.ppf_crmreports_rsviewer_reportviewer_aspx.__BuildControlreportViewer()
       at ASP.ppf_crmreports_rsviewer_reportviewer_aspx.__BuildControlform1()
       at ASP.ppf_crmreports_rsviewer_reportviewer_aspx.__BuildControlTree(ppf_crmreports_rsviewer_reportviewer_aspx __ctrl)
       at ASP.ppf_crmreports_rsviewer_reportviewer_aspx.FrameworkInitialize()
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at ASP.ppf_crmreports_rsviewer_reportviewer_aspx.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
    
    
    

    I have confirmed that I can access the report server directly from the CRM box by entering the following:

    http://<sqlservername>/reportserver2005

    When I access it this way I can run all of the reports correctly.

    The annoying thing is that this is the 4th time I have installed this and the last time I did this it worked!  I documented it (or I thought it did), but I was trying to prove that the install process worked, so I rolled it back and tried it again.

    The report server from CRM should be connecting through NETWORK SERVICE.  I am a domain admin, so it should create the authentication correctly.

    Any help you could give would be great.  I am tearing my hair out here!
    Thursday, May 21, 2009 1:39 PM

Answers

  • I suggest you start a new post explaining your exact issue, environment, error message ect.  This post is more than 1 year old and you might get a better result by starting a new post.

    Regards, Donna

           

    Tuesday, April 13, 2010 2:53 PM
  • Hi,

    Apologies for the delay in replying.  I did indeed fix this fault and it was a lot simpler than I expected.  Basically I did the following:

    1. I logged onto the SQL server and navigated to:

        C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\

    2. I changed the properties on this so that the CRM user <DOMAIN>$\<CRMUSER>$ had full permissions on the folder.  For me this CRM user was the same as my CRM server, so if for example your domain was "myDomain" and your CRM server was "myCRMServer" then the permissions would need to be set for:

        myDomain$\myCRMServer$

    Please let me know if you need anything further.

    Wednesday, April 14, 2010 8:48 AM

All replies

  • If you uninstall the SRS Data Connector, do the reports work from within CRM?

    Thursday, May 21, 2009 5:49 PM
  • Do a repair on the SRSS Connector and see if that fixes the issue. 

    Thursday, May 21, 2009 7:12 PM
  • install the SSRS connector on the CRM box
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Friday, May 22, 2009 3:37 AM
  • Hi All,

    Thanks very much for everyones replies.  In answer to your comments:

    1) Repairing the SRS data connector has no effect
    2) The error message still appears if I remove the data connector
    3) When I try to install it on the CRM box I get the following message:

    "Unable to validate SQL Server Reporting Services Report Server installation. Please check that it is correctly installed on the local machine."

     

    The information I have read seems to indicate that the SRS connector should be installed on the SQL box however, so I am not sure if this message can be ignored.

    As a final step I reinstalled the SRS connector on the SQL box, but this made no difference.  I also tried the PublishReports.exe tool that is provided as part of CRM and still nothing.

    Google search seems to indicate that this problem is not that uncommon, but unfortunately I haven't found a solution for it yet :)  Any further ideas anyone has would be greatly appreciated.

    Thanks

    Friday, May 22, 2009 10:23 AM
  • Can you still view the reports in http://sqlserver/reportserver2005?

    If you look in the CRM Deployment Manager, what is the url for the report server for your Organization?

    Friday, May 22, 2009 8:45 PM
  • Just so you know, the SRSS Connector installs on the SQL box.  I would check SQL Security and AD users to ensure the reporting rights exist in both.  I am not in front of an installation right now, to give you the exact information, but basically there are a couple of security groups that should be present in SQL and in AD. 

    You indicated in your first post that you weren't able to upgrade the original report server so you had to reinstall a new instance, load crm 3 on that and upgrade.  I suspect that whatever is not configured correctly is going to be challenging to identiify in the forum.  If you check AD and SQL rights, and all the security groups appear as expected and those same settings are available in the report server under your site settings, then you may need to engage Microsoft support to identify exactly what is missing.

    I can check one of my installs this weekend and give you a better idea what to look for if you need it.


    Best Regards, Donna
    Friday, May 22, 2009 9:47 PM
  • Hi,

    Richard - yes I can access the reports correctly through http://sqlserver/reportserver2005, even when I am logged into the CRM server

    Donna - I am in the process of trying to get my MSDN account activated for support, but it is taking a little while.  In the interim I have the following information for you:

    - The SRS connector is installed on the SQL environment
    - The report service itself runs under NT AUTHORITY\NetworkService
    - The directory security for both the Reports2005 and ReportServer2005 is set to "Windows Integrated Security"
    - When the report is ran from CRM it attempts to open http://<crmserver>/<organisation>/_root/Blank.aspx
    - With the SQL Server there are two reporting groups defined:
      <domain>\ReportingGroup {3a5d832e-521e-4635-b373-89322edd352d}
      <domain>\ReportingGroup {ff34d6f5-1a1d-4fc4-be7d-43dc187544ca}
    - The user I am testing on is a member of the above groups in AD
    - When checking in the report server (via IE) the following reporting group is defined:
       PPFINTRA\ReportingGroup {ff34d6f5-1a1d-4fc4-be7d-43dc187544ca}

       This group has Browser for Microsoft CRM, Content Manager, Publisher, Publisher for Microsoft CRM, Report Builder permissions.    In addition there is a      SQLAccess group with the same identifier.

    - The setup for the MSCRM_DataSource is as follows:
      Connection Type = Microsoft SQL Server
      Connection String = Data Source=<sqlserver>;Initial Catalog=<organisation>_MSCRM;Integrated Security=SSPI
      Windows Integrated Security


    Please let me know if you need any further information.

    Thanks

    Tuesday, May 26, 2009 8:48 AM
  • Hi,

    Not sure if this helps in any way, but I am getting an error for the reporting services and also a warning for .NET.  The reporting services on says:


    Web service request ListExtensions to Report Server http://<sqlserver>/reportserver2005/ReportService2005.asmx failed. 
    Error: The request failed with HTTP status 401: Unauthorized.

    The .NET one says:

    Event code: 3005 
    
    Event message: An unhandled exception has occurred. 
    Event time: 26/05/2009 10:49:57 
    Event time (UTC): 26/05/2009 09:49:57 
    Event ID: c81c1a5f0bb747768dd683faf2f4d8a8 
    Event sequence: 93378 
    Event occurrence: 18 
    Event detail code: 0 
    
    Application information: 
    Application domain: /LM/W3SVC/1/ROOT-1-128877888821025490 
    Trust level: Full Application Virtual Path: / Application Path: c:\inetpub\wwwroot\ Machine name: <crmserver>


    Process information: Process ID: 3412 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE Exception information: Exception type: WebException Exception message: The request failed with HTTP status 401: Unauthorized. Request information: Request URL: http://<crmserver>/<organisation>/CRMReports/rsviewer/reportviewer.aspx Request path: /<organisation>/CRMReports/rsviewer/reportviewer.aspx User host address: <ipaddress> User:<domain>\<user> Is authenticated: True Authentication Type: Negotiate Thread account name: NT AUTHORITY\NETWORK SERVICE Thread information: Thread ID: 7 Thread account name: NT AUTHORITY\NETWORK SERVICE Is impersonating: False Stack trace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message,
    WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.Crm.ReportingServices2005.ReportingService2005.ListExtensions(ExtensionTypeEnum ExtensionType) at Microsoft.Crm.Reporting.ReportServer.IsDataExtensionInstalled(String extensionName)

    Thanks



    Andrew
    • Edited by fryera2 Tuesday, May 26, 2009 10:08 AM
    Tuesday, May 26, 2009 10:03 AM
  • Thank you for all of the information, it is very helpful.  One item of particular interest is your MSCRM_DataSource.  By default, that datasource when deployed as part of the CRM isntallation does not show the Connection Type and Connection String you indicated above.

    I believe it should be showing

    Connection Type = Microsoft CRM Data Extension
    Connection String = MSCRM Data Connector Connection String
    Connect using = Credentials Supplied by User

    In Security for both the Data Source and Reporting Server I believe you should have a Domain\ReportingGroup {guid} and a Domain\SQLAccessGroup {guid}  The ReportingGroup should have Browser for Microsoft CRM and the SQLAccessGroup should have Publisher for Microsoft CRM

    I also see a Domain\Domain Users group that has Browser, Browser for Microsoft CRM, My Reports, Report Builder rights.

    As a test, open IIS on the Report Server and expand the Report Server directory.  Select the Pages folder and right click the file ReportViewer.aspx.  Select Browse from the list and see if you are able to successfully browse to the ReportServer.

    As an additional test, temporarily run the Report Service under a Domain Admin account and see if that makes a change.

    Also, review the information below and see if there is anything that might help you find resolution. 

    One option that you may want to try is to republish the reports using the publishReport.exe. That exe is located in C:\Program Files \MicrosoftDynamics CRM\Tools.  You will run this from the cmd prompt.  First, backup your database and rename your current report server folder so you can restore it if something goes wrong.

    Here is a post on how to move reports to a new SQL server.  This probably has move information than you need but there is a good description in this post by Ted from Inetium on how to view the Report Server URL in Deployment Manager to ensure it is the URL you expect. 

    http://blogs.inetium.com/blogs/tedh/archive/2008/01/17/how-do-i-move-crm-4-0-reports-to-a-new-sql-report-server.aspx
    Best Regards, Donna
    Tuesday, May 26, 2009 2:53 PM
  • Hi Donna,

    I have checked the items above and made the changes to the data source and it hasn't made any difference.  I think the system might be unfixable now as I have made so many changes over the last few days, but both the SQL and CRM servers are virtual, so I am going to rollback to the snapshot that I took just after I had upgraded and check the settings there.  Once I have done this I will update wih further details.

    I think we may be getting closer to the root cause of the problem!

    Thanks



    Andrew
    Wednesday, May 27, 2009 1:44 PM
  • Hi,

    I noticed that that there was another thread with similar problem:
    http://social.microsoft.com/forums/en-US/crmdeployment/thread/ec025168-9844-4ac4-b62e-3021d5ee54e7/

    It seems to that the CRM server is not able to get authenticated with Reporting Service. I found this useful post describing various reporting authentication settings:
    http://www.odetocode.com/Articles/216.aspx

    According to the above post the options are to enable Kerberos authentication for reporting services or run the ASP.NET process for CRM under an account with permissions to the report server .

    Hope this is helpful.



    • Edited by muralivp Sunday, June 7, 2009 8:32 AM editing hyperlinks
    Sunday, June 7, 2009 8:29 AM
  • Hi All,

    Apologies for not updating this as I promised I would - the problem is still ongoing and I have Microsoft involved now.  Basically, since my last update I have found out the following:

    1) I can run the report, using the Microsoft CRM Data Connector from a webpage http://<sqlserver>/Reports2005 when I enter the SystemUserId and OrganizationId extracted from SystemUserBase as parameters for user name and password.  This indicates to me that the data connector is working as expected.
    2) I have manually been through all the keberos authentication methods, plus I have ensured that authentication methods on both the SQL and CRM boxes are set to "Negotiate,NTLM"
    3) I have installed wireshark on the CRM box and the following errors are returned when I try and run a report:

    Content-Length: 51
    Content-Type: text/plain
    Server: Microsoft-IIS/6.0
    WWW-Authenticate: Negotiate
    WWW-Authenticate: NTLM
    MicrosoftOfficeWebServer: 5.0_Pub
    X-Powered-By: ASP.NET
    Date: Tue, 09 Jun 2009 08:15:40 GMT

    HTTP Error 401.2 - Unauthorized: Access is denied

    Thanks in advance for anyones help/suggestions!


    Andrew

    • Proposed as answer by pass-leve Friday, April 9, 2010 11:20 PM
    Tuesday, June 9, 2009 10:21 AM
  • Hi, fryera2,

     

    I have the same probem!

    Let me kown if you resolved that, and how you resolved it.

     

    Thanks

    Friday, April 9, 2010 11:37 PM
  • I suggest you start a new post explaining your exact issue, environment, error message ect.  This post is more than 1 year old and you might get a better result by starting a new post.

    Regards, Donna

           

    Tuesday, April 13, 2010 2:53 PM
  • Hi,

    Apologies for the delay in replying.  I did indeed fix this fault and it was a lot simpler than I expected.  Basically I did the following:

    1. I logged onto the SQL server and navigated to:

        C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\

    2. I changed the properties on this so that the CRM user <DOMAIN>$\<CRMUSER>$ had full permissions on the folder.  For me this CRM user was the same as my CRM server, so if for example your domain was "myDomain" and your CRM server was "myCRMServer" then the permissions would need to be set for:

        myDomain$\myCRMServer$

    Please let me know if you need anything further.

    Wednesday, April 14, 2010 8:48 AM
  • Hi, fryera2,

    Thanks for your information, it's now working.

     

    best regards

    Wednesday, April 28, 2010 12:29 AM