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

October 27, 2012

SharePoint doesn’t overwrite a document with the same name, but creates duplicates

Filed under: Content Management,SharePoint,Troubleshooting — Lars Nielsen @ 8:27 am
Tags: ,

Here’s a strange SharePoint oddity a colleague of mine saw recently.

We found that in a certain case, SharePoint looked as if it would not overwrite a Word document when we uploaded a new version of the document, even though the uploaded document has exactly same name as the existing one.  Instead SharePoint appeared to create a fresh new copy of the document, but with exactly the same name as the existing one.  So we ended with (apparently) 2 different Word documents, both with exactly the same name, in the same document library!  Here’s what we did and what was really going on:

The document library was set up to keep only major versions of documents, no enforced check-out and no content approval.  We uploaded a document into the document library with a filename of (let’s say) Departmental Resources Statement (2011-2012) .docx

The document library had a content type that required two metadata items for Title (text) and Category (choice drop-down).  So we filled in the metadata and the document appeared in the document library as expected.

Next we edited the document outside of SharePoint and uploaded it again, making sure that the tickbox was ticked to overwrite existing documents to create a new version.  On the next screen we expected to see the metadata (Title and Category) of the existing document.  But instead we saw a blank Title textbox and the default choice for Category.  So we re-entered the title again and selected the category again, the same as before, and clicked the OK button.

Next SharePoint threw an exception screen with an error message:

The specified name is already in use. A document cannot have the same name as another document or folder in this document library or folder.  Use your browser’s Back button, and type a new name.

On looking at the documents in the document library, we could see that there were now apparently 2 copies of the document both called Departmental Resources Statement (2011-2012).docx but with different Last Modified dates.

The cause of all this was the actual filename of the original document on my colleague’s PC. The document was called (look carefully!):

Departmental Resources Statement (2011-2012) .docx

See that space character after the closing parentheses and before the dot?  When you upload the document into SharePoint, on the metadata screen it trims out whitespace either side of the filename (not the extension) so that in the SharePoint document library, this file became named:

Departmental Resources Statement (2011-2012).docx

Then when we edited the original source document and uploaded it, it was still called

Departmental Resources Statement (2011-2012) .docx

SharePoint first uploaded the file into the document library, but because of that extra space it saw the filename as different to the existing document (from which the space character has been removed) so it added it as a new document.  Then it presented the metadata screen and again SharePoint trimmed off the extra space in the name. So when we hit the OK button, SharePoint saw we were trying to give this new document the name:

Departmental Resources Statement (2011-2012).docx

And because there was already a document with that filename in the document library, it threw the error. But the 2nd copy of the document had already been uploaded at this point, so both documents were visible in the document library. On the regular document library listing in SharePoint, in proportional font, that extra space character in SharePoint is practically invisible so it looked as if both documents had the same name.

Removing the extra space character in the filename solved all this. So that was it.  Simply don’t put space characters at the beginning or end of the filename and if you do, you might see the behaviour above.  Hope this helps if someone hits the same thing one day.

July 30, 2012

SharePoint 2010 Search Troubleshooting

Filed under: Search,SharePoint,Troubleshooting — Lars Nielsen @ 7:02 pm
Tags:

Here are some troubleshooting tips when you hit problems setting up SharePoint 2010 Search Content Sources

If you’re using SSL (HTTPS) for the URL of your site then you need to use SPS3S not SPS3 as the prefix for the address in the content source.  Thanks to this thread for that tip.  If you don’t then you get an error in the crawl log:

The object was not found

You might also see this error when trying to crawl My Sites and/or when using SPS3 or SPS3S:

Access is denied. Verify that either the Default Content Access Account has access to this repository, or add a crawl rule to crawl this repository. If the repository being crawled is a SharePoint repository, verify that the account you are using has “Full Read” permissions on the SharePoint Web Application being crawled. ( HttpStatusCode Unauthorized The request failed with HTTP status 401: Unauthorized. )

In this case you need to allow the crawler content account permissions access to the User Profile Service Application.  Thanks for this post from Cory Roth for the answer

In Manage Service Applications select the User Profile Service Application

Click Administrators button

Add an entry for the search crawler Content Access account and give it Retrieve People Data For Search Crawlers permission

Search Crawl account on User Profile Service

July 13, 2012

SharePoint Designer, Reporting Services and UAG

Filed under: Development,SharePoint,Troubleshooting — Lars Nielsen @ 6:51 pm
Tags: , ,

I set up a SharePoint 2010 environment with Forefront UAG and Kerberos authentication throughout, all via SSL.  Now our SharePoint sites can be published out to the Internet with a protection barrier.  It all works fine for general browsing and opening documents.  And now finally I  have solved the problem of people having to authenticate again when opening documents from SharePoint…  With UAG they authenticate once when they browse to the site and then that’s it.  They can click to open documents from SharePoint, edit documents,  and even open the document library in Explorer view without having to authenticate again.

So having got all that working, I thought: let’s push the envelope a bit and see what happens when we try to use Reporting Services reports in SharePoint Integrated mode over UAG.   Not only that, but the SSRS reports we were trying to deploy into SharePoint included hard links on the report which reference the Report Server URL directly using the ReportServerURL built-in field in the link.  So for example with regular Reporting Services your report server URL might be http://MyServer/ReportServer and then dynamicallythe report creates a link like this:

