Cannot connect to SSAS from Excel Web App 2010

Question

I am trying to host an Excel Workbook in SharePoint 2010.  The workbook uses a data connection to SSAS.  Neither the SSAS server nor SharePoint server are on a domain, however, my connection works in Excel by using a user name and password in the connection string of a user on the SSAS server.  I can get this to work using an embedded connection or odc file from Excel, but can't seem to get anything to work from Excel Services.  I've tried referencing an odc file in a Trusted Data Connections Library, used embedded connections, etc.  Am I supposed to use the Secure Store Service in this scenario?  I can't seem to get that to work as well.  The error I get is:

"An error occurred during an attempt to establish a connection to the external data source."

Another datapoint is that when running SQL Server Profiler against the SSAS server, there are no Audit Login/Audit Logout events detected when attempting from the Web App.

Any step-by-step instructions on how to set this up in my environment would be helpful.

Thank you.

Answer 1

This is what I get in the SharePoint log:

Refresh failed for 'Analytics' in the workbook 'http://sharepoint/Data Analysis/Analytics Demo-2010.xlsx'.
[Session: 1.V22.186xiqDb3GOM2OAyr0bbbe90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.1ad19689-e009-4196-a57f-39340fdf7dc01.N
User: 0#.w|domain\myusername]

Answer 2

Hi John,

 

Is this a PowerPivot workbook, or a regular Excel workbook?

 

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 3

How are you configuring Secure Store? Are you using the unattended account or using a Secure Store ID?

To configure the unattended account you need to:

In Excel: Go to Data -> Connections -> Properties -> Definition tab -> Authentication Settings and select None as your authentication setting.

In SharePoint:

1. Go to Central Admin -> Application Management -> Manage Service Applications -> Your Secure Store Service Application and create a target application Id. Set the credentials of this target application ID to the user name and password that worked for you in Excel.

2. Go to Central Admin -> Application Management -> Manage Service Applications -> Your Excel Service Application -> Global settings and set the unattended service account in the External data section to the Secure Store ID you created above.

Answer 4

Hi John,

 

 

Also, can you share with us the topology of your deployment? By that I mean, is the SSAS server running in the same server as the SharePoint server that is running the ECS? Or are they in different servers? If they are in different servers, are you using Kerberos for authentication? If not, than you should set up the Secure Store as Leon pointed out in his reply. :)

 

Best,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 5

Sorry I'm slow to answer your questions.  For some reason I'm not getting alerts on this thread or it's going into my junk.

In terms of the topology, the Sharepoint server is separate than SSAS.  This is also a regular Excel workbook, not PowerPivot.  I'm using an unattended service account.

I made some progress on this but not quite there.  I have two odc files in the the trusted connection library.  The only difference between the two is one has the password in the connection string, the other does not.  Both contain User ID and have Excel Services set to None.  I can now get it to work from the browser when using the version of my odc file WITHOUT the password.  When I open it in Excel, however, it prompts for the password, which makes sense.

The one with the password, however, should work in both the browser and Excel, but it only works in Excel.  This is my big frustration now.  I need it to work from the browser AND Excel without changing connection information.  For some reason, when password exists in the connection string, it does not work from the web app.  When password is ommitted, it does work.

I posted a comment on this thread as well if any of you are monitoring both: http://social.msdn.microsoft.com/Forums/en/sharepointexcel/thread/9b7a215d-ab7a-40a3-ac6f-e13d65bf720d

Thank you,

John

Answer 6

Hi John,

 

Can you send us the SharePoint logs (ULS logs) of when the error happens when you're opening you excel file in the browser (the one that uses the ods with the password)?

 

Thanks,

Mariano

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 7

 

 

And by the way, you should also post it at this forum (instead of the one you also posted):

 

http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010setup/threads

 

The one you posted is for older versions of SharePoint.

---

Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.

Answer 8

The only log entry created when I refresh or try to expand a hierarchy is this:

Log Name:      Microsoft-SharePoint Products-Shared/Operational
Source:        Microsoft-SharePoint Products-Excel Services Application
Date:          3/28/2011 8:35:27 AM
Event ID:      2051
Task Category: External Data
Level:         Information
Keywords:     
User:          NETWORK SERVICE
Computer:      sharepoint.company.local
Description:
Refresh failed for 'Connection2' in the workbook 'http://sharepoint/AnalyticsWebAccess/Workbooks/New Workbook.xlsx'.
[Session: 1.V22.308xKX3YkZZd/25Zw7iTc590.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.1ad19689-e009-4196-a57f-39340fdf7dc01.N
User: 0#.w|domain\username]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Microsoft-SharePoint Products-Excel Services Application" Guid="{278E40D0-FDAA-4EB4-AB6B-9E0AD6BDBE79}" />
    <EventID>2051</EventID>
    <Version>14</Version>
    <Level>4</Level>
    <Task>3</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime="2011-03-28T13:35:27.631113400Z" />
    <EventRecordID>65754</EventRecordID>
    <Correlation ActivityID="{98A3FA68-95B2-4D97-938E-2C3620AF0DD3}" />
    <Execution ProcessID="8920" ThreadID="7096" />
    <Channel>Microsoft-SharePoint Products-Shared/Operational</Channel>
    <Computer>sharepoint.company.local</Computer>
    <Security UserID="S-1-5-20" />
  </System>
  <EventData>
    <Data Name="string0">Connection2</Data>
    <Data Name="string1">http://sharepoint/AnalyticsWebAccess/Workbooks/New Workbook.xlsx</Data>
    <Data Name="string2">1.V22.308xKX3YkZZd/25Zw7iTc590.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.1ad19689-e009-4196-a57f-39340fdf7dc01.N</Data>
    <Data Name="string3">0#.w|domain\username</Data>
  </EventData>
</Event>