Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRM 4.0 reports not working Server 2008 Environment RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello,

    i am having problems to get reports to work in the following scenario:

    1x Windows Server 2008 with CRM 4.0 installed
    1x Windows Server 2008 + SQL 2008 + Reporting Services + CRM Data Connector

    Reporting Service on SQL Server runs as a normal domain user.

    When i try to run a report it fails.

    Following errors in application eventlog:

    EventID 19968 from Source MSCRMReporting
    Web service request ListExtensions to Report Server http://mssql1/ReportServer/ReportService2005.asmx failed. Error: Fehler bei der Anforderung mit HTTP-Status 401: Unauthorized.

    EventID 1309 from Source ASP.NET 2.0.50727.0
    Event code: 3005
    Event message: Es ist eine unbehandelte Ausnahme aufgetreten.
    Event time: 01.07.2009 09:43:59
    Event time (UTC): 01.07.2009 07:43:59
    Event ID: 07feefcea2e646ddab15ba13256bb628
    Event sequence: 31
    Event occurrence: 1
    Event detail code: 0
     
    Application information:
        Application domain: /LM/W3SVC/2/ROOT-1-128909075176566464
        Trust level: Full
        Application Virtual Path: /
        Application Path: C:\Program Files\Microsoft Dynamics CRM\CRMWeb\
        Machine name: CRM1
     
    Process information:
        Process ID: 2228
        Process name: w3wp.exe
        Account name: NT-AUTORITÄT\NETZWERKDIENST
     
    Exception information:
        Exception type: WebException
        Exception message: Fehler bei der Anforderung mit HTTP-Status 401: Unauthorized.
     
    Request information:
        Request URL: http://crm1:5555/OrgName/CRMReports/rsviewer/reportviewer.aspx
        Request path: /OrgName/CRMReports/rsviewer/reportviewer.aspx
        User host address: 2002:c300:e3f4::c300:e3f4
        User: domain\user
        Is authenticated: True
        Authentication Type: Negotiate
        Thread account name: NT-AUTORITÄT\NETZWERKDIENST
     
    Thread information:
        Thread ID: 14
        Thread account name: NT-AUTORITÄT\NETZWERKDIENST
        Is impersonating: False
        Stack trace:    bei System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
       bei System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
       bei Microsoft.Crm.ReportingServices2005.ReportingService2005.ListExtensions(ExtensionTypeEnum ExtensionType)
       bei Microsoft.Crm.Reporting.ReportServer.IsDataExtensionInstalled(String extensionName)
     

    CRMDIAGTOOL Output:

    Web service request ListExtensions to Report Server http://ourcrmserver/ReportServer/ReportService2005.asmx failed. Error: Fehler bei der Anforderung mit HTTP-Status 401: Unauthorized.
    Wednesday, July 1, 2009 8:05 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    O.K.,

    so here is an update.

    I changed the CRM App Pool Account on CRM Server to the domain user that i used to install sql server and crm server, he is local admin on both machines.

    Reports work fine now!!!

    But i need to know why. I think Tiaan was right that it has to do with sql permissions.

    So my questions are:

    1.) Is there any security problem when i use my domain account for crm app pool?


    2.) What permissions are needed when i use networkservice as the crm app pool account? Maybe someone can share the permissions that are set in his environment.


    @Jim --> Thanks for pointing me again to the article crm 4.0 + server 2008. I did read that article but i did not changed the crm app pool account. Do you run it under a normal domain account? Do you use a dedicated domain account or the user that you used to install crm?

    Sunday, July 5, 2009 6:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It's good to know you have fixed the problem. The local admin can't be the IIS run as account, it doesn't have permission cross the network.
    If you are running CRM 4.0 on Windows 2008 server, it's recommend to use Network Service as the service account, that's because the IIS6.0 compatibility mode on Windows Server 2008.


    Cheers,
    Jim
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    Sunday, July 5, 2009 9:44 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Thomas,

    The service account should be in both the PrivUserGroup and SQLAccessGroup, and

     in the following groups in the Microsoft Dynamics CRM server:
    • The local IIS_IUSRS group
    • The local CRM_WPG group
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    Monday, July 6, 2009 6:34 AM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello Thomas,

    have you installed the SQL Server Reporting Connector?

    Viele Grüße

    Michael Sulz
    axcentro GmbH
    Wednesday, July 1, 2009 3:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It looks like it's an authentication issue, can you check to see what's the identity that the app pool uses? Are you using NETWORK SERVICES?
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    Wednesday, July 1, 2009 5:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello,

    CRM App Pool runs under NetworkService.


    Wednesday, July 1, 2009 6:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Thomas,

    - Check if you can run the reports on the Report Server Admin Site.
    - If you use NT AUTHORITY\NETWORK SERVICE Accounts, check if the account have privilege on the MSCRM_CONFIG and ORG_MSCRM Databases, and verify the RSExecRole for ReportServer Databases.

    Regards.
    Wednesday, July 1, 2009 9:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    When i start e report on Report Server Admin Site i am ask for a password and username. I entered a domain account which has local admin rights on both crm and sql machine and is dbowner in sql.

    Following error:

    Fehler bei der Berichtsverarbeitung. (rsProcessingAborted)
    Es kann keine Verbindung mit der CRM-Datenquelle hergestellt werden. (rsErrorOpeningConnection)
    GUID muss 32 Ziffern mit 4 Bindestrichen enthalten (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

    Permissions for NT AUTHORITY\NETWORK SERVICE are correct. Verified that in SQL Management Studio.

    Could the problem be related to the account that i am using for running the report server service on SQL Server? Remember i use a normal domain account. But as i read there is no need for delegation and SPNs with CRM SRS Data Connector anymore. Right?

    Thursday, July 2, 2009 6:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Thomas,

    to run a report from CRM, the user bust be a user in CRM. It's not enough to have admin rights on the server.

    Viele Grüße

    Michael Sulz
    axcentro GmbH
    MVP für Microsoft Dynamics CRM
    Thursday, July 2, 2009 6:55 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    He is admin user in CRM, i used this user to install crm.

    Is the error "rserroropeningconnection" a normal one cause in 2 other environments i get the same error and reports do work fine there.
    Thursday, July 2, 2009 7:09 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Could the problem be related to the account that i am using for running the report server service on SQL Server? Remember i use a normal domain account. But as i read there is no need for delegation and SPNs with CRM SRS Data Connector anymore. Right?

    It Could be, I had a similar issue. Can you try changing the service account that you use for Reporting Services for a NETWORK o LOCAL ?
    If you use a domain account to run a service, you must setup the SPN for the accounts.

    In my case, I change the SQL 2008 SRS Service account for NETWORK SERVICES o LOCAL SYSTEM solved the problem.

    Regards
    Thursday, July 2, 2009 2:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Is there anything to take care of when changing the report server account? Backup the encryption key or anything else?

    I thought i do not have to setup SPNs when using CRM SRS Data Connector on SQL Server. Am i right?

    Thursday, July 2, 2009 3:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    HI
    give your admin user admin rights in sql.
    make sure your user account is featured in the report OU in ad.
    Check taht all your sql services are started under the same account
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Thursday, July 2, 2009 4:28 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Currently SQL Server and SQL Agent run under local system. Report Server runs under a domain user. I had to change SQL Server and Agent Account because issues during CRM Setup but i am careful when it comes to changing the report server account.

    My plan is to use network service for all three accounts.

    Anything to take care of when changing the three accounts, specially report server account to network service?


    Thursday, July 2, 2009 5:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes, change the reporting services account from SQL Reporting Services Configuration Tool instead using the services console.
    Thursday, July 2, 2009 6:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    This is really driving me crazy.

    Changed All SQL Accounts to network service.

    Uninstalled CRM Data Connector, reinstalled CRM DATA Connector.

    Looked at the reports site and noticed that under MSCRM_DataSource Microsoft SQL Server was selected.

    Changed it to Microsoft CRM Data Extension. Then i changed the connection string according to this kb:

    http://support.microsoft.com/default.aspx/kb/946289


    Did a restart of SQL Server and CRM Server. And guess what, same error, reports cannot be displayed. Any suggestions?
    Thursday, July 2, 2009 10:12 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thomas, did you install the CRM roll up 4 on CRM server and Data Conector?
    Thursday, July 2, 2009 10:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes, i installed the rollup 4 on both.
    Friday, July 3, 2009 5:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi
    what if you install the SSRS on the CRM box.not using the data connector

    also just as a check the kerbros settings on account

    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Friday, July 3, 2009 5:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Thomas:

    Do you have any update? Maybe Reinstalling SQL 2008 using Network or Local Services Accounts could be better and faster to resolve the issue

    Regards
    Friday, July 3, 2009 11:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I did not thought that this error is so difficult to resolve. Has anybody knowledge how to do some advanced tracing to find the cause of the error? Uninstalling SQL is not an option, cause the second instance of sql, where sharepoint backend resides is already in use by the customer.

    Could it be a kerberos issue? I thought when installing CRM SRS Data Connector the requirement to set SPNs is removed.

    Saturday, July 4, 2009 9:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    HI Thomas
    This is definately a security issue on sql.
    The data connector must be installed on the CRM box.

    This can also caused by the security constratinst on sql services based on user authority.

    Can you manually run a report on the reportserver http://reportserver/reports?

    look at this link http://extremecrm.net/2009/03/17/microsoft-crm-40-ssrs-2008-integration/ Also, in this document, they talk about "Use embedded data-sources".

    http://blogs.msdn.com/benlec/archive/2008/03/04/crmdiagtool4-for-microsoft-crm-4-0-has-been-released.aspx
    Sunday, July 5, 2009 5:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Where did you get that information from, that data connector must be installed on the crm box? In crm 4.0 deployment guide you can read the following:

    The CRM Data Connector must be installed on a computer that hosts a instance of SQL Reporting Services 2005 or 2008. And this is on the SQL Box which is seperate in my environment.


    When i try to run a report on http://sqlsvr/reports Website i am asked for credentials. I typed in my domain account which was my installation account for crm.

    Following error occured:

    • Fehler bei der Berichtsverarbeitung. (rsProcessingAborted)
      • Es kann keine Verbindung mit der CRM-Datenquelle hergestellt werden. (rsErrorOpeningConnection)
        • GUID muss 32 Ziffern mit 4 Bindestrichen enthalten (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

    But this error only occurs when i put in CRM Data Extension in the MSCRM Datasource. When i use SQL Server with integrated security reports run fine on SQL Server. But on all other Servers and CLients I get the 401 Error.

    • Edited by UTTO Sunday, July 5, 2009 8:56 AM
    Sunday, July 5, 2009 8:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Thomas,

    First try:

    If you run the following script:

    select @@version

    on the SQL server management tool, what do you get?


    Second try:

    Server1. 1x Windows Server 2008 with CRM 4.0 installed
    Server2. 1x Windows Server 2008 + SQL 2008 + Reporting Services + CRM Data Connector

    Also, how did you manage to install CRM/SQL?
    The order I suppose was: 
    1. Install Windows Server 2008(with SP2) on both servers, make sure they are in the same domain and trust each other
    2. Config both server using: Support for Microsoft Dynamics CRM 4.0 on Windows Server 2008-based computers
    3. Install SQL 2008 with Reporting Service on Server 2;
    4. Install SQL 2008 SP1 on Server2;
    5. Install CRM 4.0 on Server1;
    6. Install CRM SQL Connector on Server2;
    7. Install CRM 4.0 rollup4-Server on Server1;
    8. Install CRM 4.0 rollup4-SSRS on Server2.



    Please confirm.

    Cheers,
    Jim




    Jim
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    Sunday, July 5, 2009 10:45 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Regarding First Try i get:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)


    This is how i did installation:

    1.) Installed Server 2008 with SP2 on both Servers
    2.) Installed SQL Server 2008 with Reporting Services on Server 2 in default instance
    3.) Installed a second named instance for a Sharepoint Backend

    I used a normal domain account, that is local admin on sql box to start the installation. Everything worked fine.

    I used normal domain users for the three sql accounts on sql server (sqlserver, sqlagent and sqlreporting).

    4.) Installed SQL Server 2008 SP1
    5.) Installed CRM 4.0 on Server 1, applied updates during startup but got an error at end of setup
    6.) Changed all 3 SQL Services on sql box to networkservice
    7.) Completely uninstalled CRM on Server 1 and reinstalled, applied updates during setup
    8.) Everything went fine
    9.) I did setup trust for delegation for the CRM-Server in Active Directory (Windows 2000 mixed domain)
    10.) I installed CRM DATA Connector on SQL box
    11.) Installation of Data Connector went fine but when i looked at MSCRM Datasource on Reports-Website i noticed that the Data Source Typ was not set to MSCRM Data Extension. It was set to SQL Server. So i changed that manually and in addition i changed the connection string according to a MS KB Article.
    10.) I applied CRM Update Rollup 5 on CRM Server and on CRM Data Connector.
    11.) Restarted both servers.

    Reports did not work.

    12.) I did setup SPNs for CRM Server

    HTTP/crm1.domain.local
    HTTP/crm1:5555

    Reports still do not work.


    Sunday, July 5, 2009 5:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    O.K.,

    so here is an update.

    I changed the CRM App Pool Account on CRM Server to the domain user that i used to install sql server and crm server, he is local admin on both machines.

    Reports work fine now!!!

    But i need to know why. I think Tiaan was right that it has to do with sql permissions.

    So my questions are:

    1.) Is there any security problem when i use my domain account for crm app pool?


    2.) What permissions are needed when i use networkservice as the crm app pool account? Maybe someone can share the permissions that are set in his environment.


    @Jim --> Thanks for pointing me again to the article crm 4.0 + server 2008. I did read that article but i did not changed the crm app pool account. Do you run it under a normal domain account? Do you use a dedicated domain account or the user that you used to install crm?

    Sunday, July 5, 2009 6:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It's good to know you have fixed the problem. The local admin can't be the IIS run as account, it doesn't have permission cross the network.
    If you are running CRM 4.0 on Windows 2008 server, it's recommend to use Network Service as the service account, that's because the IIS6.0 compatibility mode on Windows Server 2008.


    Cheers,
    Jim
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    Sunday, July 5, 2009 9:44 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think you misunderstood that a little. The User is a domain user that has local admin rights on the 2 Servers. So from that point should be no problem.

    When it is recommended to use the networkservice as the crm app pool account i need to know what permissions must be set on sql to get that working. At the moment it only works with my domain account which has sysadmin role on SQL Server.

    Monday, July 6, 2009 5:48 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Thomas,

    The service account should be in both the PrivUserGroup and SQLAccessGroup, and

     in the following groups in the Microsoft Dynamics CRM server:
    • The local IIS_IUSRS group
    • The local CRM_WPG group
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    Monday, July 6, 2009 6:34 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    And as of Server 2008 R2 it appears you can no longer put "Network Service" service account into the PrivUserGroup/SqlAccessGroup if you are also on a Domain Controller (i.e. network service is not supported as the SQL server Service Account on domain controllers).

    So, some users still need to use DOMAIN\User accounts to set things up....

    Monday, August 23, 2010 6:50 PM