Export (0) Print
Expand All

Data authentication for Visio Services in SharePoint Server 2013

SharePoint 2013
 

Applies to: SharePoint Server 2013

Topic Last Modified: 2013-12-18

Summary: Visio Services supports connections with Excel workbooks, SharePoint lists, SQL Server databases, and OLE DB and ODBC data sources.

Data sources are categorized as internal or external as follows:

  • Internal: Data hosted within the SharePoint farm, such as an Excel workbook or a SharePoint list.

  • External: SQL Server data, or an OLE DB or ODBC data source.

Retrieving data from a data source requires a user to be authenticated by the data source and then authorized to access the data that it contains. In the case of a diagram, Visio Services authenticates to the data source on behalf of the user who is viewing it in order to refresh the data to which the diagram is connected.

Figure: The process by which Visio Services retrieves data from a data source

Retrieving data from a data source

Which authentication method Visio Services can use to retrieve data depends on the type of the underlying data source, as outlined in the following table. For data sources that support more than one authentication method, the data connection must specify which one to use.

 

Data source Authentication method

SharePoint lists

SharePoint user permissions

Excel workbooks

SharePoint user permissions

SQL Server

One of:

  • Windows authentication (integrated security)

    • using Constrained Kerberos Delegation

    • using Secure Store

    • using the Unattended Service Account

  • SQL Server Authentication

OLE DB/ODBC

Varies per data source, typically a user-name and password pair stored in the connection string.

Custom data providers can also be used.

The following data sources are supported in Visio but not in Visio Services:

  • Access databases

  • Excel workbooks not hosted on SharePoint Server

  • OLAP

Visio Services supports data-connected diagrams that are connected to data hosted within the SharePoint farm, including the following:

  • Excel workbooks residing in a document library

  • Data in SharePoint lists

Visio Services uses the diagram viewer's SharePoint Server credentials to connect to an .xlsx Excel workbook. For the authentication operation to succeed, the following conditions must be met:

  • Excel Services must be provisioned correctly and configured on the SharePoint farm.

  • The workbook must be hosted on the same farm as the diagram.

  • The diagram viewer must have at least "read" permissions to the Excel workbook.

No other configuration steps are required to enable this kind of data connection.

NoteNote:
As part of connecting to an Excel workbook, Visio Services requests that Excel Services refresh the workbook if it contains connections to external data. In this case, the diagram viewer's identity is passed on to Excel Services so that Excel Services can authenticate to underlying data sources to refresh the workbook.

Visio Services uses the diagram viewer's SharePoint Server credentials to connect to a SharePoint list. For the authentication operation to succeed the following conditions must be met:

  • In order for a user to access data in an External List, the user must have permissions to access the External Content Type and permissions to access the external data source.

  • The diagram viewer must have at least "read" permissions to the SharePoint list.

No other configuration steps are required to enable this kind of data connection.

Visio Services can connect to various external data sources, including SQL Server, OLE DB/ODBC, and custom data providers. To connect to the data source, Visio Services uses a specific data provider for each data source.

As a security measure, Visio Services must explicitly trust data providers before they can be used. For more information about trusted data providers, see Configure Visio Graphics Service trusted data providers in SharePoint Server 2013.

Connecting to a SQL Server data source can be done by using either:

  • Windows authentication

  • SQL Server Authentication

Other data sources use a connection string usually consisting of a user name and password.

Visio diagrams use one of two kinds of connections:

  • Embedded connections

  • Linked connections

Embedded connections are stored as part of the Visio diagram. Linked connections are stored externally to a diagram in Office Data Connection (ODC) files. To use a linked connection, a diagram must reference an .odc file that is also stored in the same farm as the diagram. Each data connection consists of:

  • A connection string

  • A query string

  • An authentication method

  • Optionally, some metadata required to retrieve external data

Each kind of connection has its advantages and drawbacks discussed here; choose the one that best suits your scenario.

 

Connection type Embedded connections ODC files

Data sources supported

  • SQL Server

  • OLE DB/ODBC

  • Excel workbooks

  • SharePoint lists

  • Custom Data Providers

  • SQL Server (supports all authentication methods)

  • OLE DB/ODBC

Advantages

  • All connection information is stored in the diagram.

  • Embedded connections require little administrative overhead to support.

  • Embedded connections are easy to create.

  • Linked connections can be centrally stored, managed, audited, shared and access to them controlled by using a data connection library.

  • Diagram authors can use existing connections without having to create queries and connection strings.

  • If the data connection details for a data source change, an administrator only need update one ODC file. Thanks to that change, all diagrams that refer to the ODC file will use the updated connection information when the next refresh occurs. (An example of this scenario is when the database server is moved or the database name is changed.)

