Geeks With Blogs
Oh my god ... not another MS CRM blog Guenter vs. MSCRM

When you open Reports from within MS CRM 3.0 after installation, it is possible to receive an error stating something like "An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'xxx'. (rsErrorOpeningConnection)". The solution to this error is not easy because there are many possible reasons. Since I encountered this error several times now,  I developed a solution strategy that works for me. Since there are also other peoples encountering these problems, I decided to post this solution here. But use this at your own risk!

Step 1:

If CRM Server and SQL Server are running on two different servers, you should check if both servers are in "Pre-Windows 2000 Compatible Access" group. If not, add them.

Step 2:

Open AD Users and Computers MMC Snap-In (dsa.msc) and search for CRM server. Select properties and then click on Delegation tab. Make sure the option "Trust this computer for delegation to specified services only" is selected. If it doesn't exist, add SQL Service from your SQL Server below (Mssqlsvc usually on port 1433). If the service is not listed you must add a SPN. If you don't know how to do that, you may take a look here (or go to Step 6):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

Step 3:

If there are multiple DCs, force replication.

Step 4:

Wait a little bit, drink some coffee, take a nap. :-)

Step 5:

Check if reports work. If yes, stop here. If not, proceed to Step 6.

Step 6:

Open up SRS Report Manager. Usually it is installed on CRM Server in the virtual directory "Reports", so open IE and type "http://<CRM server URL and port>/Reports". Then choose your organization and click on "Show details". In the list search for the data source. It should be named "MSCRM_Datasource" or something like that. Click it and look at the connectionstring. Make sure it points to the correct CRM databases containing your organization name. In addition make sure "Connect using Windows NT Integrated Security" is checked. If you changed something go to Step 5. If you didn't change anything proceed to Step 7.

Step 7:

If your SQL Server runs with Windows Authentication only (which it should): Create a user account in AD with a password that does not change. Then you have several possibilities: you may add this user to AD "SQLAccessGroup" or you may add a specific login to SQL Server for this user and add this login to CRM databases. Choose db_owner for database roles (these are the roles for the MS CRM standard logins, so don't blame me). Then go back to SRS Report Manager and manipulate the data source. Choose "Connect using Credentials stored securely in the report server", enter the credentials and check "Use as windows credentials when connecting to the database".
If your SQL Server is running in mixed mode (which it should not), you do not have to create a user account in AD. You are able to create a SQL login and use this. Add this login to the correct databases and roles (see above). Using SQL login you must NOT check "Use as windows credentials when connecting to the database".
In both cases also check "Impersonate the authenticated user after a connection has been made to the database". This is very important for CRM security mechanisms!

Step 8:

Reports should work now. I didn't encounter a situation where this did not function. Please be aware that Step 7 is not a real solution, but it works.

Have fun ;-)

P.S. If you have problems understanding how to perform any of those steps, ask your system administrator or database administrator for assistance. Right know I don't have the time to explain it in more detail or add some screenshots (weekend is near).

Posted on Friday, April 21, 2006 9:46 AM | Back to top


Comments on this post: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
Hi,
it's very interesting to read this, but my situation differs from that one you described. I have some CRM users that are able to see the reports and some that are not (receiving the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error". The second group was not able to see the reports button at all, so I added then to the ReportingGroup. They can see the reports button now, but cannot run it without error mentioned.
How this could be possible in your opinion?
Left by mx on Mar 30, 2007 10:15 AM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
Hi,
I have the "same" problem.
In Step 6 you wrote
"Click it and look at the connectionstring. Make sure it points to the correct CRM databases containing your organization name."

but i have no connectionstring !
Connection type : Microsoft SQL Server
Connection string: <nothing>

Can you help me ?

Who can I get a connection string?

thx.
Left by p43k on Jun 05, 2007 5:13 AM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
If there's no connection string then there is no connection to a database. For further details visit:

http://connectionstrings.com

BTW: Due to lacking time resources I'm not updating this blog anymore. :-(
Left by guenter on Jun 05, 2007 7:37 AM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
DataSource > [Properties] > General > Stored credentials (use admin credentials) + Check "Use as Windows credentials when connecting to the data source"

Done!
Left by Max on Dec 06, 2007 11:41 PM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
Thanks for the information! I migrated my CRMv3 setup to use a new SQL and Reporting server, but my reports weren't working at all. Step 6 solved all my problems. Thanks!
Left by Scurvy Jake on Feb 25, 2008 6:23 AM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
We had a Reporting server error (rsErrorOpeningConnection) when some users tried to access reports and our situation ended up being that the "SQLAccessGroup {GUID}" and "ReportingGroup {GUID}" were Logins on the CRM database, but were not Logins under Security for the main SQL server. We had to add them as logins for the SQL Server and the problem was resolved.
Left by Mat on Mar 25, 2008 11:42 AM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
It did work, my problem is solved. Thanks a million
Left by sreeni on May 19, 2008 2:13 AM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
here is the patch

http://support.microsoft.com/kb/947278/en-us?spid=12976


Left by niko on Jul 09, 2009 8:36 PM

# re: MSCRM 3.0 + Reporting Services + Cannot create a connection to data source + rsProcessingAborted + rsErrorOpeningConnection
Requesting Gravatar...
Hey Mat,

Right now I have one user that is unable to run reports and is getting the error

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'GP'. (rsErrorOpeningConnection)

I noticed that you said you got yours working by adding logins on the SQL server. I really dont know what logins you are talking about could you please elaborate.
Left by Paul on Dec 09, 2009 9:49 AM

Your comment:
 (will show your gravatar)


Copyright © Guenter Wallnoefer | Powered by: GeeksWithBlogs.net