Configure the Visio Services unattended service account in a BI test environment

 

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:

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 unattended service account option. For more information about how to use an ODC file, see Configure Visio Services data access by using Secure Store in a BI test environment.

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 the unattended service account for Visio Services.

Image of video

Running time: 15:49

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 you configure the unattended service account, 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 that we can use in a data-connected Web drawing which we will publish to the SharePoint document library in the Business Intelligence Center.

Use the following procedure to create the SQL Server table.

To create a table with sample data

  1. Log on to Contoso-SQL by using the Contoso\SQLAdmin account.

  2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

  3. On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.

  4. On the toolbar, click New Query.

  5. Copy the following query to the query window:

    USE [master]
    GO
    CREATE DATABASE CorporateDirectory;
    GO
    USE [CorporateDirectory]
    GO
    CREATE TABLE [dbo].[Office](
    [Name] [varchar] (50) NULL,
    [Office] [varchar] (50) NULL
    ) ON [PRIMARY]
    GO
    INSERT INTO Office ([Name], [Office]) 
    VALUES ('John Woods', '1501');
    GO
    INSERT INTO Office ([Name], [Office]) 
    VALUES ('Susan Burk', '1502');
    GO
    INSERT INTO Office ([Name], [Office]) 
    VALUES ('Cindy White', '1503');
    GO
    INSERT INTO Office ([Name], [Office]) 
    VALUES ('David Hamilton', '1504');
    GO
    
  6. On the toolbar, click Execute.

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

Configure a data access account

The unattended service account requires an Active Directory account for data access. Use the following procedure to create the account.

To create an Active Directory account for data access

  1. Log on to Contoso-DC as Contoso\Administrator.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the contoso.local node.

  4. Right-click Users, click New, and then click User.

  5. In the Full name and User logon name text boxes, type VisioUnattended.

  6. Click Next.

  7. Type and confirm a password for the account.

  8. Clear the User must change password at next logon check box.

  9. Select the Password never expires check box.

  10. 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 you need to access from your Visio Web drawing. In this lab we will grant access to the sample SQL Server database that we created earlier. Use the following procedure to create a SQL Server logon and grant that logon data reader access to the CorporateDirectory database.

To create a SQL Server logon

  1. Log on to Contoso-SQL as Contoso\SQLAdmin.

  2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

  3. 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.

  4. In Object Explorer, expand Security.

  5. Right-click Logins, and then click New Login.

  6. In the Login name box, type Contoso\VisioUnattended.

  7. Under Select a page, click User Mapping.

  8. Select the Map check box for the CorporateDirectory database, and then under Database role membership for:CorporateDirectory, select the db_datareader check box.

  9. Click OK.

With the account created and granted the appropriate data access, we can now configure Secure Store.

Configure Secure Store

Secure Store uses a target application to define connection parameters and authorized users. The unattended service account is generally considered a catch-all account that is used for general data access, so usually all users are granted access to the unattended service account through the target application.

Use the following procedure to create a target application for the unattended service account.

To create a target application for the unattended service account

  1. Log in to Contoso-AppSrv using the Contoso\FarmAdmin account.

  2. Click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.

  3. On the SharePoint Central Administration Web site home page, in the Application Management section, click Manage service applications.

  4. Click the Secure Store Service service application.

  5. On the ribbon, click New.

  6. In the Target Application ID box, type VisioServicesUnattended.

  7. In the Display Name box, type VisioServicesUnattended.

  8. 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.

  9. In the Target Application Type drop-down list, select Group.

  10. Click Next.

  11. Leave the default credential fields, and then click Next.

  12. On the Specify the membership settings page:

    1. In the Target Application Administrators box, type Contoso\FarmAdmin.

    2. In the Members box, type All Authenticated Users.

      Note

      In a production scenario, you would typically include all users here. 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 specify which credentials should be used by it. The target application credentials are those to which data access has been granted — in our case, the Contoso\VisioUnattended account to which we granted db_datareader access to the CorporateDirectory database.

Use the following procedure to set the credentials for the target application.

To set the credentials for the target application

  1. On the Secure Store Service Application page, in the Target Application ID column, point to VisioServicesUnattended, click the arrow that appears, and then click Set Credentials.

  2. In the Windows User Name box, type Contoso\VisioUnattended.

  3. Type and confirm the password for the Contoso\VisioUnattended account.

  4. Click OK.

With the credentials set for the target application, the Secure Store configuration for the unattended service account is complete. The next step is to configure Visio Services to use this target application for the unattended service account.

Configure Visio Services

The unattended service account configuration is part of the Visio Services Global Settings. Use the following procedure to configure the unattended service account in Visio Services.

To configure Visio Services Global Settings

  1. On the Central Administration home page, under Application Management, click Manage service applications.

  2. On the Manage Service Applications page, click Visio Services.

  3. On the Manage the Visio Graphics Service page, click Global Settings.

  4. On the Visio Graphics Service Settings page, in the External Data section, in the Application ID box, type VisioServicesUnattended.

  5. Click OK.

With the Visio Services Global Settings configured, setup of the unattended service account is complete. In the next section, we will create a data-connected Web drawing, publish it to a SharePoint document library, and render it by using Visio Services. This enables us to test data refresh by using the unattended service account.

Grant database access to the diagram author

When Visio Services renders a data-connected Web drawing, it can use Secure Store and the unattended service account to refresh the data. However, Microsoft Visio does not use Secure Store or the unattended service account, but instead requires the user to have direct database access to work with a data-connected diagram. Because of this, in order to create a data-connected diagram in Visio, we must first grant Read access to the CorporateDirectory 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 CorporateDirectory database.

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