Drawbacks

  • If the data connection details for a data source change, all diagrams with embedded connections to that data source will have to be republished with updated connection information.

  • Embedded data connections are more difficult to audit by SharePoint administrators.

  • Linked connections may require the help of a SharePoint administrator to share, manage and secure.

  • Linked connections are saved in clear text and may contain database passwords. Extra care must be taken to help secure these files.

Choose a linked data connection, by using an ODC file, for scenarios in which you must have a data connection to an enterprise-scale relational data source such as SQL Server. Linked data connections are most useful in scenarios in which they will be shared across many users and in which administrator control of the connection is important.

NoteNote:
If you are using Visio 2010, ODC files must first be created in Excel and exported to SharePoint Server before it can be used with Visio Services.

Choose an embedded connection for scenarios in which you have to have a quick data connection to a small or file-based data source that will only be used by some users.

ODC files can be stored in a data connection library, a special kind of SharePoint document library. Centralizing data connections in such a document library has several advantages:

  • Administrators can restrict write access to a data connection library to trusted data connection authors to make sure that only well tested and secure data connections are used by diagram authors.

  • Administrators have a single location to manage data connections for a large group of users.

  • Administrators can easily approve, audit, revert and manage data connection files by using document library versioning and workflow features.

  • Data connection libraries can be reused across other Office applications such as Excel, Excel Services, InfoPath 2013, InfoPath Forms Services, and Word.

  • End-users only have a single location to find diagram data, reducing confusion and user training.

Windows authentication requires that Visio Services present to SQL Server a set of Windows credentials. This kind of credential is common on Windows networks and is the same credential used to log on to computers on a Windows domain or to connect to a computer that is running Exchange Server. Windows credentials are considered a more secure and manageable means of controlling access to SQL Server databases. However, one obstacle to using Windows authentication with Visio Services is the Windows double-hop security measure, wherein a user's credentials cannot be passed across more than one computer in a Windows network. Given that Visio Services is a multi-tiered system, special authentication methods are required for Visio Services to retrieve data on behalf of the end-user.

Figure: The double-hop security measure prevents the passing of credentials across more than one computer in a Windows-based network

Windows Authentication

The authentication method to choose depends on various factors as outlined in the following table. Choose the one that best suits your scenario.

 

Authentication method Kerberos delegation Secure Store Unattended Service Account

Description

Using constrained Kerberos delegation, the diagram viewer's Windows credentials are sent to the data source directly.

Using Secure Store, the viewer's Windows credentials are mapped to another set of credentials specified in a Secure Store target application.

Using Secure Store, all viewers are mapped to a unique set of credentials called the Unattended Service Account that is stored in a specific Secure Store target application specified in Visio Services Global Settings.

Data connection credentials

The Windows credentials of the diagram viewer.

The credentials specified in the Secure Store target application.

The credentials of the Unattended Service Account.

Advantages

  • The Kerberos protocol is an industry standard in credentials management.

  • Kerberos ties into the existing Active Directory infrastructure.

  • Kerberos delegation enables auditing of individual accesses to a data source.

  • Given that the diagram viewer's identity is known, diagram creators can embed personalized database queries into diagrams.

  • Secure Store is part of SharePoint Server and is easier to configure than Kerberos authentication.

  • Mappings are flexible: a user can be mapped either 1-to-1 or many-to-1.

  • Non-Windows credentials can be used to connect to data sources that do not accept Windows credentials.

  • Mappings created for Visio can be re-used by other business intelligence applications such as Excel Services.

  • The Unattended Service Account is the easiest authentication method to deploy and setup.

  • The Unattended Service Account does not require much administrative overhead.

Drawbacks

  • Additional administrative effort required to configure for SharePoint Server and Visio Services.

  • Establishing and managing mapping tables requires some administrative overhead.

  • Secure Store allows limited auditing. In the many-to-1 scenario, individual incoming users are mapped into the same credentials through a target application, effectively blending them into one user.

  • Given that everyone is mapped to the same credentials, an administrator cannot distinguish who accessed a data source.

