Unable to open Excel documents in Office from SharePoint 2013

You may want to check out KB 2936341 from Microsoft. There was a known bug where the WebClient was not always sending the persistent cookie.

How I was able to get this working is the switch statement below. It may not be as surgical as other ways, but it was the only way I found to get this working. This switch statement allows the following programs through:

• Office 2010/2011/2013
• SharePoint Designer 2010/2013
• WebDAV/Open in Explorer (IE 9/10/11)
• Office Upload Center 2013
• SkyDrive/OneDrive 2013
• InfoPath 2013

switch -glob [string tolower [HTTP::header "User-Agent"]] {
    "*word*" -
    "*excel*" -
    "*office upload*" -
    "*office protocol discovery*" -
    "*soap toolkit*" -
    "*webdav-miniredir*" -
    "*frontpage*" -
    "*msfrontpage*" -
    "*shareplus*" {
        HTTP::disable
    }
    default {
        switch -glob [string tolower [HTTP::uri]] {
            "*owssvr.dll" {
                HTTP::disable
            }
        }
    }
}

I had a similar problem. Here is a write-up I did for my fix:

Problem: When running SharePoint 2013 behind an F5 Virtual Server utilizing APM and basic Active Directory authentication, when an MS Office Thick client (word, excel) makes its first request to the VIP to open a document, it has no cookie information. The APM module functions as normal and redirects the un-authenticated request to F5Networks-SSO-Req?xxxx, which sets a cookie, and forwards to my.policy page for forms login. Unfortunately, these redirects are accepted by excel client for instance, and actually attempts to request the my.policy page. Of course, excel has no idea how to open that page and barfs, indicating it cannot connect to the page.

Requirements: * Do not bypass APM authentication * Do not use disable::http and essentially pass the request directly to the end server acting as a dumb proxy (which in this instance would force NTLM authentication which is a problem). * No sharepoint server NTLM authentication. We have over 100 windows domains connecting to this VIP, so challenging the user with NTLM would force their workstation to authenticate with its domain, which isn’t going to work as this would require us to manage sharepoint permissions for every user in every domain.

Solution: Create an irule that will check the user-agent for any MS office thick client, and if found, check if the MRHSession cookie exists. If it does not exist, respond with a 403 auth status code, and send two http headers with name "X-Forms_Based_Auth_Required," and “X-Forms_Based_Auth_Return_Url”. The values in this instance will be https://[HTTP::host]/reauthpage?ReturnUrl=/_layouts/15/error.aspx, and https://[HTTP::host]/_layouts/15/error.aspx,” respectively. Reauthpage is a non-existent page that would otherwise never be called. The auth-required header will indicate to excel to call a forms login page, and the return_Url header will also cause excel to close the “re-auth APM login” window. Once complete. When excel receives these headers, the behavior appears to open some type of “mini-browser” session to make the forms login call. Strangely, these calls have a different user-agent than my installed version of Internet explorer. The mini-browser calls the fake page, and APM redirects to its own forms login page and the user can then login. Once the cookie is set in this mini-browser, excel picks it up and will use it to make all subsequent calls, until the application is closed, thus passing APM authentication. The behavior is that excel mini-browser calls the fake page, login occurs, and then APM directs the mini-browser back to the original page (in this case, “reauthpage?returnurl=/_layouts/15/error.aspx”). This request, since it has an MRHSession cookie, passes APM and is sent to the sharepoint farm. To fix this, I created an additional “if statement” that if the reauthpage page was called, and an MRHSession cookie existed, then respond with a 302 redirect to the actual return URL of /_layouts/15/error.aspx.

APM configuration An additional configuration that also helped was to ensure cookies were set to persistent. This assisted with an additional problem we had using WEBDAV calls to “open in explorer.” Until this was set, WEBDAV calls would never pick up the authenticated cookie. Additionally, we were using “multi domain” in APM, so it was not enough to have the root persistent setting set, you had to set it in each “authentication domain”