To grant database access to a user

  1. Log on to Contoso-SQL as Contoso\SQLAdmin.

  2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

  3. Connect to the Database Engine.

  4. In SQL Server Management Studio, expand Security, and then expand Logins.

  5. Right-click the Contoso\VisioDiagramAuthors logon, and then click Properties.

  6. In the pane on the left side, click User Mapping.

  7. Select the Map check box for the CorporateDirectory database.

  8. In the Database role membership for: CorporateDirectory list, select the db_datareader check box, and then click OK.

Publishing a data-connected web drawing

To confirm that we have successfully configured Visio Services and the unattended service account, we will create a data-connected Web drawing that uses the CorporateDirectory table that we created in SQL Server.

To create a diagram

  1. Log on to Contoso-Client by using the Contoso\Susan.Burk account.

  2. Click Start, click All Programs, click Microsoft Office, and then click Microsoft Visio 2010.

  3. On the Choose a Template page, in the Template Categories section, click Maps and Floor Plans.

  4. Click Floor Plan, and then click Create.

  5. From the Shapes list, drag four Room shapes onto the page and organize them in two rows of two with a small space between the rows.

  6. Drag a Door shape onto the bottom wall of each of the top two Room shapes.

  7. Drag a Door shape onto the top wall of each of the bottom two Room shapes.

You should now have a diagram that resembles the following:

Image of floorplan

The next step is to connect the data in the CorporateDirectory database to the floor plan that we just created.

To connect the Visio diagram to data

  1. On the ribbon, click the Data tab, and then click Link Data to Shapes.

  2. On the Data Selector page of the wizard, click Microsoft SQL Server database, and then click Next.

  3. On the Connect to Database Server page, type Contoso-SQL in the Server name box, and then click Next.

  4. On the Select Database and Table page select CorporateDirectory from the drop-down list, and then click Next.

  5. On the Save Data Connection File and Finish page, click Finish.

    Note

    If you are prompted to overwrite the existing data connection file, click Yes.

  6. On the Select Data Connection page, click Next.

  7. On the Connect to data page, click Next.

  8. On the Configure Refresh Unique Identifier page, clear the Name check box and select the Office check box.

  9. Click Finish.

  10. On the drawing page:

    1. From the External Data section, drag John Woods to the upper-left office in the drawing.

    2. Select the upper-left office in the drawing, and then on the Data tab, click Data Graphics, and then click Edit Data Graphic.

    3. Select the Name row in the Data Field list, and then in the Default position section, select Left from the Horizontal drop-down list.

    4. With the Name row still selected, click Edit Item.

    5. From the Style list, select Heading 3, and then click OK.

    6. Select the Office row in the Data Field list, and then in the Default position section, ensure that Horizontal is set to Left.

    7. With the Office row still selected, click Edit Item.

    8. From the Style list, select Heading 3, and then click OK.

    9. Click OK.

    10. From the External Data section, drag Susan Burk to the lower-left office in the diagram (under John Woods).

    11. From the External Data section, drag Cindy White to the upper-right office in the diagram (to the right side of John Woods).

    12. From the External Data section, drag David Hamilton to the lower-right office in the diagram (under Cindy White).

  11. Leave the diagram open for the next procedure.

You should now see a diagram similar to the following.

Image of floorplan

The next step is to publish this diagram to the Business Intelligence Center as a data-connected Web drawing.

To publish a data-connected Web drawing

  1. Click File, and then click Save & Send.

  2. Click Save to SharePoint.

  3. Under Locations, click Browse for a location.

  4. Under File Types, click Web Drawing.

  5. Click Save As.

  6. 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.

  7. In the All Site Content list, double-click Documents.

  8. Ensure that the Automatically view files in browser check box is selected.

  9. In the File name box, type Floor plan, and then click Save.

    The Web drawing renders in a browser window.

  10. When the drawing is displayed, if a Refresh Disabled warning is displayed, click Enable (always).

    Leave this browser window open for the data refresh test procedure that follows.

The rendered Web drawing shows the data currently in the CorporateDirectory database. To test the data refresh mechanism, we can modify the data in the Office 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 in to Contoso-Client as Susan Burk while you do this procedure.

To test Visio Services data refresh

  1. Log on to Contoso-SQL by using the Contoso\SQLAdmin account.

  2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

  3. On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.

  4. In the toolbar, click New Query.

  5. Copy the following query to the query window:

    USE [CorporateDirectory]
    GO
    UPDATE dbo.Office set dbo.Office.Name = 'David Hamilton' where dbo.Office.Office = '1501'
    GO
    UPDATE dbo.Office set dbo.Office.Name = 'John Woods' where dbo.Office.Office = '1504'
    GO
    
  6. In the toolbar, click Execute.

  7. Return to Contoso-Client where the Server Status Web drawing is displayed.

    Note that in the original drawing, John Woods is in office 1501 and David Hamilton is in office 1504.

  8. On the Visio Web Access toolbar, click Refresh.

    Note that John Woods and David Hamilton have now swapped offices, reflecting the manual changes we made in SQL Server.

    Note

    The Visio Services cache settings affect how quickly the new results appear when you refresh the drawing. In the Configure Visio Services for a BI test environment lab, we set the cache settings to zero (0), allowing immediate refresh. In a production environment, refresh will likely take longer depending on how you configure the cache.

See Also

Concepts

Plan Visio Services security (SharePoint Server 2010)