Friday, 23 May 2014

Connect to CRM 2013 OnPremise as OData Feed Service Excel Power Query Without Windows Credential (Join Domain)

In My Previous Post, I was talking about Power Query for generating the CRM Data. The data source can be various, from SQL Server, Online Search, Public Data, Web, OData, etc.

Now, I am talking about connect to CRM Data using OData Feed Request.

Here, are some good blogs explaining about connect to CRM Data, either Online or OnPremise, good for reference and inspiration.


Using CRM Online and Office 365 Authentication


Using CRM OnPremise and Windows Authentication.

If you have OnPremise Data, you also can access using SQL Server Authentication, it is easier.

But, right now, I want to access to CRM Data (OnPremise), that I cannot access to the SQL Database, and now my Windows Credential is not supported, since my PC is not joining Domain. I also don’t have the Office 365 Authentication.

Then, I try to access my CRM OData Feed :


Then, I click OK, it will prompt and challenge you for inputting correct Credential as I expected, since I don’t have default Credential for accessing this link.


Then, try to input the Credential

1. Using Anonymous


Result : The user was not authorized

2. Using Windows


Result : The user was not authorized

3. Try using Basic, also cannot.


Result : The user was not authorized

4. Using Web API


Result : I don’t know what value to be inputted as Key, so cannot be authorized.

5. Using Marketplace key


Result : I don’t know what value to be inputted as Key, so cannot be authorized.

6, Using Organizational account


Result : since this is not CRM Online and not support the Office 365 Authentication, so cannot be authorized.

I was thinking about this, it should be Basic can afford this one, but in this case, I cannot use the Basic Authentication, I don’t know why.

Then, I suddenly found a workaround by remembering some old-fashioned way, there is to use the Windows Credential Manager.

To fix this :
1. Go to Windows Credential Manager
It is located at: “Control Panel\All Control Panel Items\Credential Manager”
Or you can just type it in the Search Box of Windows.

2. Then Add a Windows Credential


3. Type in the Information you usually input to access your CRM through Browser.

Entry your Server URL (without port), let’s say you want to access :


Just type the mycrmserver or


4. OK and Save, here is the summary.


Back to Your excel, and try connect using Windows Credential, and it will work.


You also can filter the Query using URL:

Hope it helps!

1 comment:

  1. Microsoft Dynamics CRM is best customer relationship management system software for you small business. It is the hybrid business solution that can increase sales and marketing efficiency, with the help of MS Dynamics CRM we can manage customer account and lead in the cloud very easily.


My Name is..