For the authentication operation to succeed …

  • Kerberos delegation must be set up on a SharePoint farm.

  • Secure Store must be provisioned and configured on the Farm. It must also contain appropriate mapping information for a particular incoming user. Additionally the mapping information may need to be updated periodically to reflect password changes on the mapped account.

  • Secure Store must be provisioned and configured on the Farm. It must also contain the credentials for the Unattended Service Account. Additionally the mapping information may need to be updated periodically to reflect password changes on the mapped account.

  • Visio Services Global Settings must be configured to use the Unattended Service Account.

Choose Kerberos delegation for secure and fast authentication to enterprise-scale relational data sources that support Windows authentication.

Choose Secure Store for authentication to enterprise-scale relational data sources that may support Windows Authentication. Secure Store is also useful in scenarios in which you want to control user credential mappings.

For information about how to use Secure Store with Visio Services, see Use Visio Services with Secure Store Service in SharePoint 2013.

For ease of configuration the Visio Graphics Service provides a special configuration where an administrator can create a unique mapping where all users are mapped to a single set of credentials.

This account, known as the Unattended Service Account, must be a low-privilege Windows domain account. Visio Services impersonates this account when it connects to a data source on behalf of a diagram viewer.

It is a best practice to give this account as few network permissions as possible, typically only to log on to the network and to access the data source that you want users to connect to. For better security, be sure that the Unattended Service Account does not have access to the SharePoint Configuration and Content databases.

The Unattended Service Account is used by Visio Services in the following circumstances:

  • When an ODC file specifies the use of the Unattended Service Account for either Windows or SQL Server Authentication

  • When no ODC is used, and Kerberos authentication fails

NoteNote:
The unattended account can be a local computer account of type Windows. If the unattended service account is configured as a local computer account, make sure that the configuration is identical on every application server that is running Visio Services. For manageability reasons, the best practice is to use a domain account

Choose the Unattended Service Account when you connect to small ad-hoc deployments in which security is less important or for which speed of deployment is very important.

For information about how to use the Unattended Service Account with Visio Services, see Secure Store for Business Intelligence service applications (SharePoint Server 2013).

SQL Server Authentication requires that Visio Services present a SQL Server user name and password to a SQL Server data source to authenticate. Visio Services extracts this user name and password from the data connection's connection string and passes it to the data source.

To reduce security risks, Visio Services impersonates the Unattended Service Account when it connects to such a data source.

Authentication to third-party data sources typically requires that Visio Services present a user name and password to a data source. Like SQL Server Authentication, Visio Services extracts this user name and password from the data connection's connection string and passes them to the data source.

To reduce security risks, Visio Services impersonates the Unattended Service Account when it connects to such a data source.

Visio Services supports refreshing diagrams connected to one or more of the following data sources:

  • SQL Server

  • SharePoint lists

  • Excel workbooks hosted in SharePoint Server

  • Oracle 9i, 9iR2, 10g, 10gR2, 11g, 11gR2, and DB2 9.2

NoteNote:
If the data source that you plan to connect to is not in the list above, you can add support for it by creating a Visio Custom Data Provider. This technology enables you to wrap your existing data sources into one that Visio Services can consume.

External data refresh is the result of the following set of steps through Visio Services.

Figure: Process for external data refresh

Exernal data refresh
  1. Creating a diagram:   A diagram author uploads a data-connected diagram to SharePoint Server 2013.

  2. Triggering Refresh:   The diagram viewer triggers refresh on a data-connected diagram.

  3. Data Connections:   Visio Services retrieves data connection information for each external data source in the diagram.

  4. Trusted Data Providers:   Visio Services checks whether there is a trusted data provider it can use to retrieve data.

  5. Authentication:   Visio Services authenticates into the data source and retrieves the requested data on behalf of the diagram viewer.

  6. Diagram Refresh:   Visio Services updates the diagram based on the data source data and returns it to the viewer.

Refresh can be triggered in one of following ways from the browser:

  • The end-user opens the diagram.

  • The end-user clicks on the refresh button on an already open diagram.

  • The end-user loads a page that contains the Visio Web Access Web Part which was configured to refresh automatically by a site designer.

    NoteNote:
    A SharePoint site designer must place the Visio Web Access Web Part on a page and configure it to refresh periodically.

Refresh can also be triggered in third-party solutions by calling through JavaScript the Vwa.VwaControl.refreshDiagram Method of the Visio Web Access Web Part's Mash-up API.

If there are no previously cached versions of this diagram, any of these actions will trigger a refresh and update the diagram. For information about how to configure cache settings for Visio Services, see Configure Visio Graphics Service global settings in SharePoint Server 2013.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft