When using the OData source with CRM 4.0 SP 14 and plugging the information into Excel the error message occurs.
Error: We couldn't connect to the data source. Please check your logon information. Here's the error message we got: The remote server returned an error: (401) Unauthorized.
This was a one-off environmental issue that occurred because the ODataQuery.ashx endpoint in IIS had been modified to enable Anonymous Authentication. The resolution was to disable Anonymous Authentication on this endpoint.
Steps to Duplicate
1. Go to Analysis > Information Library > search the affected query 2. Select the arrows to the left of the query name > select get odata link > copy the link 3. Open a blank Microsoft Excel > select the data tab 4. Select From Other sources > From OData feed 5. Paste the link in the location of the data feed 6. Enter the log on credentials 7. Select Next > Receive error message