Lars Nielsen's Discoveries

February 5, 2013

Excel Services Troubleshooting

Filed under: Administration,SharePoint,Troubleshooting — Lars Nielsen @ 7:11 pm
Tags: ,

Here are some issues, traps and resolutions you might hit when setting up Excel Services in SharePoint 2010

Firstly here’s a quick review of the steps that you need to go through to provision Excel Services. The best rundown I have found on this is here

In my case I wanted to create a specific service for Excel Services, which means also I needed to set up an application pool for Excel Services. I think it’s better to have a dedicated service account in case I ever need to grant access to file shares to that account, or configure Kerberos settings on the account. You can also use the SharePoint Web Services Default application pool and its associated app pool account.

Here are the steps in order:

1) Create a service account for Excel Services. It doesn’t need any special group membership or permissions. I called mine sp_ExcelServices

2) Register the account sp_ExcelServices as a managed account in SharePoint

3) Start the Excel Services service on one or more servers (use Manage Services on Server link in Central Admin)

3) Create a new Excel Services service application. Choose to create a new application pool using the sp_ExcelServices managed account

5) Run the Powershell commands to grant access to the sp_ExcelServices account to the appropriate web application(s) where you want to use Excel Services.


$wa = Get-SPW1ebApplication -identity http://myweb.mydomain.com
$wa.GrantAccessToProcessIdentity("MyDomain\sp_ExcelServices")

6) Activate the SharePoint Server Enterprise features for both the Site Collection and the Site

After that you should be able to load an Excel spreadsheet into a document library and view it in the browser via Excel Services.

Here are some problems you might hit when you try to open a workbook.

The workbook cannot be opened

Two possible causes of this one:

Check if the Excel Services server is not started on any of the servers in the farm. Check this in Central Admin – Application Management – Manage Services on Server and start the service if necessary.

Also you can check that the Excel Services app pool account has access to the web application. You can check this with the User Policy screen for the Web Application. You should see an entry for the managed account for Excel Services. For good measure you can run the Powershell commands in step 5 again.

Web Application Policy

Wait a few minutes and try performing this operation again

If you get this when you try to open an Excel file then check the error logs and the Windows event log on the web server. You might see this error:

Error 5244

Unexpected error when trying to access service settings in the configuration database. Make sure the proxy for this service application is a member of the default proxy group for the active web application. Error = ExcelServerWebServiceApplication.Local: Could not get the web application associated with this context. This indicates that the Excel Server service is not properly registered or provisioned..

This might be caused by incorrect database permissions on the Configuration database.  Note that in this link they say that the Excel Service Application Pool account requires db_owner role. But I’ve found that its default WSS_Content_Application_Pools role seems to be enough.

However if you can run the Get-SPExcelServiceApplication command in Powershell and it runs OK, then it probably is not a problem with permissions on the config database. It might be that you have not associated the Excel Services service application with any application proxy groups. To resolve this go to Central Administration and in the Application Management screen click on Configure Service Application Associations. Then the simplest thing is to check if Excel Services is in the default association group (assuming that your web application is using that default group).

Service Application Proxies

Workbook cannot be opened because it is not stored in an Excel Services Application trusted location

Go to the management screen for Excel Services and click on the Trusted File Locations link.  Here you can see a list of locations that are trusted to provide Excel workbooks to Excel Services.  By default there’s usually one “HTTP” which is set to trust all children in SharePoint.  That effectively means everything in every SharePoint web application in the farm.  So you shouldn’t see any trust errors if your Excel files are in SharePoint.

But notice that it says HTTP and not HTTPS.   What if you are using Alternate Access Mappings to provide an SSL version of your SharePoint site?  Or you’re using Forefront UAG to force SSL?  In that case you might see this trust error when using the SSL address for the site, and you need to add a trusted location that starts with HTTPS, such as the hostname of the web application.  So for example if your Intranet is mapped to an externally available address, you might want to trust all the children of
https://extranet.myorganisation.com

You can’t see the Excel Web Access web part in the web part gallery or the Excel file doesn’t open in the browser

It might be obvious, but just check that the SharePoint Server Enterprise feature is activated on the Site Collection and the Site

Excel Web Access web part doesn’t update

