Customer experience | Sales, Customer Insights,...

Correct Power BI Connection on Prem

(2) Share

Report

Posted on by Liamh501

Hi,

Could somebody please help me with the correct route to connect the CRM API to Power BI? We Have CRM dynamics on prem.

I'm getting the below error when trying to connect.

Below is what I can see and the options available to me

Thanks

Liam

Categories:

Dynamics 365 Customer Insights - Data

All responses (5)

Answers (0)

Liamh501 31 on at

Like (0)

Report

Correct Power BI Connection on Prem

Hi @Daivat Vartak (v-9davar),

Thanks for this.

On the OData feed connection I'm getting authentication issues.

I have full access to CRM. Are you able to help direct me to where the other permissions would need to be?

Thanks in advance

Liam
Suggested answer

Daivat Vartak (v-9d... 6,642 Super User 2025 Season 1 on at

Like (1)

Report
Correct Power BI Connection on Prem

Hello Liam,

Okay, let's troubleshoot connecting your on-premises Dynamics 365 CRM API to Power BI. The "correct route" involves a few key steps and considerations. The error message you're encountering is crucial for pinpointing the exact problem.

Please share the exact error message you are getting in Power BI. This will help significantly in diagnosing the issue.

In the meantime, here's a breakdown of the common and recommended routes to connect Power BI to your on-premises Dynamics 365 CRM API, along with potential causes for connection errors:

The Correct Route(s) to Connect Power BI to On-Premises Dynamics 365 CRM:

There are primarily two recommended ways to connect Power BI to your on-premises Dynamics 365 CRM data:

1. Using the Dynamics 365 (on-premises) Connector:

How it works: Power BI Desktop has a dedicated connector specifically designed for on-premises Dynamics 365 CRM environments. This connector uses the CRM Web API (Organization Data Service).

Steps in Power BI Desktop:

Open Power BI Desktop.

Click on Get Data.

Search for and select Dynamics 365 (on-premises).

Click Connect.

Server URL: Enter the base URL of your Dynamics 365 CRM organization. Crucially, this should be the URL of your CRM server's Organization Web Service (OData endpoint). It typically looks like: https://<your_crm_server>:<port>/<your_organization_name>/XRMServices/2011/OrganizationData.svc/

Note the /XRMServices/2011/OrganizationData.svc/ at the end. This is essential for the older OData v2 endpoint. For newer versions (v9.x and above), you might be able to use the Web API endpoint directly (see option 2, though it's less common for this specific connector).

Authentication Method: Choose the appropriate authentication method based on your CRM setup:

Windows: If your Power BI Desktop is running under a Windows account that has access to your CRM server (often through Active Directory), you can try this.

Organizational account: This usually prompts for your CRM user credentials.

Alternate Credentials: Allows you to specify a username and password.

Click Connect.

You should then be able to navigate through your CRM entities and select the data you want to import.

2. Using the OData Feed Connector:

How it works: You can directly connect to the Dynamics 365 CRM OData endpoint using the OData Feed connector in Power BI.

Steps in Power BI Desktop:

Open Power BI Desktop.

Click on Get Data.

Search for and select OData Feed.

Click Connect.

URL: Enter the base URL of your Dynamics 365 CRM Organization Data Service endpoint, exactly as mentioned in step 5 of the Dynamics 365 (on-premises) connector instructions.
https://<your_crm_server>:<port>/<your_organization_name>/XRMServices/2011/OrganizationData.svc/

Authentication: Choose the appropriate authentication method (Basic, Windows, or Anonymous if your CRM is configured for it, though Basic or Windows are more common for on-premises).

Click Connect.

You'll be prompted for credentials if required.

You should then see the list of CRM entities.

Why You Might Be Getting Connection Errors (Common Causes):

Without the specific error message, here are the most frequent reasons for Power BI failing to connect to on-premises Dynamics 365 CRM:

Incorrect Server URL:

Missing /XRMServices/2011/OrganizationData.svc/: This is the most common mistake. Ensure the URL ends with this specific path for the older OData v2 endpoint used by this connector.

Typos: Double-check the server name, port number, and organization name.

HTTPS vs. HTTP: Ensure you are using the correct protocol (usually HTTPS for security).

Authentication Issues:

Incorrect Credentials: Verify the username and password you are using have valid access to your Dynamics 365 CRM.

Firewall Blocking: The firewall on your Power BI Desktop machine or the CRM server might be blocking communication on the specified port (usually 443 for HTTPS or a custom port if configured).

Authentication Type Mismatch: Ensure the authentication method you choose in Power BI matches the authentication configured for your on-premises CRM (e.g., claims-based authentication, Windows authentication).

Network Connectivity Problems: Your Power BI Desktop machine might not have network access to the CRM server. Try pinging the CRM server from your machine to test basic connectivity.

CRM Version Incompatibility (Less Likely): While generally compatible, very old versions of Dynamics 365 CRM might have issues with the latest Power BI Desktop. However, this is less common.

SSL Certificate Issues (HTTPS): If your CRM uses HTTPS with a self-signed or untrusted certificate, Power BI might throw an error. You might need to configure Power BI Desktop to trust the certificate or use HTTP (if not recommended for security).

Service Principal Issues (Less Common for Direct User Connections): If you are trying to connect using a service principal (for automated scenarios), ensure the service principal has the necessary permissions in your CRM. This is less likely for initial Power BI Desktop connections by individual users.

Troubleshooting Steps (Without the Specific Error):

Double-check the Server URL: Carefully verify the URL, including the /XRMServices/2011/OrganizationData.svc/ part.

Test Network Connectivity: Ping your CRM server from the machine running Power BI Desktop.

Verify Firewall Rules: Ensure your firewall allows communication between Power BI Desktop and the CRM server on the correct port.

Try Different Authentication Methods: If one authentication method fails, try another that is configured in your CRM.

Check CRM User Permissions: Ensure the CRM user account you are using has the necessary permissions to access the data you are trying to retrieve.

Review CRM Server Logs: If the connection attempt reaches the CRM server, check the CRM server's event logs or trace logs for any errors related to the connection attempt.

Once you provide the specific error message, I can give you much more targeted guidance on how to resolve the connection issue.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak
Suggested answer

Holly Huffman 5,917 on at

Like (0)

Report
Correct Power BI Connection on Prem

Sure thing -

Steps to Locate Credentials for the API URL

Check User Account Permissions

Verify that your account has the necessary permissions in Dynamics 365.

Navigate to Settings > Security > Users and check if his account is enabled for OData API access.

Confirm Authentication Type

The error suggests issues with credentials provided for the API URL.

If your organization uses OAuth, ensure you are logging in with an account tied to Azure Active Directory (AAD). These credentials would typically be his work email and password.

If using Windows Authentication, ensure the correct domain credentials are being entered.

Verify Azure Active Directory Credentials

Head to the Azure Portal (portal.azure.com) and -

Check if your account is listed under the Active Directory for the organization.

Ensure it has proper roles assigned for Dynamics 365 access.

Test with Another User Account

If your specific account has issues, try credentials from another user with admin permissions to see if the issue is account-specific.

Retrieve and Register OAuth Client ID

If your API is configured for OAuth, you may need:

The Client ID and Client Secret from the Azure App Registration for Dynamics 365.

These can be found under Azure Active Directory > App Registrations in the Azure Portal.

Validate API URL Setup

Ensure the API URL you are using (e.g., https://commerciald365.commercial.co.uk/api/data) matches the settings configured in Dynamics 365.

The URL must correspond to the specific environment in Dynamics 365.

Check Logs for More Information

In Dynamics 365, check the audit logs or server logs to identify specific authentication errors. This might pinpoint where the issue lies.

Hope this is helpful :)
Liamh501 31 on at

Like (0)

Report

Correct Power BI Connection on Prem

Hi @Holly Huffman,

Thankyou for the informative reply.

On the OData feed connection it is giving me authentication issues.

Would you know where I can find credentials that can access this API URL? I'm a little bit lost

Thanks

Liam
Suggested answer

Holly Huffman 5,917 on at

Like (0)

Report
Correct Power BI Connection on Prem

Good morning, afternoon, or evening :) depending on your location!

Based on the details in your message, it seems there may be some configuration issues with the API URL and/or authentication method.
1. Validate Your Web API URL

Ensure the structure of your Web API URL is correct. For on-premises Dynamics CRM, the format typically resembles:
https://<organizationname>.<domain>/api/data/v9.1

Replace <organizationname> and <domain> with your actual CRM environment details. Avoid including unnecessary parentheses or placeholders.

2. Check Authentication Method

The error might occur if you're using Basic Authentication, which is not typically supported for on-premises Dynamics CRM when connecting to Power BI. Instead:

Verify your CRM deployment is configured for OAuth authentication via Active Directory Federation Services (ADFS).

Power BI requires modern authentication protocols, and your CRM environment must allow token-based authentication.

3. Data Gateway Configuration

For on-premises connectivity, you need to set up and configure a Power BI Data Gateway. The gateway enables secure data transfer between Power BI and on-premises data sources like Dynamics CRM.

Install the on-premises Data Gateway from Microsoft (make sure it's on the same network as your CRM server).

Register the gateway in your Power BI workspace.

Add your Dynamics CRM instance as a data source through the gateway.

4. Connector Setup in Power BI

In Power BI Desktop, use the OData feed connector:

Go to Get Data > OData Feed.

Enter your Web API URL (e.g., https://<organizationname>.<domain>/api/data/v9.1).

Use the correct authentication method (OAuth or Windows Authentication depending on your setup).

5. Resolve Input Format Issues

The error invalid arguments: one or more of the input fields is in the incorrect format suggests there might be invalid data in your connection settings. Double-check the following:

Ensure the URL field contains no extraneous spaces, characters, or invalid data.

Validate the credentials being used—especially for OAuth or Active Directory.

6. Test Your Connection

After making these adjustments, test the connection in Power BI Desktop to ensure successful authentication and data retrieval.

Additional Notes

If you're migrating from Dynamics 365 Online (legacy), confirm you're not using outdated authentication settings.

Hope this helps some!