Configure Visio Services data access by using Secure Store in a BI test environment

Article
07/22/2014

Applies to: SharePoint Server 2010

Summary: Read this example for a demonstration of how to connect data in a remote database to a Visio diagram by using an ODC file.

Important

This article is part of the Configuring a BI infrastructure: Hands-on labs series. To complete the steps in this article, you must first have completed:

All steps in the Create a baseline environment for a BI test environment series of articles.
All steps in Configure Visio Services for a BI test environment

Visio Services in Microsoft SharePoint Server 2010 provides two methods of using Secure Store to refresh data in a data-connected Web drawing. One is by using the unattended service account and the other is by using an Office Data Connection (ODC) file. This lab covers the ODC file option. For more information about how to use the unattended service account, see Configure the Visio Services unattended service account in a BI test environment.

Note

ODC files must be created in Microsoft Excel 2007 or Microsoft Excel 2010. You must have Excel together with Microsoft Visio Professional 2010 or Microsoft Visio Premium 2010 installed on the Contoso-Client virtual machine to complete this lab.

Using the unattended service account involves configuring access to your data for an Active Directory account, storing the credentials for this account in Secure Store, and configuring Visio Services to use this account when it needs to refresh the data in a data-connected Web drawing.

The following steps are required to configure the unattended service account in Visio Services.

Configure a data access account
Configure Secure Store
Configure the Visio Services Global Settings

In this lab, we will also create a small sample table in SQL Server and publish a Web drawing with data connected to the SQL Server table.

Video demonstration

This video shows how to set up data access for Visio Services using Secure Store.

Running time: 14:41

Play video Watch the video.

Download video For an optimal viewing experience, download the video.

Right-click the link, and then click Save Target As to download a copy. Clicking the link opens a .wmv file in the default video viewer for full-resolution viewing.

Creating sample data

When configuring data access through Secure Store, you have to grant the account access to the data source to which your Visio Web drawing will be connected. For the purposes of this lab, we will create a SQL Server table. The table contains some simple data which we can use in a data-connected Web drawing which we will publish to a document library in the Business Intelligence Center.

Use the following procedure to create the SQL Server table.

To create a table with sample data

Log on to Contoso-SQL by using the Contoso\SQLAdmin account.
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.
On the toolbar, click New Query.

Copy the following query to the query window:

USE [master]
GO
CREATE DATABASE ServerStatus;
GO
USE [ServerStatus]
GO
CREATE TABLE [dbo].[Status](
[Server] [varchar](50) NULL,
[Status] [bit] NULL
) ON [PRIMARY]
GO
INSERT INTO Status ([SERVER], [Status]) 
VALUES ('Seattle', 'TRUE');
GO
INSERT INTO Status ([SERVER], [Status]) 
VALUES ('New York', 'TRUE');
GO
INSERT INTO Status ([SERVER], [Status]) 
VALUES ('London', 'FALSE');
GO

On the toolbar, click Execute.

With the sample table created, the next step is to create an Active Directory account for use as the data access account.

Configure a data access account

Secure Store maps users designated to have data access to an account that has data access. For this lab, we will create an Active Directory account and give it access to the ServerStatus database that we created in the previous section. Use the following procedure to create the account.

To create an Active Directory account for data access

Log into Contoso-DC as Contoso\Administrator.
Click Start, click Administrative Tools, and then click Active Directory Users and Computers.
Expand the contoso.local node.
Right-click Users, click New, and then click User.
In the Full name and User logon name boxes, type VisioDataAccess.
Click Next.
Type and confirm a password for the account.
Clear the User must change password at next logon check box.
Select the Password never expires check box.
Click Next, and then click Finish.

Once the account has been created, the next step is to grant that account read access to the required data. In a production scenario, you would grant access to whatever data source that you need to access from your Visio Web drawing. In this lab we will grant access to the sample SQL Server that we created earlier. Use the following procedure to create a SQL Server logon and grant that logon data reader access to the ServerStatus table.

To create a SQL Server logon

Log on to Contoso-SQL as Contoso\SQLAdmin.
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
On the Connect to Server dialog box, select Database Engine as the Server type, type Contoso-SQL in the Server name box, and then click Connect.
In Object Explorer, expand Security.
Right-click Logins, and then click New Login.
In the Login name box, type Contoso\VisioDataAccess.
Under Select a page, click User Mapping.
Select the Map check box for the ServerStatus database, and then under Database role membership for: ServerStatus, select the db_datareader check box.
Click OK.

