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 Windows 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 SSS that will map users to a Windows account. These 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 Windows Authentication scenario so set the user name and password to a valid Windows account.
The credentials that I set for the target application are also setup in my SQL server and assigned to the dbreader role 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.
Now let’s review the scenarios.
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.
Instead of specifying a target application configured in SSS you can choose to default the connection to the target application specified by the Unattended account option in the Visio Services and Excel Services global settings from Central Administration, which we configured in Part 1 of this article.
The steps to create this type of ODC file are the same as above with one exception. In step 10 above, instead of selecting the SSS option for authentication, choose the None option.
This is the only difference. Now when you monitor refresh activity using SQL Profiler you will see the LoginName reflecting the Windows account credentials that you set for the Unattended target application in SSS.
No ODC file?
I recommend using ODC files for data refresh in both Visio Services and Excel Services for most scenarios. However, you can connect a Visio diagram directly to SQL and publish it to SharePoint for Visio Services to refresh without using an ODC file.
To do this
I am trying to do something similar where I want to create a network map that pings all of our remote locations and shows a green indicator for online or red for down next to the server on the map. Would you script ping to save to an excel file and pull the data from excel or do you have better recommendations of getting network statistics. I use IPmonitor for network monitoring but it uses its own proprietary database which stinks.
Check out this article with a sample that should show you how to do just that.