Introduction
This is a follow-up post on the SharePoint 2013, Office Web Apps 2013 and Excel files with Data Connections post that I previously wrote. That post talked about how you needed to do, so called, WOPI Suppressions if you had Excel files with Data Connections and had those data connections configured to use the authenticated users account. The WOPI Suppression made sure that the rendering of the Excel book was done by Excel Services in SharePoint 2013 rather than with Office Web Apps 2013.
But if you have Excel documents with external data and do not rely on delegating the account (and don’t do the Negotiate, SPN and delegate dance), but instead like to store the credentials to the data in the connection string (not recommended) or like to take advantage of the Secure Store in SharePoint 2013 – then you actually have the option to not do any WOPI Suppressions. Let’s take a look on how you can get this to work: an Excel sheet with an external data connection using Secure Store to store credentials and then using Office Web Apps 2013 to refresh the data.
Configuring the Secure Store
First of all we need to have the Secure Store Service Application up and running in our SharePoint farm. Once that is done and you have generated the encryption key, we need to add a new Target Application. To create the Target Application use the New button in the Ribbon. This will take you to a wizard. First of all we need to give the Target Application an ID, this is what we later use to reference this application from Excel (and other sources). Also give it a descriptive name as well as a contact. Then we have to choose what type of Target Application Type to use. Use Individual or Group, where Group is preferred from a management perspective. If you choose Individual you have to set the credentials for each user individually and for Group you have one set of credentials per application.
On the next wizard page you set up the “fields” for the application. By default it assumes that it is Windows credentials that we would like to store, if that is the case you only need to click Next. Otherwise, if you’re using for instance SQL credentials, you have to set up two new fields using the User Name and Password type of fields. On the final page you give permissions to the Target Application itself and if it is a Group application then you also configure what users/groups that are mapped to the credentials.
When the application is configured we need to set the credentials for it. For a Group Application you use Central Administration or PowerShell to set the credentials to be used by the group of users. For Individual Applications you need to set the credentials for each and every user through Central Admin or create some custom Web Part or similar that the user can use to fill in it’s credentials. Fortunately most of the plumbing is already done for this. You can use the Layouts page called securestoresetcredentials.aspx to allow the user to set their credentials. This is how it is used:
/_layouts/15/securestoresetcredentials.aspx?TargetAppId=AdventureWorks
You have to pass the TargetAppId as an argument and set the value to the Application ID of the application. Good to know here is that if you don’t use SSL/TLS (HTTPS) on you SharePoint sites you will get messages that will annoy your users, since this data will be sent in clear text. Just another reason to do HTTPS :-)
By now we should be all set to continue to the next step.
Create the Excel book with the Data Connection
Now it is time to create our Excel book. To do this we just add a data connection as normal but in the connection wizard we click on the Excel Services: Authentication button and then choose to use a stored account. In the stored account input box we have to enter the Secure Store Target Application ID, the one we created in the Secure Store Service Application above. Everything else is precisely as normal.
Now this workbook is ready to be uploaded to a document library in SharePoint. Good thing here is that now you don’t have to do anything more – no SPN’s, no nothing!
Refresh data using Office Web Apps 2013
So, let’s see if this works with Office Web Apps 2013! If you fiddled with the WOPI Suppressions as in my previous blog post, make sure to remove them so we have WAC rendering the Excel Sheet instead of Excel Services (this will work in Excel Services as well, but that is not the purpose of this post).
Click on the document so that it renders in the browser, make sure to update some data in the external data source so that you can see that the data is actually refreshed, then use Data > Reload All Data Connections to update the sheet. Everything should work fine here, your data should be refreshed.
So, there it is you can actually use Office Web Apps 2013 to render and refresh external data – only thing is that you can’t use any delegation features.
Troubleshooting
Didn’t it work? Of course things can go wrong. But a correctly configured environment should work immediately. Here are some of the common errors that you can get:
External Excel data connections disabled in Office Web Apps
Your admins (or you) might have turned of the Excel external data. You can check this by running the following command on a WAC machine:
(Get-OfficeWebAppsFarm).ExcelAllowExternalData
If it returns false, then you have turned it off. By default when configuring a new farm it is set to true. If set to false and you want to use external data then use the following command:
Set-OfficeWebAppsFarm -ExcelAllowExternalData
Note that it can take a couple of minutes before changes like this actually is propagated in the WAC farm.
If this is your problem then you’re getting an error like this:
“The trusted location where the workbook is stored does not allow external data connections.”
You’re running SharePoint over HTTP!!!
The most common problem is that your SharePoint is still using HTTP and not HTTPS (when will you ever learn!). If that is the case you will get an error like follows:
“An error occurred while accessing application id XXX from Secure Store Service.”
This is perfectly normal and good! Since the SharePoint farm is accessible through HTTP this also means that Office Web Apps Server will try to retrieve the credentials over HTTP – in clear text, and you don’t want that.
But if you are running a development environment that uses HTTP it could be all right to work around this. Or you might be running IPSec (fat chance…) then it also ok to allow this. Otherwise I do recommend you to change your SharePoint content web application to use HTTPS or don’t just use the Secure Store and WAC.
Anyhow, this is how you configure WAC to allow retrieval of credentials over HTTP:
Set-OfficeWebAppsFarm –AllowHttpSecureStoreConnections
Once this command is executed, wait a minute, reload the workbook and you’re golden.
No credentials configured
Another common problem, common when using Individual Secure Store applications, is that the application does not have any credentials set. The error you will see then is the following:
“An error occurred during an attempt to establish a connection to the external data source.”
To fix this you have to set the Group credentials, if it is a Group application type, or make sure that all users set their individual credentials.
Summary
You have now seen that Office Web Apps 2013 can actually render external data, including refreshing it, using the SharePoint 2013 Secure Store service. Just be careful with the security issues I highlighted with SSL/TLS/HTTPS. All this will also work with plain ol’ Excel Services if you need to combine it with delegation.