Use Visio Services with Secure Store

Article
12/09/2016

Applies to: SharePoint Server 2010

Summary: Secure Store can be used to store encrypted credentials for use in refreshing data-connected Visio Web drawings in Visio Services.

Visio Services in Microsoft SharePoint Server 2010 can be configured to use the Secure Store Service to provide user authentication for data-connected Web drawings that use an external data source such as Microsoft SQL Server.

Note

This article assumes that you have already deployed a Secure Store Service Application. If you have not deployed Secure Store, see Plan the Secure Store Service (SharePoint Server 2010) and Configure the Secure Store Service (SharePoint Server 2010).

Secure Store provides a method of mapping users who do not have direct data access to an account that does have data access. Secure Store and Visio Services work together in the following basic sequence of events:

A user accesses a data-connected Web drawing on a SharePoint site.
Visio Services passes the user’s identity to the Secure Store Service.
Secure Store determines whether the user is authorized to access the data. If so, Secure Store returns the data access credentials to Visio Services.
Visio Services impersonates the data access credentials, accesses the data, and displays the data to the user.

Visio Services provides two primary methods of using Secure Store to provide data access:

Unattended Service Account You can configure a single data access account to provide data access to all users. This option is useful for providing broad access to data that is not considered sensitive.
Group Mappings You can map a specific group of users to a data access account. This option is useful for providing data access to a particular group of users. You can create as many group mappings as you need to provide the needed data access for the users.

Microsoft Visio Professional 2010 and Microsoft Visio Premium, which are used to create the Web drawings, do not use the Secure Store Service for data authentication. You must configure direct data access for Web drawing authors. Once the Web drawing has been published to a SharePoint site, Visio Services can use Secure Store when it renders the Web drawing.

Video demonstrations

These video demonstrations show the procedures discussed in this article.

Configure the Visio Services unattended service account

This video shows the procedures involved in configuring the unattended service account for Visio Services.

Video: CConfigure the Visio Services unattended service account

Use Visio Services with Secure Store group mappings

This video shows the procedures involved in using Visio Services with Secure Store group mappings.

Video: Use Visio Services with Secure Store group mappings

Configuring Visio client data access

Users of Visio Professional and Visio Premium must be granted direct database access in order to link data to shapes in Visio. For ease of administration, we recommend that you use Active Directory groups:

Create an Active Directory group that contains the users to whom you want to give access to a given data source.
Grant that group data reader permissions on the data source.

You can create as many groups as needed and map them to your data sources in whatever combination meets your needs.

The following procedure describes how to map an Active Directory group to a SQL Server data source.

To create a SQL Server logon for an Active Directory group

In SQL Server Management Studio, connect to the database engine.
In Object Explorer, expand Security.
Right-click Logins, and then click New Login.
Click Search.
In the Select User or Group dialog box, click Object Types.
In the Object Types dialog box, select the Groups check box, and then click OK.
On the Select User or Group dialog box, in the Enter the object name to select box, type the name of the Active Directory group for which you are creating the logon, and then click OK.
Under Select a page, click User Mapping.
Select the Map check box for the database to which you want to give the Active Directory group data access, and then under Database role membership for: <database>, select the db_datareader check box.
Click OK.

Once data reader access has been granted to your diagram authors, they will be able to connect to the data source in Visio.

Configuring the Unattended Service Account

Configuring an unattended service account for Visio Services consists of the following steps:

Creating an account to use for data access
Creating a logon for the data access account on the data source
Creating a Secure Store target application that uses the data access account credentials
Configuring Visio Services to use the Secure Store target application for the unattended service account

The first step is to create an account to use for data access. Have your domain administrator create an Active Directory account that you can use to access your data sources.

Important

Visio Services requires a Windows Active Directory account for the unattended service account. You cannot use a non-Windows account.

Once the account has been created, follow these steps to create a logon for the data access account in SQL Server. (If you are using a data source other than SQL Server, see the instructions for your data source to create a logon with data read permissions for the data access account.)

