Saturday, July 31, 2010

While trying to publish a workbook to SharePoint 2010 to use with Excel Services, I ran into some problems. Specifically, it appeared that Excel wasn't able to access my Document library.

To publish my workbook, I followed instructions you might find anywhere on the web, using the Office 2010 Backstage to save to SharePoint. However, none of my libraries showed up by default. Not a problem! I can just type the URL into the address bar, right? Apparently Excel says that it "can't open this location using this program".

Generally problems like this are permissions related. Well, I was logged into the machine as my farm administrator, who has full rights to the Documents library. I was also able to click on files from SharePoint and have them open Excel automatically.

I decided to take a new approach. I opened the demo Excel workbook that came with the Business Intelligence Center template. I then made a small update and saved it back to SharePoint. Now when I opened Backstage to save to SharePoint, the Documents library showed up in the Recent Locations section.

I was hoping by doing this, I could trick Excel into finding the location. So I then tried to save my new workbook by just clicking on that recent location. However, Excel was still not able to do it, and told me it was unable to open my site:

After looking around on the web, I found a forum post that seemed promising, as I'm using a Windows Server 2008 R2 machine. I opened Server Manager and installed the Desktop Experience, which required me to install the Ink and Handwriting Services as a prerequisite. I was then prompted to reboot upon completion. After rebooting, I attempted to save my workbook to SharePoint again and it worked.

I've been playing a lot with Excel Services for the last week and while it is nice, it is also temperamental. Most of this can be chalked up to inexperience on my part as I discover the closest to least privileges you can get for a SharePoint 2010 Excel Services Service Application, in light of the ever popular bug, "The workbook cannot be opened". However, I'm also convinced it's a little more particular than the previous version.

By default when I installed Excel Services using a PowerShell script, it had an entry for a Trusted File Location at "http://". After playing with the Business Intelligence Center template for a bit, I created my own workbook that had a PivotTable and a PivotChart that talked to my SSAS installation. Naturally I decided to update my Trusted File Location settings. So I deleted the default entry and created a more specific one that pointed directly to the Documents library that came with the template. Then I checked on the Excel example that came with the template and found that Excel Services was no longer working because it was "Unable to process the request".

Event Viewer showed nonstop critical errors and ULS had some gems in there pinning the blame on the Secure Store Service: "Request for security token failed with exception". I tried refreshing the key figuring that my WFE and App server were out of sync, but that didn't fix the problem. Lacking a better idea, I did an IIS reset on the WFE - and it started working again.

I'm not sure why it was unhappy, but at least it was only a 10 min troubleshooting span and a simple fix!

Monday, July 26, 2010

Recently, while trying to experiment with Excel Services in SharePoint 2010, I decided to remove the service and do a reinstall with a PowerShell script. Since I like my scripts to create managed accounts as well, I removed the account that was running my service. Apparently, SharePoint didn't like this and I received an error stating that my SPManagedAccount could not be deleted because other objects depend on it when I loaded the Managed Accounts page in Central Administration. Well, not being able to load the page sort of limits my options for correcting the problem, doesn't it, Microsoft?

I double checked and the service application had been removed from SharePoint, the Excel Calculation Service had been stopped on the App Server, and the ApplicationPool had even been removed from IIS. Looking up the CorrelationID in the logs also didn't tell me very much.

I decided to pop open PowerShell and see what I could pull off. Turns out it was a rather simple fix. First, I looked up my Managed Account. I then tried to remove it and found out the dependency was an application pool. After removing the dependency I was about to remove the Managed Account! The PowerShell Commands to do this were: