OLEDB connection string error in Excel Services

Question

I have Excel document in my SharePoint library. This document is connected to Analysis Services. Connection string is  "Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=tfs2010;Location=tfs2010;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error". In Excel 2007 and SharePoint Server 2007 it works.

In auth setting SSO is configured

In SharePoint 2010 I get message "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh". In SharePoint Logs I found "OLEDBConnection::SetInitProperties: Format of the initialization string does not conform to the OLE DB specification"

Answer 1

Hi Konstantin.  I'm assuming that you are using Excel Services to communicate with Analysis Services 2008.  Unfortunately, this won't work in Beta 2.  The issue has been resolved since then, but we don't have a workaround in Beta 2 other than to use a different version of Analysis Services.  Sorry.

Answer 2

I'm having the same problem with SSAS 2005 - Excel 2007 and Sharepoint 2010. It works fine if I access the web page with the Excel Web part from the Sharepoint computer but will not work from any other computer.

All help is appreciated.

---

pete doyon

Answer 3

That's likely because you have your connection set to use Windows Authentication (which works when you are on the same physical machine as the server) when you need to use SSS (or SSO in SharePoint 2007) to allow Excel Services to delegate your credentials when you are on another box.

Take a look at one of the follow two articles:

For SharePoint 2007: http://technet.microsoft.com/en-us/library/cc262899.aspx
For SharePoint 2010: http://technet.microsoft.com/en-us/library/ee662541%28office.14%29.aspx#section4 (and some more SSS specific documentation: http://technet.microsoft.com/en-us/library/ee806866%28office.14%29.aspx)

Answer 4

Hi Dan,
I'm attempting to do the same thing, Excel spreadsheet that contains a Pivot Table that is linked to a Analysis Server 2008 connection. However I am just getting a generic "Unable to refresh data for a data connection in the workbook" error.

Does this mean that it is absolutly NOT possible to have this functionality in SP2010? If not is there any way that we can work around this as this is a huge PITA for us :)

Thanks!

Answer 5

Hi Dan,

I spent a whole day trying to figure out why the Excel PivotTable connecting to a SSAS 2008 cube didn't work. After monitoring the analysis server and seeing the connection was made successfully, I was totally confused why Excel Web Service kept complaining about not being able to make connections. Finally I saw your post and knew it was the problem with BETA!

When will Microsoft post a fix? I am setting up a Sharepoint website at my company for POC. I would really like to get the PivotTable working. Can you please send me the fix?

Thanks
Yan