How to connect SharePoint with PingOne Data through external lists

Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Provide PingOne data to SharePoint users as an external list.

You can use the CData API Server to give your users the capabilities to access PingOne data in SharePoint. This article shows how to create an external list of PingOne data that is always up to date. You will use an external content type as a template to create the external list. The external content type enables connectivity through OData, a real-time data streaming protocol for mobile and other online applications. The API Server is an OData producer of PingOne feeds.

After setting up the API Server, creating an external list with connectivity to PingOne data consists of three basic steps:

  1. Create the External Content Type
  2. Import the External Content Type
  3. Create the External List

This article also covers how to accomplish the following tasks:

Set Up the API Server

If you have not already done so, download the CData API Server. Once you have installed the API Server, follow the steps below to begin producing secure PingOne OData services:

Connect to PingOne

To provide PingOne data to SharePoint users as an external list, we start by creating and configuring a PingOne connection. Follow the steps below to configure the API Server to connect to PingOne data:

  1. First, navigate to the Connections page.
  2. Click Add Connection and then search for and select the PingOne connection.
  3. Enter the necessary authentication properties to connect to PingOne.

    To connect to PingOne, configure these properties:

    • Region: The region where the data for your PingOne organization is being hosted.
    • AuthScheme: The type of authentication to use when connecting to PingOne.
    • Either WorkerAppEnvironmentId (required when using the default PingOne domain) or AuthorizationServerURL, configured as described below.

    Configuring WorkerAppEnvironmentId

    WorkerAppEnvironmentId is the ID of the PingOne environment in which your Worker application resides. This parameter is used only when the environment is using the default PingOne domain (auth.pingone). It is configured after you have created the custom OAuth application you will use to authenticate to PingOne, as described in Creating a Custom OAuth Application in the Help documentation.

    First, find the value for this property:

    1. From the home page of your PingOne organization, move to the navigation sidebar and click Environments.
    2. Find the environment in which you have created your custom OAuth/Worker application (usually Administrators), and click Manage Environment. The environment's home page displays.
    3. In the environment's home page navigation sidebar, click Applications.
    4. Find your OAuth or Worker application details in the list.
    5. Copy the value in the Environment ID field. It should look similar to: 
      WorkerAppEnvironmentId='11e96fc7-aa4d-4a60-8196-9acf91424eca'

    Now set WorkerAppEnvironmentId to the value of the Environment ID field.

    Configuring AuthorizationServerURL

    AuthorizationServerURL is the base URL of the PingOne authorization server for the environment where your application is located. This property is only used when you have set up a custom domain for the environment, as described in the PingOne platform API documentation. See Custom Domains.

    Authenticating to PingOne with OAuth

    PingOne supports both OAuth and OAuthClient authentication. In addition to performing the configuration steps described above, there are two more steps to complete to support OAuth or OAuthCliet authentication:

    • Create and configure a custom OAuth application, as described in Creating a Custom OAuth Application in the Help documentation.
    • To ensure that the driver can access the entities in Data Model, confirm that you have configured the correct roles for the admin user/worker application you will be using, as described in Administrator Roles in the Help documentation.
    • Set the appropriate properties for the authscheme and authflow of your choice, as described in the following subsections.

    OAuth (Authorization Code grant)

    Set AuthScheme to OAuth.

    Desktop Applications

    Get and Refresh the OAuth Access Token

    After setting the following, you are ready to connect:

    • InitiateOAuth: GETANDREFRESH. To avoid the need to repeat the OAuth exchange and manually setting the OAuthAccessToken each time you connect, use InitiateOAuth.
    • OAuthClientId: The Client ID you obtained when you created your custom OAuth application.
    • OAuthClientSecret: The Client Secret you obtained when you created your custom OAuth application.
    • CallbackURL: The redirect URI you defined when you registered your custom OAuth application. For example: https://localhost:3333

    When you connect, the driver opens PingOne's OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver then completes the OAuth process:

    1. The driver obtains an access token from PingOne and uses it to request data.
    2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

    The driver refreshes the access token automatically when it expires.

    For other OAuth methods, including Web Applications, Headless Machines, or Client Credentials Grant, refer to the Help documentation.

  4. After configuring the connection, click Save & Test to confirm a successful connection.

