If you did not read Part 1 of this series, do so now as it will help ensure that you have Visio Services, Excel Services and Secure Store Service configured properly.
There are a few scenarios that you can configure in order to publish Visio diagrams to Visio Services that can be refreshed from SQL Server using SQL Authentication.
Before we dive into these scenarios I want to make sure that you have a Data Connection Library configured on your SharePoint site. A Data Connection Library is a secure location used to store Office Data Connection (ODC) files. This is a standard library type in SharePoint 2010 and can easily be created using the Data Connections Library template when you choose to create a new library.
I simply named mine Data Connections and added it to my Quick Launch bar for easy access.
You also need to configure a target application in Secure Store Service (SSS) that will map users to a SQL Login. The steps to do this are covered in Part 1 of this series where we create the Unattended target application. Use the same steps but choose a unique name for this additional target application. I try to use a name that reminds me of the authentication type and credentials that are configured for the target application.
Don’t forget to set the credentials on this target application after creating it, this is a common mistake. And remember this is a SQL Authentication scenario so set the user name and password to a valid SQL Login.
The credentials that I set for the target application are also setup as a SQL Login in my SQL server and assigned to the dbreader role for the databases I wish to refresh from to ensure that this login can only read data from my tables.
This is a recommended practice as these is no reason to have additional permissions as Visio Services cannot update the tables, so this will reduce your security risk for this account. I recommend that you have a specific account that is used specifically for Visio Services and Excel Services data refresh scenarios. Doing so will make it easier to manage access and debug connectivity issues.
Using ODC files created with Excel
Why start with Excel? Excel’s data connection wizard gives you additional options for specifying advanced authentication options and the ability to export the connect to a different location. There are a few articles on the web outlining this procedure but I thought it would be helpful to describe all the steps that I follow when configuring data refreshable diagrams for Visio Services.
To create the ODC file in Excel:
Now that you have configured the ODC file and it is being used properly by Excel Services you can simply connect your Visio Diagram to the same ODC file. To do this, open your Visio diagram in Visio 2010 Professional or Premium and start the Data Linking wizard by clicking the Link Data to Shapes button on the Data tab in the ribbon.