ADO.NET Provider for Excel

Build 20.0.7655

Connecting to Google Drive

The provider facilitates the following OAuth authentication flows:

  • The user consent flow enables individual users to connect to their own data.
  • The service account flow enables access to domain-wide data.

Using a User Account to Connect to Google

This OAuth flow requires the authenticating user to interact with Google using the browser. The provider facilitates this in various ways as described below.

Authenticate to Google from a Desktop Application

After setting InitiateOAuth to GETANDREFRESH, you are ready to connect. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property. When you connect the provider opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process:

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Refreshes the access token when it expires.
  3. Saves OAuth values to be persisted across connections. This file can be configured in OAuthSettingsLocation.

Authenticate from a Web Application

When connecting via a Web application or if the provider is not authorized to open a browser window, you exchange a verifier code for the access token.

To begin, you need to register an OAuth app with Google and set the following connection properties.

  • OAuthClientId: Set this to the client Id in your app settings.
  • OAuthClientSecret: Set this to the client secret in your app settings.

Once you have registered an app and set OAuthClientId and OAuthClientSecret you can exchange a verifier code for the access token.

  1. Call GetOAuthAuthorizationURL. The stored procedure returns the URL of the OAuth endpoint.
  2. Log in at the OAuth endpoint and authorize the application. You are redirected back via the callback URL.

    The verifier code is appended to the callback URL in a query string parameter named "code". Extract the verifier code.

  3. Call GetOAuthAccessToken.
To connect to data, set OAuthAccessToken. When the access token expires, call RefreshOAuthAccessToken.

Using a Service Account to Connect to Domain-Wide Data

You can use a service account in this OAuth flow to access Google APIs on behalf of users in a domain. A domain administrator can delegate domain-wide access to the service account.

To complete the service account flow, generate a private key in the Google APIs Console. In the service account flow, the provider exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The OAuthAccessToken authenticates that the provider has the same permissions granted to the service account.

Generate a Private Key

Follow the steps below to generate a private key and obtain the credentials for your application:

  1. Log into the Google API Console.
  2. Click Create Project or select an existing project.
  3. In the API Manager, click Credentials -> Create Credentials -> Service Account Key. In the Service Account menu, select New Service Account or select an existing service account. In the Key Type section, select the P12 key type.
  4. Click Create to download the key pair. The private key's password is displayed: Set this in OAuthJWTCertPassword.
  5. In the Service Account Keys section on the Credentials page, click Manage Service Accounts and set OAuthJWTIssuer to the email address displayed in service account Id.
  6. Click Library -> Google Drive API -> Enable API.
  7. If using Domain Wide Delegation, you'll need to add the following scopes to the service account client ID in the Google Admin console. In the Admin Console, go to Security -> API controls -> Domain wide delegation -> Manage Domain Wide Delegation. Add the service account's ID and enter the following scopes: https://www.googleapis.com/auth/drive, https://www.googleapis.com/auth/devstorage.read_write

Authenticate with a Service Account

After setting the following connection properties, you are ready to connect:

  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
  • OAuthJWTCertType: Set this to "PFXFILE".
  • OAuthJWTCertPassword: Set this to the password of the .p12 file.
  • OAuthJWTCertSubject: Set this to "*" to pick the first certificate in the certificate store.
  • OAuthJWTIssuer: Set this to the email address of the service account.
  • OAuthJWTCert: Set this to the path to the .p12 file.
  • OAuthJWTSubject: Set this to the email address of the user for whom the application is requesting delegate access.
When you connect the provider completes the OAuth flow for a service account:
  1. Creates and signs the JWT with the claim set required by the provider.
  2. Exchanges the JWT for the access token.
  3. Submits the JWT for a new access token when the token expires.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7655