Configure API Server Users

Next, create a user to access your PingOne data through the API Server. You can add and configure users on the Users page. Follow the steps below to configure and create a user:

  1. On the Users page, click Add User to open the Add User dialog.
  2. Next, set the Role, Username, and Privileges properties and then click Add User.
  3. An Authtoken is then generated for the user. You can find the Authtoken and other information for each user on the Users page:

Creating API Endpoints for PingOne

Having created a user, you are ready to create API endpoints for the PingOne tables:

  1. First, navigate to the API page and then click Add Table .
  2. Select the connection you wish to access and click Next.
  3. With the connection selected, create endpoints by selecting each table and then clicking Confirm.

Gather the OData Url

Having configured a connection to PingOne data, created a user, and added resources to the API Server, you now have an easily accessible REST API based on the OData protocol for those resources. From the API page in API Server, you can view and copy the API Endpoints for the API:

Create the External Content Type

The external content type is a schema that will provide the core connectivity to PingOne data from any SharePoint app. You can create a schema for any OData query. You can pass in the required options with the $sharepoint query string parameter. Below is an example request, which will return the schema in an .ect file: 

https://my-server:8080/api.rsc/[CData].[Administrators].Users?$sharepoint=AuthMode:Passthrough&@authtoken=my-authtoken

Note that for simplicity, the authtoken is passed in the query string to authenticate the request for the .ect. This is not enabled by default; if you would like to use this method to authenticate to the API Server, you will need to add an entry like the following to your settings.cfg file: 

[Application]
AllowAuthtokenInUrl = true

The settings.cfg file is located in the data directory. In the .NET edition, the data directory is located in the app_data subfolder of the application root. In the Java edition, the location of the data directory depends on your operating system:

  • Windows: C:\ProgramData\CData
  • Unix or Mac OS X: ~/cdata

Import the External Content Type

After you have created the .ect, you can follow the steps below to import it into SharePoint Online or an on-premise SharePoint installation.

SharePoint 2013

Navigate to the SharePoint central administration portal and click the link to manage service applications. In the resulting page, click Business Data Connectivity Service. Select External Content Types in the menu and click Import. In the BDC Model section, click Choose File to select the .ect file in the dialog.

SharePoint Online

In the SharePoint admin center, click BCS from the quick launch menu and then click Manage BDC Models and External Content Types. On the resulting page, select External Content Types in the menu and click Import. In the BDC Model section, click Choose File.

Create the External List

You can now create SharePoint apps that can access and modify PingOne data:

  1. Navigate to your SharePoint site and choose Site Contents -> Add an App -> External List.
  2. Click the Select External Content Type icon and choose the external content type that you created in the previous section.
  3. Click Create.

Limit Results

SharePoint has limits on how much data can be retrieved from external lists of OData sources. External lists display results in pages of 30 items by default. To modify the paging size, you can set the Item Limit property in the settings for the default view. Alternatively, you can build a custom Web part to view the data from the external list.

In SharePoint 2013, requests to external data sources are limited by bandwidth throttling controls, which can be changed using the Set-SPBusinessDataCatalogThrottleConfig command. In SharePoint Online, requests to external data sources are limited by your Server Resource Quota and by bandwidth throttling controls; to work around this, you can apply filters in the request for data.

The API Server sets the default limit for the number of returned rows to be 500. You can disable this limit by adding the limit option to the $sharepoint query string parameter and setting its value to 0.

Configure Pass-Through Authentication

To use pass-through authentication for accessing your external content type, set the AuthMode option to pass-through.

If you are using Kerberos authentication, you need to add these users to the API Server. If you are not using Kerberos authentication, you are likely using another form of Windows authentication such as NTLM.

When users are authenticating via NTLM authentication and using pass-through authentication in the Business Connectivity Service (BCS) in SharePoint, SharePoint connects to the external Web service using the default IIS account. This account is often the NT AUTHORITY\IUSR account. Give this user access to the CData API Server.

See the help documentation for a guide to enabling Windows authentication for the API Server.

