Answered by:
CRM 4.0 reports not working Server 2008 Environment

Question
-
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
-
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?- Marked as answer by Donna EdwardsMVP Wednesday, July 8, 2009 3:18 PM
Sunday, July 5, 2009 6:03 PM -
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- Marked as answer by Donna EdwardsMVP Wednesday, July 8, 2009 3:18 PM
Sunday, July 5, 2009 9:44 PMModerator -
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- Marked as answer by Donna EdwardsMVP Wednesday, July 8, 2009 3:18 PM
Monday, July 6, 2009 6:34 AMModerator
All replies
-
Hello Thomas,
have you installed the SQL Server Reporting Connector?
Viele Grüße
Michael Sulz
axcentro GmbHWednesday, July 1, 2009 3:57 PM -
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.comWednesday, July 1, 2009 5:59 PM -
Hello,
CRM App Pool runs under NetworkService.Wednesday, July 1, 2009 6:59 PM -
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 -
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 -
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 CRMThursday, July 2, 2009 6:55 AM -
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 -
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?
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.
RegardsThursday, July 2, 2009 2:18 PM -
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 -
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.niekerk1Thursday, July 2, 2009 4:28 PM -
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 -
Yes, change the reporting services account from SQL Reporting Services Configuration Tool instead using the services console.Thursday, July 2, 2009 6:59 PM
-
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 -
Thomas, did you install the CRM roll up 4 on CRM server and Data Conector?Thursday, July 2, 2009 10:44 PM
-
Yes, i installed the rollup 4 on both.Friday, July 3, 2009 5:10 AM
-
hi
what if you install the SSRS on the CRM box.not using the data connectoralso just as a check the kerbros settings on account
Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1Friday, July 3, 2009 5:22 AM -
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
RegardsFriday, July 3, 2009 11:33 PM -
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 -
HI Thomas
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".
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?
http://blogs.msdn.com/benlec/archive/2008/03/04/crmdiagtool4-for-microsoft-crm-4-0-has-been-released.aspx- Edited by Tiaan van Niekerk Sunday, July 5, 2009 5:46 AM
- Proposed as answer by Tiaan van Niekerk Sunday, July 5, 2009 5:47 AM
Sunday, July 5, 2009 5:39 AM -
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).
- Es kann keine Verbindung mit der CRM-Datenquelle hergestellt werden. (rsErrorOpeningConnection)
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 - Fehler bei der Berichtsverarbeitung. (rsProcessingAborted)
-
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.cnSunday, July 5, 2009 10:45 AMModerator -
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 -
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?- Marked as answer by Donna EdwardsMVP Wednesday, July 8, 2009 3:18 PM
Sunday, July 5, 2009 6:03 PM -
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- Marked as answer by Donna EdwardsMVP Wednesday, July 8, 2009 3:18 PM
Sunday, July 5, 2009 9:44 PMModerator -
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 -
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- Marked as answer by Donna EdwardsMVP Wednesday, July 8, 2009 3:18 PM
Monday, July 6, 2009 6:34 AMModerator -
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