Now that the Contoso\VisioDataAccess account has access to the ServerStatus database that we created, the next step is to configure Secure Store.

Configure Secure Store

Configuring Secure Store involves three steps:

Determine which users should have data access through Secure Store
Create a Secure Store target application that contains those users
Map the target application to the credentials that have data access

Generally, you will want to provide data access to multiple users. The easiest way to manage that is by creating an Active Directory group and then populating that group with the users to whom you want to grant data access.

Use the following procedure to create the Active Directory group.

To create an Active Directory group for user data access

Log on to Contoso-DC using the Contoso\Administrator account.
Click Start, click Administrative Tools, and then click Active Directory Users and Computers.
Expand the contoso.local node.
Right-click Users, click New, and then click Group.
In the Group name box type VisioDataAccessGroup.
Click OK.

Next, we will populate the group with the users to whom we want to grant data access. In this case, we will use the Susan Burk and John Woods accounts. Use the following procedure to populate the VisioDataAccessGroup.

To populate the data access group

In Active Directory Users and Computers, in the users list, double-click the VisioDataAccessGroup group.
On the Members tab, click Add.
On the Select Users dialog box, type Contoso\susan.burk; Contoso\john.woods, and then click OK.

The Members tab should now show Susan Burk and John Woods as members of the group.
Click OK.

The next step is to create a Secure Store target application that contains the VisioDataAccessGroup group. Use the following procedure to create the target application.

To create a target application

Log on to the Contoso-AppSrv virtual machine using the Contoso\FarmAdmin account.
Click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.
On the Central Administration home page, under Application Management, click Manage service applications.
Click the Secure Store Service service application.
On the ribbon, click New.
In the Target Application ID box, type VisioServicesData.
In the Display Name box, type VisioServicesData.
In the Contact E-mail box, type farmadmin@contoso.local.

Note

Although e-mail notifications are not configured in this environment, Contact E-mail is a required field.
In the Target Application Type drop-down list, select Group.
Click Next.
Leave the default credential fields, and then click Next.
On the Specify the membership settings page:
1. In the Target Application Administrators box, type Contoso\FarmAdmin.
2. In the Members box, type Contoso\VisioDataAccessGroup.
3. Click OK.

Now that the target application has been created, we must associate it with the credentials that have data access (the Contoso\VisioDataAccess account). Use the following procedure to set the credentials for the target application.

To set the credentials for the target application

On the Secure Store Service Application page, in the Target Application ID column, point to VisioServicesData, click the arrow that appears, and then click Set Credentials.
In the Windows User Name box, type Contoso\VisioDataAccess.
Type and confirm the password for the Contoso\VisioDataAccess account.
Click OK.

The Secure Store configuration is now complete. In the next section, we will configure data access for the Microsoft Visio user who will be creating data-connected Web drawings.

Grant database access to the diagram author

When Visio Services renders a data-connected Web drawing, it can use Secure Store to refresh the data. However, Excel and Visio do not use Secure Store, but instead require the user to have direct database access to work with an ODC file or a data-connected diagram. Because of this, we must first grant Read access to the ServerStatus database to the user who will create the diagram.

In Configure Visio Services for a BI test environment, we created an Active Directory group named VisioDiagramAuthors for granting database access to diagram authors. We must now grant db_datareader access to that group for the ServerStatus database.

Use the following procedure to grant database access to the Contoso\VisioDiagramAuthors group.

To grant database access to a user

Log on to Contoso-SQL by using the Contoso\SQLAdmin account.
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
Connect to the Database Engine.
In SQL Server Management Studio, expand Security, and then expand Logins.
Right-click the Contoso\VisioDiagramAuthors logon, and then click Properties.
In the pane on the left side, click User Mapping.
Select the Map check box for the ServerStatus database.
In the Database role membership for: ServerStatus list, select the db_datareader check box, and then click OK.

With configuration of database access for the Contoso\VisioDiagramAuthors group complete, the next step is to create an Office Data Connection file.

Create an Office Data Connection file

Visio cannot create an Office Data Connection (ODC) file, so we must create one in Excel, publish it to a data connection library, and then connect to it from Visio.

Use the following procedure to create and publish the ODC file in Excel.

To create and publish an ODC file

