Auth error using OData to read Sharepoint list

[Former Member]

Hi,

I'm trying to read a Sharepoint 2010 list using an OData 2.0 source in SAP BusinessObjects 4.1.

Every time I test the connection I have the same authentication error

A problem occurred while communicating with the provider. Detailed message: Server error: (response: status=401, reason='Unauthorized', request='/myURL/$metadata')

I confirmed that the user name and password are correct, the same for permissions

I made some basic test and I can connect to my OData feed using Excel/PowerPivot and IE for example. Also, I can connect to "sample" OData feeds in SAP BusinessObjects (http://services.odata.org/V3/Northwind/Northwind.svc/)

Any help about it ?

Thanks!

[Former Member · Accepted solution]

We finally got this working.  The main requirement is that SharePoint is configured for BASIC authentication.  This may be a sticking point for you as it means that credentials are not encrypted when transmitted.  However, the Data Access Guide states that OData connections support only basic HTTP authentication or a GET access token.

In SharePoint 2013 Central Admin, click Manage Web Applications, then select the Web App (1), click Authentication Providers (2), then click the Zone name (3):

In the popup, then enable basic authentication as follows, and save the changes:

Note the warning that credentials will not be encrypted when sending requests.

In the Information Design Tool, create a new Generic OData 2.0 connection and enter the Service Root URI as shown below, replacing the blanked out area of the URI with your own site and specifying an appropriate User Name and Password.

Test Connection should work successfully.  Finish the steps to create the connection as normal.

Right-click the new connection in the Local Projects area and select New Data Foundation.

You should be able to see any lists that you have access to listed with other endpoints under the connection.  Just add it to the data foundation as if it were a table.  Right-click the list and choose Show Table Values to confirm.

Sweet!

Let me know if you need any further clarification.

[gp9 · Accepted solution]

This works great for us using http but not https:

https://xxxxxxxxxxxxxx.com/_vti_bin/listdata.svc

I'm getting error:

"A problem occurred while communicating with the provider. Detailed message: peer not authenticated"

I'm using IDT SP06 Patch 1.

Thanks,

George

[gp9 · Accepted solution]

I got this to work with https by using keytool command to import the server certificate into the jre (~\win32_x86\jre) locally where my IDT is running.

[Paul1202 · Accepted solution]

Hi Gheoghe, can you please give me some more details on how you were able to import the SharePoint server-certificate ? Many thanks

[mike_howles4]

Same issue, here.  I'm wondering if NTLM or SSO authentication is being attempted instead of the provided credentials?

[Former Member]

Hi Fabian,

Did you have any luck with this?  I am experiencing exactly the same issue using SharePoint 2013.

Best,

Paul

[Former Member]

Hi Paul,

Nothing. For now, I'm exporting SharePoint list to Excel and using it as a data source

If you find any way to use OData please let me know

Thanks

BR,

Fabian