Visio Services External Data Refresh Part 1

It is no secret that Visio allows you to connect the shapes in your diagrams to a variety of data source, most of which are also compatible with Visio Services once your diagram is hosted in a SharePoint 2010 document library. What seems to be a secret is how to configure Visio Services to refresh from external data sources such as SQL Server.

Excel Services and SharePoint lists are probably the most popular data sources to use with Visio Services for the simple fact that there is no additional configuration needed. You simply connect the shapes in your diagram to a workbook hosted in a SharePoint 2010 document library or a SharePoint 2010 List, save your diagram to a SharePoint 2010 document library using the new Web Drawing file format and the diagram will automatically refresh from the connected workbook or list automatically when viewed in the browser.  These scenarios both use the credentials of the current user to refresh from the workbook or the list based on permissions defined for that user within the SharePoint site/farm.

When connecting to an external data source, such as SQL Server, there are a number of additional steps, including configuring authentication, that need to be completed prior to connecting your diagram to the data source and publishing it.

For this article let’s complete the first and most basic configuration step…

Setting up the Unattended Service Account for the Visio Graphics Service service application

I am not going to explain all the complexities of Secure Store Service in SharePoint 2010, but I will tell you that SSS is a service application that is used to map users to specific credentials for accessing external data sources. If you need more detailed information on Secure Store Service feel free to review this article on MSDN, https://msdn.microsoft.com/en-us/library/ee557754.aspx.

In order for Visio Services to refresh data from any external data source the Unattended Service Account must be configured. If an unattended service account is not properly configured, data connections that use a non-Windows SSO authentication method or no authentication method will fail. So if you publish a Visio diagram that is connected to a SQL table and you get this error…

there is a good chance that you have not configured the unattended service account for Visio Services.

Note: this also holds true for the workbooks refreshed using the Excel Services service application.

To setup a new target application in SSS follow these steps…

Note: there is also a nice video on TechNet that demonstrates this process

1.  Visit your Central Administration site and navigate to the Manage Service Applications page.

2.  From the Manage Service Applications page click on Secure Store Service link from the list of service applications.

3.  Create a new Target Application buy clicking on the New button from the Ribbon.

Note: You may first have to generate a key that is used by the configuration database to secure the target applications stored in SSS. Details for this are located in this article, https://technet.microsoft.com/en-us/library/ee806866.aspx .

4.  Enter the properties for the Target Application

• For the Target Application ID choose a unique name. This is the ID that we will apply to the setting for Visio Services.
• For the Display Name choose a unique name. This is only used for display purposes. I typically use the same name for both so that I do not get confused ;-)
• Enter a valid email address for the person that administrators this Target Application.
• For Target Application Type choose Group. This will allow us to define a group from our domain that will map to a single set of credentials that we configure for this target application.
• Leave Target Application Page URL set to None.
  
• Click OK.

5.  On the next step we define the type of credentials that will be assigned to this target application.

Leave the default Windows User Name and Windows Password field names and field types set because for the Unattended Account you need to specify a valid Windows account.

6.  For Target Application Administrators, enter the user(s) that are allowed to make changes to this Target Application.

For Members, enter the user(s) or group(s) that will be mapped to the credentials that we will set for this Target Application.

7.  Click OK and the new Target Application will be created.

8.  Select the new Target Application from the list and click on the Set Credentials button from the Ribbon.

9.  Enter the Windows username and the password for the Windows account that you want to be used by the Unattended Account and click OK.

These credentials must be set to a valid Windows account or errors will occur from any external data source refresh scenario, even scenarios that do not fall back to the Unattended Account.

10.  At this point the target application is created and configured. Now you can set the Unattended Service Account setting under the Global Settings for the Visio Graphics Service service application to the name of this new target application.

11.  I also suggest that you set the Unattended Service Account setting for the Excel Services service application at the same time. This way both service applications are properly configured for external data refresh scenarios and this gives you the ability to test the data source from two different applications.

12.  Excel Services requires additional configuration beyond just setting the Unattended Service Account setting in Global Settings from the Central Administration site. In order for Excel Services to refresh from ODC files located in a Data Connection Library we have to tell Excel Services to trust the library. This is easily accomplished by adding the library URL to the list of Trusted Data Connection Libraries.

Note: The beginning of this video on TechNet demonstrates how to configure each of these settings.

Configuration Complete

Now your Visio Services service application (and Excel Services) is ready for external data refresh.

In a future articles I will describe specific scenarios that walk you through setting up Excel workbooks and Visio diagrams that both connect to external data source and refresh in SharePoint.