Refreshing diagrams from SQL Server using Windows Authentication

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.

Prerequisites

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:

1. Start Excel 2010
   
2. From the Data tab choose the From Data Connection Wizard option under the From Other Sources list.
   
   
3. In the connection dialog
   1. Enter the name of your SQL server
   2. Leave the default option to Use Windows Authentication selected
   3. Click Next
      
      
4. Select the table you wish to import data from and click Next
   
   
5. On the Save Data Connection file and finish screen just click the Finish button.  This will save the ODC file locally to your machine.  We will soon modify this ODC file and upload it to our SharePoint site.
   
   
6. In the Import Data dialog choose the PivotTable Report option and click OK.
   The reason for this is we are going to save this workbook to our SharePoint site as a test to ensure Excel Services can refresh the workbook but Excel Services will not refresh a Table so we need to choose a Pivot in this scenario.
   
   
7. Configure the Pivot any way you wish.
   From my data source I will choose Location + MachineName + Status
   
   
8. Now we need to update the ODC file and export it to our SharePoint site.  To do this click on the Connections button on the Data tab.
   
   
9. In the Workbook Connections dialog select the connection that we just configured and click on the Properties button
   
   
10. In the Connection Properties dialog
    1. Select the Definition tab
    2. Click on the Authentication Settings… button
    3. Choose the SSS option
    4. Enter the name of the target application that you configured in SSS for Windows Authentication
    5. Click OK
       
       
11. Now that the ODC file is configured to use our SSS target application, it is time to export the file to our SharePoint site.  Click on the Export Connection File… button.
    
12. Browse to your Data Connection library (mentioned at the beginning of this article) and save the ODC file in this location.
    
    
    When complete you will see the Connection file property change from a local path to the path of your Data Connection library on your SharePoint site.
    
    
13. Click OK and then Close to save these changes to the workbook.
    
14. At this point the workbook is connected to SQL server using Windows Authentication and will pass the current users credentials when you click the refresh button.  To verify this open SQL Profiler and watch the entries.  You will see an entry for your query from the client.  Notice the ApplicationName column and the LoginName column.
    
    Note: The client is connecting to the data source so the current user’s credentials will be used at this point. The client does not use the credentials set for the specified SSS target application as SSS is specifically for SharePoint service applications, not clients.
    
    
15. Now it is time to verify that Excel Services can refresh this workbook.  Why?  Because it is an easy next step since we already have the ODC file and the workbook configured.  If Excel Services will not work, most likely Visio Services will not work either.
    Click Save As and browse to your document library and save the workbook.
    
16. Once the workbook is saved navigate to the document library in your browser and click on the workbook’s link.  Excel Services should render the workbook in your browser.
    
    
17. To trigger a refresh of the Pivot make sure your cursor is in a cell that participates in the pivot then choose the Refresh All Connection option from the Data button on the ribbon.
    
    
18. Again, if you monitor SQL Profiler you will see the LoginName being used is the one that you set for the target application in SSS.
    

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.

1. In the first screen of the wizard you just need to select the last option, Previously created connection,
   
   and then browse to the ODC file that you saved to your Data Connection Library on your SharePoint site.
   
   
2. After you finish stepping through the rest of the data linking wizard, the data from your SQL server will be imported to the diagram and displayed in the External Data window.
   
   Note: The client is connecting to the data source so the current user’s credentials will be used at this point. The client does not use the credentials set for the specified SSS target application as SSS is specifically for SharePoint service applications, not clients.
   
3. From the External Data window, drag and drop a few records on a few of the shapes in your diagram.
   This is an important step because Visio Services will not refresh the data record set in the published diagram unless there is at least one record linked to one shape in the diagram.
   
   Note: If you need a data linking tutorial check out this article, http://office.microsoft.com/en-us/visio-help/add-imported-data-to-shapes-HA010131383.aspx
   
4. Data Graphics are also configured for you by default.  Without Data Graphics applied to your shapes you will not see any visual indication in your diagram that data has actually been refreshed.
   For my example I added a Color by Value item based on the Status column in my table to the default Data Graphic that was assigned to my shape when I linked a record to it from the External Data window.
   Note: If you need a tutorial on Data Graphics check out this article, http://blogs.msdn.com/b/visio/archive/2009/10/13/data-graphics-in-visio-2010.aspx
   
   
   
5. Now I can save this diagram to my SharePoint site using the new VDW file format.  Once saved, I can click the link to the diagram and it will render in my browser.
   
   
6. When you click on the Enable button in the Refresh warning message bar the diagram will refresh using the Windows account credentials that you set for the target application in SSS.
   You can verify this using SQL Profiler.

Using ODC files created with Excel – version 2

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

1. Run the Link Data to Shapes wizard and choose the SQL Server option.
   
2. You will be prompted for credentials and again you must use the Windows Authentication option.  Remember this is the client so your credentials will be passed by the client to establish the connection.
   
3. After you complete the wizard and all the steps to link data to your shapes, including configuring Data Graphics, save the Visio file using the VDW file format to your SharePoint site.  When the diagram is rendered and refreshed in the browser, Visio Services will default to the Unattended account that you configured in SSS.  The Unattended target application is configured to use a Windows account so these credentials will be passed to SQL during the refresh operation.