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.

*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 :

SNAGHTML1690690c[9]

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.

SNAGHTML16a121fe

Then, try to input the Credential

1. Using Anonymous

SNAGHTML16a07bda

Result : The user was not authorized

2. Using Windows

SNAGHTML169d4c0f

Result : The user was not authorized

3. Try using Basic, also cannot.

SNAGHTML169fe661

Result : The user was not authorized

4. Using Web API

SNAGHTML169fab9a

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

5. Using Marketplace key

SNAGHTML16a3cede

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

6, Using Organizational account

SNAGHTML16a6f718

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

image

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

image

4. OK and Save, here is the summary.

SNAGHTML16942345

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

SNAGHTML16a9145a

You also can filter the Query using URL:
http://mycrmserver:5555/Contoso/XRMServices/2011/OrganizationData.svc/ContactSet

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.

    ReplyDelete

My Name is..