How to connect to CRM

[widasofiya]

I’m trying to connect to https://contoso.crm4.dynamics.com/main.aspx.

• I first tried using Dataverse, but I couldn’t schedule a refresh on the server.

• Then I tried using the OData Feed with Organizational Authentication, which worked locally, but when I published to the server, the connection failed because Organizational Authentication is not available there.

How can I connect in a way that supports automatic refresh on the server?
My current code:

Source = OData.Feed("https://contoso.crm4.dynamics.com/api/data/v9.2/", null, [Implementation="2.0"]),

The error on server (data source):
- Current using windows credential, but get error.
"The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly. Hide error details Log on failed. Ensure the user name and password are correct."

 Thank you

[Aala_Ali · Accepted solution]

Hi @widasofiya ,

Root cause
Power BI Report Server (PBIRS) can’t refresh Dataverse because PBIRS doesn’t support OAuth/Organizational authentication. Dataverse (and CRM OData v9.2) requires Azure AD (OAuth), so refresh fails on PBIRS with Windows/Basic credentials.

Two workable paths

1) Power BI Service (cloud) — connect directly to Dataverse (recommended)

1. Build in Desktop (standard, not RS): Get Data → Dataverse (or OData v9.2) → pick tables, model, and save.

2. Publish: Home → Publish → choose a workspace in the Power BI Service.

3. Set credentials: Workspace → Datasets → your dataset → Settings → Data source credentials → OAuth2 (Organizational account) → Sign in.

4. Schedule refresh: Same page → Scheduled refresh → set frequency/time → Apply.

5. Test: Click Refresh now once and confirm it succeeds.

No gateway is needed for pure Dataverse. If your model also uses on-prem sources, install the On-premises data gateway only for those sources; Dataverse stays gateway-free.

2) Power BI Report Server (on-prem) — stage CRM data to SQL, then refresh

Since PBIRS can’t use OAuth, stage Dataverse data into a SQL store that PBIRS can reach with stored credentials.

1. Choose landing store: Azure SQL Database (SQL auth) or on-prem SQL Server.

2. Replicate Dataverse:

   • Option A: Azure Synapse Link for Dataverse → lands to ADLS Gen2 → move/merge into SQL via ADF/Fabric Dataflows/Synapse pipelines (incremental).

   • Option B: SSIS/3rd-party (e.g., KingswaySoft) to copy Dataverse → SQL on a schedule.

3. Model against SQL: In Power BI Desktop (RS) → Get Data → SQL Server/Azure SQL → select curated tables/views → build model/visuals.

4. Publish to PBIRS: File → Save As → Power BI Report Server (or upload the .pbix).

5. Configure data source (PBIRS): Portal → Report → Manage → Data Sources → SQL Server/Azure SQL → Stored credentials (SQL user/pass) → Test Connection → Apply.

6. Schedule refresh (PBIRS): Manage → Processing/Scheduled refresh → create a schedule.

7. Test & monitor: Run a manual refresh; verify row counts/timestamps. Add SQL indexes/partitioning if volumes are large.

Notes

• Ensure your Power BI tenant and Dataverse environment are in the same Entra ID tenant when using the Service.

• Prefer the Dataverse connector over raw OData where possible (metadata/relationships, throttling handling).

• For the PBIRS route, treat SQL as the single source of truth and load incrementally to keep refresh times down.
  
  
  

  If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful🤗