To create a SQL Server logon for the data access account

In SQL Server Management Studio, connect to the database engine.
In Object Explorer, expand Security.
Right-click Logins, and then click New Login.
In the Login name box, type the name of the Active Directory account that you created for data access.
In the Select a page section, click User Mapping.
Select the Map check box for the database that you want to provide access to, and then under Database role membership for: <database>, select the db_datareader check box.
Click OK.

To create a target application for the unattended service account

On the SharePoint Central Administration Web site home page, in the Application Management section, click Manage service applications.
Click the Secure Store Service service application.
On the ribbon, click New.
In the Target Application ID box, type an ID for the target application (for example, VisioServicesUnattended).
In the Display Name box, type a name for the target application.
In the Contact E-mail box, type an e-mail address.
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 the account of the user who will administer this account.
  
  Note
  
  You can type multiple names or the name of an Active Directory group that contains the users whom you want to administer this target application.
2. In the Members box, type All Authenticated Users.
  
  Note
  
  The unattended service account is intended for granting broad database access. You can restrict the users who have access to the unattended service account to a specific Active Directory group if you want, but be aware that only one unattended service account can be created per Visio Services service application.
3. Click OK.

Once the target application has been created, you must set the target application to use the credentials for the data access account that you created. Use the following procedure to set the credentials.

To set the credentials for the target application

On the Secure Store Service Application page, in the Target Application ID column, point to the target application that you just created, click the arrow that appears, and then click Set Credentials.
In the Windows User Name box, type the Active Directory account that you created for data access.
Type and confirm the password for the account.
Click OK.

The target application configuration is now complete. The next step is to designate this target application for use as the unattended service account in Visio Services. Use the following procedure to configure the unattended service account in Visio Services Global Settings.

To configure Visio Services Global Settings

On the Central Administration home page, under Application Management, click Manage service applications.
On the Manage Service Applications page, click the Visio Services service application.
On the Manage the Visio Graphics Service page, click Global Settings.
On the Visio Graphics Service Settings page, in the External Data section, in the Application ID box, type the ID of the Secure Store target application that you just created.
Click OK.

Once you have configured the External Data setting in Visio Services Global Settings, the unattended service account is configured and ready to use. When you create a data connected Web drawing in Microsoft Visio Professional 2010 or Microsoft Visio Premium, you can publish it to a SharePoint site and render it with Visio Services and the unattended service account will be used to refresh the data.

The following procedure describes how to create a data-connected Web drawing in Visio and publish it to a SharePoint site.

To create a data-connected Web drawing for use with the unattended service account

Start Microsoft Visio Professional 2010 or Microsoft Visio Premium.
Select a template and click Create, or open an existing diagram.
On the Data tab, click Link Data to Shapes.
Follow the data connection wizard to connect to your data source.
Link the data to your shapes by dragging data rows to shapes on the page.
When the drawing is complete:
1. Click File, click Save & Send, and then click Save to SharePoint.
2. Choose a location from the list under Recent Locations, or select Browse for a location.
3. In the File Types section, click Web Drawing, and then click Save As.
4. Save the Web drawing to a SharePoint document library.

Once the Web drawing has been saved to a SharePoint document library, users can access it and the unattended service account will be used to refresh the data.

Configuring group mappings in Secure Store

You can map a specific group of users to a specific data source by using group mappings in Secure Store. This provides more fine-grained security than using the unattended service account. Creating a group mapping consists of the following steps:

Creating an account to use for data access
Creating a logon for the data access account on the data source
Creating a Secure Store target application that uses the data access account credentials
Creating an Office Data Connection (ODC) file by using Microsoft Excel and publishing it to a SharePoint data connection library
Using the ODC file as a data source in Visio and linking the data to shapes in a diagram.

The first step is to create an account to use for data access. Have your administrator create an Active Directory account that you can use to access your data sources.

