Error during data refresh while interactive with published PowerPivot in SharPoint Excel Services

Question

I did manage to install PowerPivot for SharePoint on a single machine, although it was not easy :-). Here is my step by step guide:
http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

Now when I publish PowerPivot to SharePoint PowerPivot Gallery, I can see report there as expected, event preview does not work (I can work on that later). But as soon as I try to interact with report, I am getting following error:


"Excel Web Access"
An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:
Sandbox

How would I start investigating this problem? Which logs should I check?

I aleady checked log here:
[SystemDrive]:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\gemini.log
but there is nothing interesting.

External data source is SQL server database published on the same machine. I used SQL Server security to connect to SQL Server (that is userID and Password specified during SQL Server connection).



I am having exactly same problem on Windows 2008 server and on Windows 2008 R2 server. Hotfixes for SharePoint installed on both. I would like to learn which logs should I check,

Thank you in advance for your help

Vidas Matelis

---

http://www.ssas-info.com/VidasMatelisBlog

Answer 1

Hi, Vidas,

You can look at the sharepoint usage log from the same folder [SystemDrive]:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS. Look for the usage log file with the time stamp around the time you tried to interact with the report.

We have seen this error thrown when the PowerPivot workbook was created by an earlier build of PowerPivot addin for Excel and published to the later build of Sharepoint server. Could you tell us what build you have installed on this particular machine:
- OS
- PowerPivot Excel addin
- AS build in Sharepoint integration
- Sharepoint server build

If the machine had earlier build of PowerPivot addin, did you uninstall the earlier build of addin and reinstall the Nov CTP, and did you recreate the workbook from scratch with Nov CTP addin?

Thanks,
Lisa

Answer 2

Hi Vidas,

Was the workbook created with the same version of PowerPivot for Excel add-in that is your PowerPivot for SharePoint? Workbooks created in earlier versions of PowerPivor for Excel won't work with the current version (and the error would be pretty much like the one you got)...

Answer 3

How would I read usage log?

That machine I build is brand new virtual server. All software has just latest version.  Just to confirm:

OS: Windows 2008 SP2, 64bit, all security patches applied
PowerPivot Excel Add x64 - was downloaded from website and installed same day as it become available to public. How can I check PowerPivot version?
Office version x64 : 14.0.4514.1004 (From Excel.exe file)
AS Server build: 10.50.1352.12 (connected to SSMS to get version)
Sharepoint - 14.0.4514.1004 (from installation file)

Excel file was created and then deployed to SharePoint from scratch.

Thank you,

Vidas Matelis

---

http://www.ssas-info.com/VidasMatelisBlog

Answer 4

Mariano - could you please clarify how can I find version of PowerPivot for Excel and PowerPivot for SharePoint? Everything was installed last week, as soon as all software was released to public. I downloaded SharePoint installation files from MSDN.

I did 2 separate installations - one on Win2008 SP2, another on Win2008R2. Both have same problem.
Both machines have everything installed on them - Domain Controller, SharePoint, Office.

Thanks,

Vidas Matelis

---

http://www.ssas-info.com/VidasMatelisBlog

Answer 5

Hi, Vidas,

Appears you have an earlier build for SharePoint server. The correct build to install should be 14.0.4536.1000, not 14.0.4514.1004. You can check the binary files from C:\Program Files\Microsoft Office Servers\14.0\Bin, such as Microsoft.Office.Server.Native.dll, for the build number.

For PowerPivot addin, you can check the file version on C:\Program Files\Microsoft Analysis Services\AS Excel Client\10\Microsoft.AnalysisServices.Modeler.FieldList.dll.

Thanks,
Lisa

Answer 6

One thing that might be happening is that if your VM has its own DC, and the user who is trying to open the workbook is in a different domain, Excel expects that those two domains have a two-way trust relationship.


About the usage log, the files are created under the folder

 [SystemDrive]:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

And they will be named something like: MACHINENAME-date-time.log

Exemple
SERVERA-20091110-1408.log

You may want to look for "SSAS" and / or "Excel" in the log.

Also, in the error page you get from UI it will show a "Correlation Id", or something like that. You may want to look for it in the log file. All events related to that action will carry this correlation id along with it.


Thanks,
Mariano

Answer 7

Lisa,

Thank you for answer.I just went to technet and downloaded again latest version of SharePoint 2010 server. Again I noticed that it is exactly the same file i used to install my SharePoint. Then I checked version of that file and it states 14.0.4514.1004.

Then I open SharePoint file on already installed machine as you suggested and noticed that version is different.
On the file version is: 14.0.4536.1000

So that means my SharePoint is up to date, just you versioning of the installation file is not right.

I checked PowerPivot version and it is: 10.50.1352.12

Thanks,

Vidas Matelis

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 8

Marian,

There is no issue with domains, as I am accessing from the same virtual machine where everything is installed. So security is not an issue.

I'll try to look into logs a bit later.

Thanks for your help.

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 9

Hi Vidas,


The domain issue happens when you server is in a domain, let's say domain_a, and the user who is trying to open the workbook belongs to a domain_b. Even if that domain_b\user has proper rights in SharePoint domain_b.server, the Excel Services won't open the workbook unless domain_a and domain_b have a two-way trust relationship.

If your user and the machine are all in the same domain, so that's not the issue... :)


Thanks,
Mariano

Answer 10

I reviewed logs and I can confirm that at the time of this error message, there is no entry placed in the log Machinename-date-time.log.

Are there any other logs I could review?

I have some log entries during startup in the application event log.
Security-Licensing-SLC - Token-based Activation failed. (Warning)


