Nov22

What To Do When SharePoint’s Visio Service Is Unable To Refresh A Data Connection

As SharePoint Consultants, we hate run-on sentences.  Especially run-on sentences in SharePoint error messages like “Visio Services was unable to refresh external data connections in this Web Drawing because it was unable to retrieve credentials to access underlying data sources from the farm’s Secure Store Service.”

And to add insult to that long-winded bit of error messaging, it caps it by telling you to resolve the issue you should contact your system administrator.  Which is great unless you’re the system administrator – because that means you have to figure out how to resolve this!  Luckily, we have some ideas to help you to do just that.

visio_serices_error

The short answer is that you have what is known as a “double-hop” issue.  Let’s dig into this and see how you can resolve the issue.

Although you can use impersonated credentials locally (first hop), Windows won’t forward impersonated credentials to a remote resource (the second hop). So the user’s identity is lost even before reaching the back end.  Additionally, SharePoint can’t pass the credentials of the logged-in user all the way to the back end via the services.

To solve this problem, a mechanism will be needed by which the logged-in user can be “impersonated as someone else” in order to use Visio services and connect to the back-end data.  This can achieved by using the Secure Store Services found in SharePoint.

Secure Store Services

Secure Store Services were introduced in SharePoint 2010.  In many ways you can think if is as the next generation of the single sign-on service in MOSS. Secure Store Services is a credential store that saves account information securely in the database. This allows you to create and set these credentials on a per-application basis associated with an Application ID and to use this Application ID for different services that are subject to the double-hop issue.

You can think of this as a gatekeeper service for authenticating a user (or a group) against an application. You can also set the ID for each target application at the farm level.

Configuring Secure Store Services

Before you can use Secure Store Services, you’ll need to configure it.  Let’s gets moving with that by performing the following:

  • As a Farm Administrator, log on to the Central Administration site.
  • Click on Application Management, and choose ‘Manage Service Applications’ from the Service Applications group as shown in figure belowvso_mange_service_apps
  • From the list of available services, click on ‘Secure Store Service’ as indicated in figure below.vso_secure_store_service
  • Choose ‘Manage Service Applications’ in the Central Administration site
  • On the Secure Store Service page, set the following options:
    • Generate New Key: Before beginning to create a new target application, you need to generate a new key from a pass phrase as you can see in the figure below. This key is used to encrypt and decrypt credentials that are stored in the database. Please note – you must have Administrator rights to create a key.

vso_generate_new_key

IMPORTANT: Refreshing a key will be necessary if you add a new application server to the existing server farm; if you restore a secure store service database; or if you receive errors such as “Unable to get master key.”

vso_refresh_key

Use Caution: Secure Store Services has its own database. Whenever you create a new key or refresh an existing key, be sure to back up the database.

After you have successfully created the new key, click the “New” button under Manage Target Applications on the Edit tab of the ribbon and provide the following information in the Target Application Settings window:

  • Target Application ID: A unique identifier that will be used in service applications as the unattended service account reference for authentication. You can’t change the Target Application ID once it is created.
  • Display Name: A name used for display only.
  • Contact E-mail: The primary e-mail for this Application ID.
  • Target Application Type: Specify Individual for individual users and Group for group credentials.
  • Target Application Page URL: Selecting Use default page will result in the sign-up page
  • http://yoursite/_layouts/SecureStoreSetCredentials.aspx?TargetAppId=<applicationID> being used; this option is available only when you select the Individual Target Application Type. If you select Use custom page, you need to create a page first and provide its URL. This page is used to authenticate users. Choose “None” if you don’t want a sign-up page.
  • Refresh Key: To refresh a key, you again need Administrator rights, as well as the pass phrase you set when you created the key.

Enter “VisioServices” for the Target Application ID; Secure Store Services for Visio Services for the Display Name; <ValidEmailAddress> for the Contact E-mail; Individual for the Target Application Type; and Use default page for the Target Application Page URL. Click Next.

vso_target_app_ID

  • In the Add Field window leave the defaults as they are and click on Next to continue.vso_win_user_name
  • Add administrator users who need to have access to this Application ID on the Target Application Administrators, and click OK.
  • Return to the Secure Store Services window, and choose the Target Application ID created in the previous step. Click the Set button on the Credentials tab of the Edit tab, as seen in the figure below:vso_set_Cred
  • On the Set Credentials for Secure Store Target Application screen (Figure 2-53), enter values for Credential Owner, Windows User Name, and Windows Password (and Confirm Windows Password), and click OK. Credential owners are the members (individuals or group) of the target application that will impersonate credentials when accessing external data.

IMPORTANT: If you will be using Secure Store Services for your Visio diagram to connect to the SQL Server instance, you need to enter the user credentials of those who have permissions to the Visio Services database from where you will load data. It must be credential for low-privilege users, not an administrator.

The previous step creates a new Secure Store Services Application ID that can be used to connect to SQL Server from service applications that require additional authentication to retrieve data.

Using Visio with SQL Server and Secure Store Services

Now let’s configure Visio Services to use the new Application ID:

  • Go to Central Administration ➤ Application Management ➤ Service Applications ➤Manage Service Applications, and choose Visio Graphics Service.

Click on the Global Settings in the External Data section, and enter VisioServices under Application ID. Click OK.vso_set_cred_indv

IMPORTANT: Application IDs are set for each service application, such as Visio Graphics Service. You can also have two Visio Graphics Service applications set for one Web application: one default and the other a Custom Visio Graphics Service application. For that, create a new Application ID with different credentials and set them to a new Custom Service Application. You can then add the new custom service application to the web application Service Connections.

This sets the authorization proxy for the Visio Graphics Services to connect to the SQL Server database and retrieve data without losing the user context.

Keep in mind that you mileage may vary and some screens may not be exactly as posted above.  Also, your specific SharePoint configuration may have some wonkiness to it that may impact what you can see and do.  Should you run into problems, give Unbounded Solutions a call to see if we can help.

Leave a Comment

Your email address will not be published. Required fields are marked *