Note

You can also use a SQL Server logon with SQL Server authentication. For information about how to use SQL Server with Secure Store, see Use Secure Store with SQL Server Authentication (SharePoint Server 2010).

To create a SQL Server logon for the data access account

In SQL Server Management Studio, connect to the database engine.
In Object Explorer, expand Security.
Right-click Logins, and then click New Login.
In the Login name box, type the name of the Active Directory account that you created for data access.
Under Select a page, click User Mapping.
Select the Map check box for the database that you want to provide access to, and then under Database role membership for: <database>, select the db_datareader check box.
Click OK.

Once you have created a logon for the data access account and granted the account access to your data source, you must create a target application in Secure Store to contain the credentials for the data access account. This target application will be used to map the data access account to the users to whom you want to grant data access.

When you create the target application, you will be able to specify individual users to whom you want to grant data access, or you can specify an Active Directory group. For ease of administration, we recommend that you use an Active Directory group. This allows you to update the user list in the future without having to update the target application.

Use the following procedure to create the target application.

To create a target application

On the SharePoint Central Administration Web site home page, in the Application Management section, click Manage service applications.
Click the Secure Store Service service application.
On the ribbon, click New.
In the Target Application ID box, type an ID for the target application (for example, VisioServicesDataAccess).
In the Display Name box, type a name for the target application.
In the Contact E-mail box, type an e-mail address.
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 the account of the user who will administer this account.
  
  Note
  
  You can type multiple names or the name of an Active Directory group that contains the users whom you want to administer this target application.
2. In the Members box, type the names of the users to whom you want to give data access or the name of the Active Directory group that contains those users.
3. Click OK.

To set the credentials for the target application

On the Secure Store Service Application page, in the Target Application ID column, point to the target application that you just created, click the arrow that appears, and then click Set Credentials.
In the Windows User Name box, type the Active Directory account that you created for data access.
Type and confirm the password for the account.
Click OK.

In order for Visio Services to use the Secure Store target application that you just created, it must reference that target application by using an ODC file. Because Visio cannot create an ODC file, you must create the ODC file in Microsoft Excel, save it to a SharePoint data connection library, and then connect to it from Visio.

Use the following procedure to create an ODC file and publish it to a data connection library.

To create and publish an ODC file

In Excel, on the Data tab, click From Other Sources, and then click From SQL Server.
In the Server name box, type the name of the instance of SQL Server where your data is located, and then click Next.
From the Select the database that contains the data you want list, select the database that you want to connect to, and then click Next.
On the Save Data Connection File and Finish page, click Finish.
On the Import Data dialog box, select the Table option, and then click OK.
On the Data tab, click Connections.
Select the data connection that you are using, 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 that you want to use with Visio Services.
Click OK.
On the Connection Properties dialog box, click Export Connection File.
In the File Save dialog box, type the URL of a data connection library in the URL box, and then press Enter.
In the File name box, type the name that you want to use 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.

Once you have saved the ODC file, you can use it as a data source for linking data to shapes in a Visio diagram. Use the following procedure to create a data-connected Web drawing by using an ODC file.

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

In Visio, open a diagram or create a new diagram.
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, in the URL box, type the URL of the data connection library where you saved the ODC file, and then press Enter.
Select the ODC file and then click Open.
On the Select Data Connection page, click Finish.
Connect the data to the shapes in your diagram.
When you are ready to save the drawing, click File, and then click Save & Send.
Click Save to SharePoint.
Choose one of the existing locations under Recent Locations or click Browse for a location.
Under File Types, click Web Drawing.
Click Save As.
On the Save As dialog box, type the location of the SharePoint document library in the location box at the top, and then press Enter.
In the File name box, type the name that you want to use and then click Save.

Once the Web drawing has been published, it is available to view by using Visio Services. When the data in the Web drawing is refreshed, it uses the Secure Store target application that you specified in the ODC file.