CRM 2011 - MSCRM_DataSource being reset to CRM SQL

(0) Share

Report

Posted on by Community Member

Hi,

I have a folder in SSRS which hosts my reports and has a MSCRM_DataSource datasource which uses a Data source type of Microsoft SQL Server and has an appropriate connection string.

The reports all work for a while until for some reason the MSCRM_DataSource is somehow changed to have a Data source type of Microsoft Dynamics CRM SQL and a connection string of "MSCRM Data Connector Connection String". I have no idea what causes this. When it occurs I simply edit the MSCRM_DataSource back as it should be. I guess I could edit the security on the datasource to make it read-only but I would like to find out what is continually resetting MSCRM_DataSource.

This occurs on all the servers that we have the reports loaded onto.

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (1)

Royal King 27,686 on at

Like (0)

Report
All these are custom reports published in CRM is it? If so check the <DataProvider> element in the report and if it looks like below

<DataProvider>MSCRMFETCH</DataProvider>

Change it to

<DataProvider>SQL</DataProvider>

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for your reply Chitra.

Yes, the reports are published in the CRM.

The report RDLs do not have a <DataProvider> element, they do have:

<DataSource Name="MSCRM_DataSource">

<DataSourceReference>MSCRM_DataSource</DataSourceReference>

<rd:SecurityType>None</rd:SecurityType>

<rd:DataSourceID>---Guid ---</rd:DataSourceID>

</DataSource>

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

Add <DataProvider>SQL</DataProvider> in your reports , publish it and monitor the behavior

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Chitra,

Do I need to put the connection string in the RDL as well or will it still use the connection string in MSCRM_DataSource?

Was this reply helpful? Yes No
Verified answer

ScottDurow 21 on at

Like (0)

Report

I think the problem may be that you are using the out of the box connection that is created by CRM. If you want to use a different connection you should create a one named differently.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ah, so you think that Dynamics keeps an eye on the MSCRM_DataSource and MSCRM_FetchDataSource datasources and if they get changed it resets them?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I've changed one of my reports to use a new shared datasource and it works OK in BIDS.

I uploaded it into SSRS and ran it, it aborted with "Cannot create a connection to datasource MSCRM_DataSource". When I Manage the report in SSRS under DataSources it is still set to use MSCRM_DataSource and MSCRM_FetchDataSource rather than using the new shared datasource I created and set in the RDL. There is no mention of the 2 MSCRM datasources in the report RDL at all!

ANy ideas?

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

This is what i have in my custom reports for datasource section of the rdl file

<DataSource Name="MSCRM">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=ORMCRM;Initial Catalog=Org_MSCRM</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
<rd:SecurityType>Integrated</rd:SecurityType>
<rd:DataSourceID>a26f9852-8aad-48bc-a083-ea8b2f49978a</rd:DataSourceID>
</DataSource>

I think adding <DataProvider>SQL</DataProvider> will resolve your issue.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm getting somewhere now I think.

When I upload a NEW report that uses a new shared datasource, the datasource is correct under Manage ... Data Sources and the report runs OK.

So now I need to find out why the Data Sources are not updated when an existing report is updated.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I want to avoid having the connection string inside each report because we have a number of tenancies in which the suite of reports need to be published.

A shared datasource that has the connection string will make deployment easier.

Was this reply helpful? Yes No