when HTTP_REQUEST {
if { [HTTP::uri] contains "/reauthpage" } {
      if { [HTTP::cookie exists "MRHSession"] } {
         HTTP::respond  -version "1.1" content "Authentication successful.  Please close this window to access your document." "connection" "close"
         HTTP::redirect https://[HTTP::host]/_layouts/15/error.aspx
      }
   }
switch -glob [string tolower [HTTP::header "User-Agent"]] {
    "*word*" -
    "*excel*" -
    "*office upload*" -
    "*office existence discovery*" -
    "*office protocol discovery*" -
    "*soap toolkit*" -
    "*ms-office*" -
    "*microsoft office onenote*" -
    "*webdav-miniredir*" -
    "*frontpage*" -
    "*msfrontpage*" -
    "*shareplus*" {
     if {not [HTTP::cookie exists "MRHSession"] } {
          set head1 "X-Forms_Based_Auth_Required" 
            set val1 "https://[HTTP::host]/reauthpage?ReturnUrl=/_layouts/15/error.aspx"
            set head2 "X-Forms_Based_Auth_Return_Url" 
          set val2 "https://[HTTP::host]/_layouts/15/error.aspx"
          HTTP::respond 403 -version "1.1" $head1 $val1 $head2 $val2
      }   
    }
    default {
        switch -glob [string tolower [HTTP::uri]] {
            "*owssvr.dll*" {
       if {not [HTTP::cookie exists "MRHSession"] } {
          set head1 "X-Forms_Based_Auth_Required" 
            set val1 "https://[HTTP::host]/reauthpage?ReturnUrl=/_layouts/15/error.aspx"
            set head2 "X-Forms_Based_Auth_Return_Url" 
          set val2 "https://[HTTP::host]/_layouts/15/error.aspx"
          HTTP::respond 403 -version "1.1" $head1 $val1 $head2 $val2
          }
          }
        }
    }
}
}

Hi Joe,

Does your users get prompted with a pop-up box every-time they use the thick client to open up a document, we are currently experiencing the same issue, it will only occur once for each document type.

We are using 11-4-1 and sharepoint 2013. we hit a bug with 11-4-1 where we couldn't use multiple domain cookies and had to settle with a base domain. which is why maybe we are getting promoted each time to log in, I notice you mentioned something about have cookie specific to each domain

Can you confirm the LTM version you are using thanks and what the users should see as they open documents using the thick client

thanks

John

Hi John,

If you rewrite external URL to internal URL to use Sharepoint AAM, APM will send multiple domain cookie with internal domain name, then the browser will not send it to APM as it does not match the URL Domain... this is not a bug but by design in every version...

to edit office documents in sharepoint, look my answer in the here: https://devcentral.f5.com/questions/sharepoint-and-office-integration-part-2

I tried to resolve any issues I encountered for a 20K concurrent users (3M users) connecting to a sharepoint 2010. (this is not the last and optimized irule version but it was working for every needs)

Hi Stanislas

Thanks for the reply and the additional information, I kind of understand what the iRule is trying to do. I will try to explained the issue we are seeing and if you could verify this will fix the issues we are seeing.

1. first issue we ran into, in 11.4.1 when configuring SSO with multi-domain cookies, APM would authenticate the user but not validate the session and the log-on page (APM) will just go username and password error. The work around from F5 was to configure a base cookie for just the domain (which is what we are currently using).

we are running SharePoint2013 with Apps and OWA running through the same VIP (each one using a different sub-domain).

1. With out any irules the user can open up a word,excel, powerpoint, project, document in OWA (through a browser) but when they tried to open it with the thick client the APM embeds a login page in the application asking to allow JavaScript and authenticate again.

when I used the above IRule posted by Joe, we could open documents in the thick client but a popup box would display asking the user to log in each time, a bit annoying for the user and unacceptable for the customer.

When we did further investigation it looked like it has something to do with the users computers not accepting the web cookie and turning it into a persistent cookie so applications have access to it (persistence is check on the single base cookie).

can you validate what the irule does and the likely hood it would solve our problem.

John Attard

the irule activate Basic authentication which request popup authentication.

I tried to update the irule to support MS-OFBA (Office Form Base Authentication) but I did complete it. The main goal of MS-OFBA is to replace popup by form and manage cookies.

the irule also copy the session cookie in a persistent cookie for office client to not configure the cookie persistent for the whole domain. if this cookie is sent by a non browser user-agent, the cookie is copied back with the name of APM session cookie.