Configure Credentials Authentication

In the following sections, you will first create a secure store target application that authenticates SharePoint users to the API Server with the credentials for a user who has been added to the API Server. Next, you will create the external content type and configure it to authenticate with the credentials in the secure store.

SharePoint Online

  1. In the SharePoint Online administration center, click secure store from the quick launch bar and then click New.
  2. In the Target Application Settings section, enter the target application Id, display name, and contact email.
  3. In the Credential Fields section, add the credentials for a user in the API Server.
  4. In the Target Application Administrators section, choose an administrator who can access the connection settings for the external content type. The account for the SharePoint Online administrator is usually specified here.
  5. In the Members section, enter the users in SharePoint who are authorized to access PingOne data.

After you create the target application, save the credentials of The API Server user into the secure store:

  1. In the quick launch bar, click secure store.
  2. Click Set Credentials in the menu for the target application.
  3. Enter the username and password of a user in the API Server.

Next, create a new connection settings object:

  1. Click BCS from the quick launch bar and then choose the option to manage connections to online services.
  2. Click Add. Name the connection and enter the URL of the OData endpoint, https://my-server/api.rsc.
  3. Select the option to use credentials stored in SharePoint. Enter the application Id for the target application you created.

Finally, create the external content type, import it into SharePoint Online, and create the external list:

  1. Generate the external content type by making a request for data and specifying the required options in the $sharepoint query string parameter. Set the AuthMode option to Credentials. Specify the target application Id and the Id of the connection settings object. For example: 
    https://my-server/api.rsc/[CData].[Administrators].Users?$filter=EmployeeType eq 'Contractor'&$sharepoint=AuthMode:Credentials,TargetApplicationId:my-target-application-Id,ODataConnectionSettingsId:my-odata-connection-settings-Id
  2. Import the resulting .ect file: Open BCS from the quick launch bar and click the link to manage BDC models and external content types. Click Import.
  3. You can now create the external list.

SharePoint 2013

  1. Browse to the central administration area for your SharePoint site and click the link to manage service applications.
  2. Click Secure Store Service and then click New.
  3. Enter the application Id, display name, and contact email to configure the new secure store target application. Set the target application type to Group.
  4. On the next page, add the field names and field types to display when the user enters their username and password. Select the username and password field types.
  5. Next, in the Target Application Administrators section, choose administrators who are authorized to access connection settings for the external content type. In the Members section, enter the users in SharePoint who are authorized to connect with the credentials of the API Server user.

After you create the new secure store target application, follow the procedure below to set the credentials that users in SharePoint will provide when accessing the API Server:

  1. Click Set Credentials in the menu for the target application.
  2. In the resulting menu enter the username and password (authtoken) of a user in the API Server.

  3. Open an instance of the SharePoint management shell and initialize a new SharePoint connection object. Note that the connection object is site specific. 

    New-SPODataConnectionSetting -AuthenticationMode Credentials -ServiceAddressURL http://my-server/api.rsc -ServiceContext http://myspsite/ -Name MyODataConnectionSettingsId -SecureStoreTargetApplicationId my-target-application-Id

    The output below shows that the command has executed successfully:

The final steps involve creating the external content type for the table you want to expose in SharePoint, configuring it to authenticate with credentials from the secure store, and then importing it into SharePoint.

  1. Generate the external content type by making a request for data and specifying the required options in the $sharepoint query string parameter: Below is an example request: 
    http://my-server/api.rsc/Account?$filter=Industry eq 'Floppy Disks'&$sharepoint=AuthMode:Credentials,TargetApplicationId:my-target-application-Id,ODataConnectionSettingsId:my-odata-connection-settings-Id
  2. To import the external content type into SharePoint, point your browser to the SharePoint central administration portal and click the link to manage service applications.
  3. Click Business Data Connectivity Service in the resulting page.
  4. Click Import and select the .ect file in the dialog.
  5. You can now create the external list. On the Site Contents page on your SharePoint site, click the button to add an app and then click the icon to choose the external content type.

Ready to get started?

Learn more or sign up for a free trial:

CData API Server

In this article

Related articles