http://MyServer/ReportServer?https://myportal.domain.com/Reports/Myreport.rdl&rs:Command=Render&MyParameter=Something

That’s fine when the browser can get to http://MyServer but of course once you’re out on the Internet that URL won’t work because there’s no public DNS entry for it.  So the link will break.  You can work round this because SharePoint creates a proxy address for the Report Server which you can use instead.  The URL is like this:

https://myportal.domain.com/_vti_bin/ReportServer

OK so then this should work as the link to the report:

https://myportal.domain.com/_vti_bin/ReportServer?https://myportal.domain.com/Reports/Myreport.rdl&rs:Command=Render&MyParameter=Something

There was also a requirement for a link that directly exports the report as a PDF, using an additional query string parameter:

&rs:Format=PDF

So I thought just tag that on to the end of the main URL like this:

https://myportal.domain.com/_vti_bin/ReportServer?https://myportal.domain.com/Reports/Myreport.rdl&rs:Command=Render&MyParameter=Something&rs:Format=PDF

But when I tried this URL, instead of downloading a PDF file of the report I just got a 500 internal server error from the web server.  I tried adding an entry in my HOSTS file for myportal.domain.com to bypass the UAG server and connect directly to the SharePoint web server.  Then the link above worked OK and the download PDF popup box appeared.  So UAG is doing something to break that link.

The SharePoint ULS logs pointed to the problem.  Every time I got a 500 errors from Reporting Services I say errors like this in the logs:

Unexpected error in ProcessRequest: System.Net.CookieException: The ‘Value’=’2012,6,13,13,14,27,56,0’ part of the cookie is invalid.     at System.Net.Cookie.VerifySetDefaults(CookieVariant variant, Uri uri, Boolean isLocalDomain, String localDomain, Boolean set_default, Boolean isThrow)     at System.Net.CookieContainer.Add(Cookie cookie)     at Microsoft.ReportingServices.SharePoint.Soap.Utils.SetRelayRequestCookies(HttpWebRequest relayRequest, HttpRequest clientRequest)     at Microsoft.ReportingServices.SharePoint.Soap.RSBaseHttpHandler.ProcessRequest(HttpContext context) 2b0eb16a-e8df-4d6c-8cb8-3741011353f4

It looked as if there was some problem with a cookie with a value of 2012,6,13,13,14,27,56,0 – some kind of time stamp apparently.  I had a look at the site in Chrome and used the developer toolbar Resources tab to look at the cookies in the browser.  Sure enough there was a cookie called dwLastDetectionTimestamp with a value that looked like a timestamp with commas between the values.  I deleted this cookie manually from the browser and tried the URL again and it worked.  So it was that rogue cookie that was causing Reporting Services to fail.

Chrome Cookies

It turns out UAG injects a Javascript file called logoff.js into every web request that passes through it.  This Javascript file is what does the work of checking whether the activity timeout period has run out and whether to warn the end user of an impending auto-timeout.  It enters into an infinite recursive loop, checking that value of the cookie and if it’s null, setting it to be the current date and time, then checking to see if a certain period of time has elapsed.  Reporting Services for some reason inspects that value of this cookie but cannot understand it (because it belongs to UAG) and thus throws an exception.

The way round this is to override the UAG script and redefine that loop function to set the value of that cookie to be some non-null value, which deletes it from the cookie set.  I created a standard page in SharePoint to act as the target for Reporting Services reports.  The SharePoint page itself had a single Content Editor web part in it with a reference to my Javascript file which looks like this:

/* Strip the dwLastDetectionTimestamp cookie off the request and redirect it to the Report Server proxy */
function compareTimeStamps()
{
   $.cookie("dwLastDetectionTimestamp", null, { path: '/' }); // delete cookie
}

$(document).ready(function() {
  var FullUrl = window.location.href;
  var QueryString = FullUrl.split("?")[1];
  var NewServerUrl;
  if ((QueryString.match(/^https:\/\/myportal.mydomain.com.+$/ig))) {
    NewServerUrl = "https://myportal.mydomain.com/_vti_bin/ReportServer?" + QueryString;
    $.cookie("dwLastDetectionTimestamp", null, { path: '/' });
    window.location.href = NewServerUrl;
  }
});

To use this script you’ll need to have <script> tag references to JQuery and to the JQuery cookie plugin.  And of course you need to replace myportal.domain.com with the address of the root of your SharePoint site. The code in the script strips out the query string from the incoming request URL and checks to see if it’s a request to Report Server, and if so then it removes the cookie and redirects the browser to the Report Server proxy with the same query string.  Note the regular expression test (QueryString.match) which prevents the redirect happening when you are editing the page.  It will only redirect if the first thing after the question mark is a URL string.  The script also redefines the compareTimeStamps function within the script that UAG injects.  This is the function that is called continuously to check for auto-timeout.

In a report or a SharePoint page when you need to create a link that would use the URL of Report Server, you use instead the URL of the SharePoint page that holds this script. Add any query string parameters as required just as if you were going to the Report Server directly.

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

 

Next Page »

Blog at WordPress.com.