If you have an Excel file that is being rendered onto a page via an Excel Web Access web part, then you update the Excel file, then refresh the page, you’d expect to see the changes on the Excel file to be reflected on the page. But there is caching in Excel Services and you might need to wait a few minutes before the changes show up in the web part on the page.

I’ve also found that sometimes, if you edit the Excel Web Access web part properties and hit the Apply button, it loses its named range property and you have to fill it in again. And you can’t use a server-relative URL for the location of the workbook in the web part – it always fills in the fully qualified URL.

Advertisements

June 15, 2012

Kerberos, Reporting Services and SharePoint across domains

Filed under: Administration,SharePoint,Troubleshooting — Lars Nielsen @ 8:21 pm
Tags: , ,

Here’s another small piece of knowledge to add to the body of info about Kerberos, SharePoint and Reporting Services Integration.  Like so many others, I spent ages recently trying to get Kerberos to work with SharePoint 2010. Through trial and error, I found one point that I haven’t seen mentioned anywhere else, so I thought I’d write it up here. This applies when you are trying to use Constrained Delegation in Kerberos. If you’re wondering what that is, or if you’re starting out down the road of Kerberos Authentication in SharePoint 2010, then here are the resources I found most useful:

It’s worth reading about what Kerberos is and what it does.  This video is a good first introduction.

The best starting point is this Microsoft White Paper – get it and read it very carefully!  It actually has 90% of what you need to know, but in some places I found it ambiguous or confusing. The white paper describes a number of Kerberos tools for debugging, like Kerbtray, although I ended up using just KLIST in a command window most of the time which lists your Kerberos tickets, along with Fiddler to track the requests and responses from the web server. Another great resource are Adam Saxton’s blogs on Kerberos – for example this one.  It walks you through the process with plenty of screen shots.

If (OK, when) you hit problems, you need to enable Kerberos error logging on all the SharePoint servers in the farm. You may as well do it on all the servers so then turn it off on all of them once everything is working OK. Note that you will start to see “errors” that may not be causing you any real problems – especially KDC_ERR_PREAUTH_REQUIRED errors.  Don’t worry if your event logs aren’t completely error-free.

Cross Domain Issues

The main point of this post is to cover the case where you’re working across domains, something that is not really covered in detail in the White Paper above. In this case there’s more work to do.  For example you need to establish trust between the domains even if they are in the same forest.

Duplication

You also need to bear in mind the Forest Search Order and how this affects which KDC is hit first.  If you find that everything works from one domain, but not from another, start looking for duplicate SPN’s (or even duplicate Constrained Delegation settings – see below) in the different domains.  You need to get rid of any duplicates.  Learn the SETSPN command – it has new options in Windows 2008.  I found that the SETSPN -XF command which searches for duplicates sometimes didn’t work across the forest – it missed some duplicates and reports what look like false positives relating to other services.  I  prefer to use SETSPN -L to search for duplicates explicitly on each of the relevant SharePoint and Reporting Services service accounts.  Remember to use the -S option in SETSPN rather than -A.

Delegation

One point that is in the White Paper above, but if often missed elsewhere, is if you’re using Kerberos Constrained Delegation.  Constrained Delegation is configured when, in the Delegation tab in Active Directory Users and Computers, you select the option “Trust this user for delegation to specified services only”.  Basic Delegation is set up when select “Trust this user for delegation to any service”.

In that case, you need to set up delegation (using the HTTP service) from the SharePoint Application Pool account to the Reporting Services cluster. That’s in addition to the delegation to the fully qualified and NETBIOS names for the SharePoint web application URL’s. So for example if your SharePoint site is at intranet.mydomain.com and your Reporting Services cluster is at rs.mydomain.com then, on the Application Pool identity account, you need to set up delegation for:

  • HTTP/intranet.mydomain.com
  • HTTP/intranet
  • HTTP/rs.mydomain.com
  • HTTP/rs

But you only need to set SPN’s on that same account for:

  • HTTP/intranet
  • HTTP/intranet.mydomain.com

If you don’t put in delegation to the Reporting Services cluster address, then you’ll find the authentication to SharePoint works OK, and you can see your ticket in KLIST.  But things start to break when you try to use Reporting Services features within SharePoint. For example, when you try to edit the data source in SharePoint, you might see an error:

