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.

*Online
http://niiranen.eu/crm/2013/12/connecting-crm-online-odata-feed-excel-2013-power-query/

Using CRM Online and Office 365 Authentication

*OnPremise
http://www.mindthecloud.net/?p=1020

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 :

http://mycrmserver:5555/Contoso/XRMServices/2011/OrganizationData.svc/
or
http://175.111.222.18:5555/Contoso/XRMServices/2011/OrganizationData.svc/

Just type the mycrmserver or 175.111.222.18



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:
http://mycrmserver:5555/Contoso/XRMServices/2011/OrganizationData.svc/ContactSet

Hope it helps!