How to Create Dynamics CRM Reports Accessing Outside Data

I recently had a request to create a Report that used both tables inside of Microsoft Dynamics CRM 2011, with tables from another database within the same SQL Server. Typically, when I do this, I write the query inside SQL Server Management Studio and make sure the results I am looking for are returned.  Then, when I start to create the Report in Visual Studio, at the Query Builder, I copy and paste my SQL Statements in and test the results.

select P.CALLNUM, P.ITEMNUM, P.ITEMUSETYPE,

 I.ticketnumber, I.title,I.AccountIdName,

            E.new_gpitemnumber, E.new_gpequipdesc, E.new_serialnumber

from Filterednew_equipment as E

   join FilteredIncident as I

      on I.new_equipmentid = E.new_equipmentId

   inner join [Other_MDB].dbo.Parts as P

      on 'CAS-' + LEFT(P.CALLNUM,5) + '-' + substring(P.CALLNUM,6,10) collate database_default =

 I.ticketnumber collate database_default

 Then in Visual Studio I right-click on each table and change the Alias to CRMAF_Table for each table so I can use CRM Filtering. When I finished the Report it looked beautiful.  I then uploaded it into CRM Reports and it loaded with no errors.  However, when I ran the Report in CRM I got the following error:

 

The way to identify the problem is to go to the Event Viewer on the SQL Server and look at the errors generated by MSCRMReportingDataConnector.  The first error that I got was:

          Report data set execution failure. Error: The server principal "SERVER\sqldevmscrmsvc" is not able to access the database "Outside_CRM_DB" under the current security context.

The Solution to this error is to navigate in SQL Server Management Studio to the Security Folder in the Object Explorer outside of the Databases section (Not the Security section inside a specific Database). Here's how to get there:

• Expand Logins. (ServerName > Security > Logins)
• Select the Login that is having the troubles.   In my case, sqldevmscrmsvc
• Right click on the Login and select Properties (as shown below).

Next, select User Mapping.  This will list all databases on the server with a check mark on the databases that the Login has been mapped to.  The database in question was not checked, even though the Login was assigned as a User on the database itself (as shown below). 

After this, I re-ran the Report and got the same error.  I went back to the Event Viewer and the error message changed:

          Report data set execution failure. Error: The SELECT permission was denied on the object 'MyTable', database 'Outside_CRM_DB', schema 'dbo'.

I fixed the error by going back into User Mapping and assigning the default schema for the database to db_owner following this path:

• Security > Logins > Username > Properties > User Mapping > ellipsis on the right (as shown below).

This time when I ran the Report it displayed as I had hoped, and there was no corresponding Event Viewer error. Accessing data from outside sources and displaying them in a CRM Report is a powerful feature. And by following a few extra steps, it's very do-able.

Submitted by Gerry Yurko, Core Solutions CRM Technician.