The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

[mwand]

Receiving an error which I was hoping to receive some help with.  I am attempting to connect to a Sharepoint list - when loading data into the model I receive the following error message:

Failed to save modifications to the server.  Error returned: 'OLE DB or ODBC error: [DataSource.Error] SharePoint:Request failed: the remote server returned an error: (406) Not acceptable. (The HTTP header ACCEPT is missing or its value is invalid.)

Thanks.

[P3UAL]

Hi,

Yes, we've encountered this issue with the SharePoint List connector.

Using Fiddler, I traced what the SharePoint List connector was doing.

SharePoint List Connector - Request Header

You can see the blackbox connector is requesting JSON to come back as JSON Minimal. Unfortunately SharePoint PRE SP1 does not support JSON Light. In the SharePoint SP1 update it included an enhancment to add JSON Light support.

You simply need to amend the headers being sent using the SharePoint REST API however and ask for verbose JSON which is supported pre SP1, you won't be able to use the SharePoint List Connector blackbox as this does not allow you to customise any request headers..

My solution was to use the web connector to send your own SharePoint REST API call. Authentication remains the same if you were using kerberos.

URL: https://SITENAME/_api/web/lists/GetByTitle('YOUR_LIST_TITLE')/items?$top=5000

Header: ACCEPT application/json;odata=verbose

Web Connector to SharePoint List

You should get data from the list but you'll get lots of metadat too, so you can just transform the data pick the columns you require.

SharePoint List Data Tranformed

[Anonymous]

I receive this error also. I am connecting to an on-premise sharepoint list. The data loads in the query editor but flags this error upon attempting to load the data into the model?????????

[gmohrman]

Can a Microsoft MVP and/or PowerBI Specialist please help with this issue?! 

[Anonymous]

After some investigation work using fiddler our on-prem SharePoint doesn't accept the default header from the SharePoint list connector, if this connector had the custom header option that the web connector has I would be able to get our SharePoint to accept the header. Our SharePoint 2013 is pre sp1, are others getting this post sp1?

Thanks

[gmohrman]

Did anyone get this resolved?  If so, how ... 

[mwand]

Hello,

receiving the following error when attempting to load data from a sharepoint list. 

Failed to save modifications to the server.  Error returned: 'OLE DB or ODBC error: [DataSource.Error] SharePoint: Request failed: The remote server returned an error: )4-6_ Not Acceptable. (the HTTP header ACCEPT is missing or its value is invalid.).

Thank you for your help!

[v-yuezhe-msft]

Hi @mwand,

Could you please provide more information about your scenario?

1. Do you connect to on-premises SharePoint list or SharePoint Online list in Power BI Desktop?
2. How do you connect to the SharePoint list in Power BI Desktop? And what authentication type do you use?
3. What type of columns do you have in SharePoint list?

Thanks,
Lydia Zhang

[phil_m]

Hi Lydia,

I am also getting the error:

1. In Power BI Desktop connecting to an on-premises sharepoint list

2. Using Windows Authentication

3. The list has the following column types:

• Date and Time
• Single line of text
• Choice
• Number
• Multiple lines of text (plain text only, no RTF)
• Person or Group (single select)
• Calculated field which uses DATEDIF on two other Date and Time fields in the list

Thanks.

Philip

[timberwolf720]

Hi Lydia,

I am also experiencing a similar error. I am trying toconnect to a SharePoint List via teh SharePoint Online List in Power BI. THe list opens okay in the Query Editor, howevever, when I close the Query Editor window and apply the changes, I get the aforementioned error message in mwand's original post.

I am connecting to the SharePoint List using Windows authentication (AD) user credentials I believe. The columns contain mostly text and some numbers, but the datatype is general (any).

Any way to fix or otherwise resolve this issue?

Thanks

[gmohrman]

Did you ever resolve this issue? If so, please share.

[gmohrman]

I'm getting the following error message ["OLE DB or ODBC error: [DataSource.Error] SharePoint: Request failed: The remote server returned an error: (406) Not Acceptable. (The HTTP header ACCEPT is missing or its value is invalid.)]" and I appreciate the Power BI community forum feedback.  However, I don't see that this conversation thread has a solution per se.   Did you ever get this resolved?  Does anyone else have any instructions or advise to help me with this issue (406)?  If so, please help.   Thanks,