Log on to Contoso-Client as Contoso\Susan.Burk.
Click Start, click All Programs, click Microsoft Office, and then click Microsoft Excel 2010.
In Excel, on the Data tab, click From Other Sources, and then click From SQL Server.
In the Server name box, type Contoso-SQL, and then click Next.
From the Select the database that contains the data you want list, select ServerStatus, and then click Next.
On the Save Data Connection File and Finish page, click Finish.

Note

If you are prompted to overwrite the ODC file, click Yes.
On the Import Data dialog box, select the Table option, and then click OK.
On the Data tab, click Connections.
Select the ServerStatus data connection, and then click Properties.
On the Connection Properties dialog box, on the Definition tab, click Authentication Settings.
Select the SSS option, and in the SSS ID box, type the name of the Secure Store target application, VisioServicesData.
Click OK.
On the Connection Properties dialog box, click Export Connection File.
In the File Save dialog box, type http://Contoso-AppSrv/sites/BICenter in the URL box, and then press Enter.

Note

It may take some time for the dialog box to refresh and show the site content.
Double-click Data Connections.
In the File name box, type ServerStatus.odc, and then click Save.
On the Web File Properties dialog box, select Office Data Connection File from the Content Type drop-down list, and then click OK.
On the Connection Properties dialog box, click Cancel.
On the Workbook Connections dialog box, click Close.
Exit Excel.

Note

There is no need to save the Excel workbook. We have exported the ODC file and that is all we need to create a data connection from Visio.

Remain logged on to the Contoso-Client virtual machine as Susan Burk.

Now that the ODC file has been created and published, we can create a data-connected Web drawing.

Create a data-connected Web drawing by using an ODC file

In this section, we create a data-connected Web drawing, and then publish it to the document library in the Business Intelligence center. This helps us confirm that data refresh through Secure Store is working correctly.

Use the following procedure to create the Web drawing.

To create a data-connected Web drawing by using an ODC file

Click Start, click All Programs, click Microsoft Office, and then click Microsoft Visio 2010.
On the Choose a Template page, in the Template Categories section, click Network.
Click Detailed Network Diagram, and then click Create.
On the ribbon, click the Data tab, and then click Link Data to Shapes.
On the Data Selector page of the wizard, click Previously created connection, and then click Next.
On the Select Data Connection page, click Browse.
On the Existing Connections dialog box, click Browse for More.
In the Data Selector dialog box, type http://Contoso-AppSrv/sites/BICenter in the URL box, and then press Enter.

Note

It may take some time for the dialog box to refresh and show the site content.
Double-click Data Connections.
Click the ServerStatus data connection, and then click Open.
On the Select Data Connection page, click Finish.
On the blank drawing page:
- Drag three Server icons from the Shapes area onto the page.
- Drag each row from the data table in the External Data section to one of the shapes.
  
  Each shape should now have a data graphic connected to it indicating the server name and status (1 or 0).

You should now have a diagram similar to the following.

Image of servers with data callouts

The next step is to publish the diagram as a data-connected Web drawing and then confirm that data refresh is working correctly.

Use the following procedure to publish the drawing.

To publish a data-connected Web drawing

Click File, and then click Save & Send.
Click Save to SharePoint.
Under Locations, click Browse for a location.
Under File Types, click Web Drawing.
Click Save As.
On the Save As dialog box, type http://Contoso-AppSrv/sites/BICenter in the location box at the top, and then press Enter.

Note

It may take some time for the dialog box to refresh and show the site content.
In the All Site Content list, double-click Documents.
In the File name box, type Server Status, and then click Save.

The Web drawing will render in a browser window.
When the drawing is displayed, if a Refresh Disabled warning is displayed, click Enable (always).

Note the server statuses displayed:
- Seattle – Online (1)
- New York – Online (1)
- London – Offline (0)
Leave this browser window open for the data refresh test procedure that follows.

The rendered Web drawing shows the data currently in the ServerStatus database. To test the data refresh mechanism, we can modify the data in the Status table in SQL Server and then refresh the Web drawing to see the change.

Use the following procedure to modify the data in SQL Server and then refresh the Web drawing.

Note

Remain logged on to Contoso-Client as Susan Burk while you do this procedure.

To test Visio Services data refresh

Log into Contoso-SQL using the Contoso\SQLAdmin account.
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.
In the toolbar, click New Query.