The request failed with HTTP status 401: Unauthorized.

And when you try to deploy a report from Business Intelligence Developer Studio, you’ll be challenged to authenticate with your username and password again in a Reporting Services Login box.  Then the deployment will fail.

Don’t Mix Basic and Constrained Delegation

This is something I haven’t found documented anywhere so far.  If you use Constrained Delegation on the SharePoint Application Pool identity account, you must also use Constrained Delegation on the Reporting Services service account.  You cannot use Constrained Delegation on the App Pool identity and Basic Delegation on the Reporting Services account.  I’m guessing that the opposite is true – i.e. if you use Basic Delegation on the Reporting Services service account you must also use it on the App Pool identity account.  The White Paper above suggests that this is an issue if you need to switch away from Kerberos later in the authentication chain (protocol transition).  But I found even if everything is pure Kerberos, the same thing still applies.

If you get this wrong then you find that reports will deploy OK from Business Intelligence Developer Studio, but will not run.  If you edit the data source in SharePoint and set it to use Windows Authentication, and then click the Test Connection button, you’ll see an error:

Logon failed for NT AUTHORITY\ANONYMOUS

You’ll see similar errors on SQL Server in the SQL log files. This is because the connection between Reporting Services and SQL Server has lost your credentials, because the delegation didn’t pass the credentials that far.

Again according to the White Paper above, you can use Basic Delegation (everywhere) if you want to connect to a data source outside the domain where the Reporting Services service is running.   But if you set Basic Delegation on the Application Identity account then that might limit your ability to connect from that web application to services other than Reporting Services, like Excel Services or authenticated RSS feeds.  Using Constrained Delegation (everywhere) is more flexible, but it will prevent Reporting Services from passing your credentials to data outside its domain (even within the same forest).

[Updated – additional notes below]

SQL Analysis Services as a Data Source

As well as using Reporting  Services to report from SQL Database tables or views, you can use SQL Analysis Services as a data source as well.  If you want to do this, the Kerberos configuration becomes more complicated   But it’s the same principle.  You set MSOLAPSvc.3 SPN’s on the SQL Analysis Services service account (assuming you’re using a dedicated service account) as described in the White Paper above.  If you’re using a named instance of SQL Server for Analysis Services then you also need to set SPN’s on the SQL Browser service account.  Then you set the delegation on the Reporting Services service account to be the SPN’s on the SQL Analysis Services account.  This allows Reporting Services to pass your credentials to Analysis Services.

You also need to ensure that the connection string in the data source (RSDS file in SharePoint) contains SSPI=Kerberos.  Otherwise when you edit the data source in SharePoint and click on the Test Connection button you will see a red error message:

The connection either timed out or was lost

If you don’t put in the right SPN’s for Analysis Services then when you click the Test Connection button you will see a red error message:

Authentication Failed

 

May 15, 2012

Down for Maintenance page for SharePoint sites

Filed under: Administration,SharePoint — Lars Nielsen @ 8:36 pm
Tags: , ,

Here’s a quick tip if you need to take a SharePoint site down for maintenance, or if you have some incident that takes it down.  For example, if you’re restoring a site collection, you can put up a friendly “down of maintenance” message when people visit the site.  You can put a “no access” lock on the site collection in Central Admin, but that won’t help if you’re restoring a site collection because it needs to be unlocked.  And when the site collection is deleted, visitors will just get a timeout error in their browser if they try to go to a page within the site collection.

If you can turn off access to a whole web application then there’s a another way.  This might apply for example to an Intranet that has its own web application.  You can put a special file called app_offline.htm in the IIS root folder of the site (the same folder than contains the web.config file).  This file can contain HTML, CSS and Javascript (but no ASP.NET code – it is pure HTML).  We use it to provide links to external applications and services that are not part of SharePoint, but which people frequently access via the Intranet.   In this way people can still find those applications even if the Intranet is down.

When you do this, IIS will return the app_offline.htm for every web requestwithin the web application domain.  So don’t use <script> or <link> tags to reference other files in the same folder.  Keep everything within the HTML code of the page itself.

This is an ASP.NET facility, but it works fine with SharePoint as well.  Because it’s ASP.NET it doesn’t rely on SharePoint at all. So even if your farm is down completely (database server down, for example) then this technique will still work. You need to deploy this file manually on the appropriate web server(s) into the right IIS folder on the file system.

