Dynamics CRM Publish External Report

Dynamics CRM on-prem has the ability to publish reports "externally" so that non-CRM users can view them.  Corresponding reports are created by CRM in the "root" folder (tenant name_MSCRM)  By default though, the SSRS data connection (MSCRM_DataSource) in the folder is not configured during installation, so a required step is to login to SSRS Report Manager and setup the data source.  Note, you have to click Details View (upper right corner of menu) in order to see the data source.

Typically, you'll want to use a service account that has the least privileges necessary to query the data from CRM.  The connection string follows the format:

Data Source=sql server;Initial Catalog=tenant_MSCRM

If you use a service account, you may get an error when you Test Connection in SSRS Report Manager:
Log on failed. Ensure the user name and password are correct.

In that case, the error may be caused because the account does not have "Log on locally".  

To grant this permission, do the following:

1.      On the report server computer, in Administrative Tools, open Local Security Policy.

2.      Under Security Settings, expand Local Policies, and then click User Rights Assignment.

3.      In the details pane, right-click Allow log on locally and then right-click Properties.

4.      Click Add User or Group.

5.      Click Locations, specify a domain or other location that you want to search, and then click OK.

6.      Enter the Windows account for which you want to allow interactive login, and then click OK.

7.      In the Allow log on locally Properties dialog box, click OK.