Maybe that is an issue? Anything else should I check?

Thank you again for your help.

Vidas Matelis

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 11

Hi Vidas,


There should be at the folder. Note that the time is approximate. For instance, let's say that the event happened at 3:16 PM of 11-24-2009, and the UI informed you of the problem is associated with the correlation Id d7781e88-7b4d-4c8c-86e5-57d5e5898a3a. You can look at the log's folder for a file that's closer to that time. Let's say that there are three files there:

1.     MACHINE-20091124-1430.log
2.     MACHINE-20091124-1500.log
3.     MACHINE-20091124-1530.log


Your event should should be at the second file MACHINE-11242009-1500.log


And it should look like this:

11/24/2009 15:16:11.95  w3wp.exe (0x136C)                        0x0D9C SSAS Mid-Tier Service          Request Processing             32 Verbose  Redirecting request for UserName=[SHAREPOINT\system], UserAddress=[], Image=[http://MACHINE:8384/PowerPivot Management/933435f8-b2f7-46f1-bcf5-07d27b24b22a/PowerPivot Management Data.xlsx], Version=[1.0], Agent=[ADOMD.NET], Application=[], DatabaseId=[6abb8682-a815-4326-8f95-0969cecbcb12]. d7781e88-7b4d-4c8c-86e5-57d5e5898a3a

Answer 12

Mariano,

Thank you again for your help. I can confirm again, that there is no entry in the log added at the time of interaction.
Here is what happened.

At 19:00 I started server.
At 19:15 I tried to access PowerPivot report, got my error message.

Here are entries in my log:

Timestamp               Process                                  TID    Area                           Category                       EventID Level      Message  Correlation
11/24/2009 19:01:25.55  wsstracing.exe (0x09E8)                  0x09F4 SharePoint Foundation          Tracing Controller Service     5152 Information Tracing Service started. 
11/24/2009 19:01:43.85  wsstracing.exe (0x09E8)                  0x0A24 SharePoint Foundation          SQM                            8zn7 High     SQM: Failed to open file 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\data002.dat' with error 0x0 
11/24/2009 19:01:43.85  wsstracing.exe (0x09E8)                  0x0A24 SharePoint Foundation          SQM                            auk9 Medium   SQM: LastUpload=131390Ms; Now=131390Ms; FileCount=4; TotalBytes=2212 

THere are no more entries in the log. Next log file was created 30min latter and it is empty.

I can confirm that another person reported through Twitter exactly the same behaviour in his environment. What else can I do?

Thank you again for your help.

If this is an option - I can create some sort of remote access to that machine. For example using LogMeIn or any other software that you will suggest. I can create account for you to access that machine so you could investigate issue. This is demo server, there is nothing important on that machine. If this is an option, my e-mail: [e-mail]


Vidas Matelis

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 13

I decided to prepare another virtual machine that I have for remote access. That machine has Win2008R2 installed. I noticed that on that machine log file is populated and at the time of error I found following entry:

DATA REFRESH
EXCEPTION: System.UnauthorizedAccessException: Access to the port is denied.     at System.IO.Ports.InternalResources.WinIOError(Int32 errorCode, String str)     at System.Threading.Semaphore.OpenExisting(String name, SemaphoreRights rights)     at Microsoft.AnalysisServices.SharePoint.Integration.DataRefreshService.Execute() 5d7d6510-4c7f-461b-914f-800ba0d21db4

I disabled firewall on my machine, but still in SharePoint I am getting error message:
An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:

What ports should I check?

Vidas Matelis

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 14

Hi Vidas,



Can you check if the user that ytou selected during setup to be the PowerPivot system acount belongs to the SQLServerMSASUser$MACHINENAME$INSTANCENAME windows user group?


An exemple of a user group would be:
 SQLServerMSASUser$SERVER-TEST$POWERPIVOT


Thanks,
Mariano

Answer 15

Mariano,

During SQL Server I specified 2 accounts:

1. SharePoint admin account. User was <domain>\SPAdmin.
2. Account that is used by SQL Server and Analysis Services as their service account: <domain>\SQLService.

My instance name is default: GeminiBI

I checked domain user group  SQLServerMSASUser$<MyServerName>$GeminiBI and there is one member in that group <domain>\SQLService.

Just to be sure I also manually made user <domain>\SPAdmin as Analysis Services administrator. Did not help.

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 16

Could you add the <domain>\SPAdmin to the Administrators group? The problem is that the user who runs the PowerPivot process does not have access to the port. This should fix the issue.


Hope that helps,
Mariano

Answer 17

Mariano,

I added <domain>\SPAdmin user to domain admins group and then restarted machine. After that everything works as expected. 

Thank you very much for your help, it is very much appreciated!

Vidas Matelis

---

http://www.ssas-info.com http://powerpivot-info.com

Answer 18

thanks Mariano, I had the same problem this solved it. 

I did find the following line the log files which confirms your solution:

11/27/2009 11:51:27.08 w3wp.exe (0x0ED0)                       0x123C SSAS Mid-Tier Service         Data Refresh                   99 High     EXCEPTION: System.UnauthorizedAccessException: Access to the port is denied.     at System.IO.Ports.InternalResources.WinIOError(Int32 errorCode, String str)     at System.Threading.Semaphore.OpenExisting(String name, SemaphoreRights rights)     at Microsoft.AnalysisServices.SharePoint.Integration.DataRefreshService.Execute() 2a3a8302-2356-4cfe-a27c-02b82ef13223

Answer 19

Hi Vidas,


As of the Data Refresh problem, which Security Option did you use for scheduling?