May 1, 2012

Easy way to move, copy or add bulk multiple accounts in SharePoint groups

Filed under: Administration,SharePoint — Lars Nielsen @ 7:10 pm
Tags: ,

Have you ever wanted to work with a bunch of users in a SharePoint group? For example:

  • Move multiple users from one SharePoint group to another?
  • Export the details of users within a SharePoint group?
  • Get a list of accounts to populate a SharePoint group?

Here’s an easy way to work with the members of a SharePoint group in bulk. Suppose for example you want to move or copy a load of users from one group to another, if you have Outlook 2007 or higher.

First browse to the membership of the SharePoint group in Site Settings – People and Groups.

Then click the “Select All” checkbox on the top left. Or select the users you want.

Select Actions – Email Selected Users

Email Selected Users

Outlook will open a new e-mail message with the e-mail addresses of those accounts you selected.

Outlook Message

Copy the e-mail addresses in the Send To text area and paste them into Notepad

Close the new e-mail message (don’t send it!!)

Browse to the SharePoint group that you want to add those users into

Select New – Add Users

In the text area paste the e-mail addresses you copied from the Outlook message

Add New Users

Click the “Check Names” icon and they should all resolve to actual AD accounts

Resolve New Users

Click OK and the users will be added to the group.

This is a great way to harvest a list of accounts for membership of a group.  If you’ve ever asked customers or colleagues to send you a list of people who should be made members of a group, what do you get?  An e-mail with a list of people names, nicknames, shortened names…   And what’s the betting that those names translate to 3 people in your corporate AD with the same name and you can’t work out which one it is?  Or it’s “William” instead of “Bill”, etc.

Solve all those problems by asking your customer to create an e-mail message with all the appropriate people in the “To” box, copy the e-mail addresses out of the box, and paste them into the body of an e-mail.  Then all you need to do is to copy and paste the list of e-mail addresses into the Add Users box as above.

April 2, 2012

PowerShell ForEach behaviour with null and empty lists

Filed under: Administration,Development,SharePoint — Lars Nielsen @ 6:23 pm
Tags: ,

Watch out when using ForEach in Powershell scripts.

Here’s a simple script that enumerates the site collections that have “portal” in the URL, and lists their URL’s:

Get-SPSite | Where-Object {$_.Url -like "*portal*"}

If you wanted to something more complex with each SPSite you might want to use a ForEach loop like this:

$sites = Get-SPSite | Where-Object {$_.Url -like "*portal*"}
foreach ($site in $sites) {
  Write-Host "Site is SPSite Url=$site"
}

This will output something like this:

Site is SPSite Url=http://portal.mydomain.com
Site is SPSite Url=http://portal.mydomain.com/sites/site1
Site is SPSite Url=http://portal.mydomain.com/sites/site2

But what if you change the condition in the Where-Object to something which doesn’t match any of your site collections, like this:

$sites = Get-SPSite | Where-Object {$_.Url -like "*8h78fw7rhfr78*"}
foreach ($site in $sites) {
  Write-Host "Site is SPSite Url=$site"
}

How many times will the ForEach loop execute in this case? If you’re a C# developer you’ll say zero – it won’t execute at all. Or you might expect an error – depending on whether $sites is a zero-length array, or null. But actually in Powershell this script above outputs this:

Site is SPSite Url=

The ForEach loop does execute, once, with a null value.

However, if you don’t use the $sites variable then it behaves differently, so if you do this:

foreach ($site in (Get-SPSite | Where-Object {$_.Url -like "*J9898fsh*"})) {
     Write-Host "Site is $site"
  }

It will output nothing and the ForEach loop doesn’t execute.

I think this is because of the way ForEach works will $null values. If you want to use variables and ForEach you need to include an explicit check for null values, which in fact you would normally do in C# anyway to be on the safe side:

$sites = Get-SPSite | Where-Object {$_.Url -like "*J9898fsh*"}
if ($sites -ne $null) {
   foreach ($site in $sites) {
     Write-Host "Site is $site"
   }
}

In this case the ForEach loop will not execute when $sites is null, neither when $sites is a zero-length array.

Next Page »

Create a free website or blog at WordPress.com.