Creating Service Accounts for Connectors Using OAuth Authentication

The steps below describe how to create service accounts for connectors with OAuth authentication.

• Hubspot

• Box Cloud Storage

• Google Drive

• Google Big Query

• Google Sheets

• Google Ads

Hubspot

Steps:

1. Login to your Hubspot account.

2. Click on Settings.

3. Navigate to Integrations > API Key.

4. Generate an API Key:

   

    

   Next Step:

   When importing tables from HubSpot, add the following Other connection option using the key value as your API Key: "APIKey=[key value]"

Box Cloud Storage

Steps:

1. Log in to your Box developers dashboard: https://app.box.com/developers/console.

2. Click Create New App and select your App type (e.g., Custom App).

3. Select the OAuth 2.0 with JWT (Server Authentication) authentication method and select View Your App.

4. Click Configuration from the main menu to access your app settings.

5. Select the application access level and the scope of user permissions your app will request. The enterprise access level enables you to work with existing users in your enterprise. The application-level setting restricts access to App-type users, users that have only API access.

6. In Add and Manage Key Pairs, click on Generate a Public/Private keypair. This will prompt to enable 2-Factor authentication. Go to settings and enable this:

You have a choice of using the Authenticator App:

Or using SMS Text Message:

1.  

7. Go back to the Developer console, configuration of your app and click on Generate a Public/Private keypair. This will create a new JSON file. Open the file.

8. Copy the value of Private Key and save to a PEM file. This file will be the value for OAuthJWTCert.

9. Replace all \n instances with a new line. The file should look something like this:

    

    

10. Note the Client ID and Client Secret in the JSON file. These will be the values for OAuthClientId and OAuthClientSecret, respectively.

11. Authorize the application in the enterprise admin console: Select  Apps > Custom Applications > Authorize New App and enter the Client Id in your app settings.

    Note: If you change the JWT access scopes, you will need to reauthorize the application in the enterprise admin console: Click Apps in the main menu and then select the ellipsis button next to your JWT application name. Select Reauthorize App in the menu.

    NEXT STEP:

    When importing tables from Box Cloud Storage to Data Prep Studio, add the following Other connection option: "OAuthJWTCert=[Location of JWT Cert File];OAuthJWTCertType=PEMKEY_FILE;OAuthJWTSubjectType=enterprise;OAuthJWTSubject=[JWT Subject];OAuthJWTPublicKeyId=[JWT Public Key ID];OAuthJWTCertPassword=[JWT Cert Password];OAuthJWTCertSubject=*;OAuthClientId=[OAuth Client ID];OAuthClientSecret=[OAuth Client Secret]"

 

Reference:  

• http://cdn.cdata.com/help/BBE/odbc/pg_connectingtoboxcustomappcreate.htm.

Google Drive and Google BigQuery

Go to https://console.cloud.google.com/apis/credentials.

Steps:

1. Login to your Google account.

2. Select a project.

3. Select Create Credentials > Service Account.

4. Specify a Name and Description.

5. Note of the Service Account ID. This will be the value for OAuthJWTIssuer.

6. Click Create Button, Continue, then Done.

7. Edit the new service account.

8. Select Keys > Add Key > Create New Key,

9. Select P12 key type, then Create.

   

    

   A p12 file will be generated. This will be the value for OAuthJWTCert.

10. Take note of the private key password (e.g. “notasecret”), this will be the value for OAuthJWTCertPassword.

11. Click Save.

12. Select Create Credentials, OAuth Client ID.

13. Select Desktop app as Application Type, then set the Name.

14. Note the Client ID and Client Secret. These will be the values for OAuthClientId and OAuthClientSecret, respectively.

    

     

15.  Go to Library, search for Google Drive API and Google BigQuery API, and click on Enable API.

    Next Step:

    When importing tables into Data Prep Studio via the Google Drive and Google BigQuery connectors, set the following Other connection option: OAuthJWTCert=[Location of JWT Cert File];OAuthJWTCertType=PFXFILE;OAuthJWTCertPassword=[JWT Cert Password];OAuthJWTCertSubject=*;OAuthJWTIssuer=[JWT Issuer];OAuthClientId=[OAuth Client ID];OAuthClientSecret=[OAuth Client Secret]

 

References:

• http://cdn.cdata.com/help/RGE/odbc/pg_oauthcustomappcreate.htm

• http://cdn.cdata.com/help/DBE/odbc/pg_oauthcustomappcreate.htm

Google Sheets and Google Ads

Steps:

1. Go to https://console.cloud.google.com/apis/credentials.

2. Login to your google account.

3. Select a project.

4. Select Create credentials, Service Account.

5. Set a Name and Description.

6. Note the Service Account ID, this will be the value for OAuthJWTIssuer.

7. Click Create, Continue, then Done.

8. Edit the new service account.

9. Select Keys > Add Key > Create New Key,

10. Select JSON key type, then Create.

    

     

    A JSON file will be generated.

11. Open the JSON file.

12. Copy the value of Private Key and save to a PEM file. This file will be the value for OAuthJWTCert.

13. Replace all \n instances with a new line. The file should look something like this:

     

     

14. Click Save.

15. Click on Create Credentials, OAuth Client ID.

16. Select Desktop app as Application type, then set the  Name.

17. Take note of the Client ID and Client Secret. These will be the values for OAuthClientId and OAuthClientSecret, respectively.

    

     

18.  Go to Library, then search for Google Sheets API, and click on Enable API.

19. Edit the service account. Click on Show Domain-wide delegation, and check Enable G-Suite Domain-wide delegation. This will also create a service account client in OAuth Client ID list.

20. Enable Gsuite domain delegation:

    1. Go to https://admin.google.com/datawatchqa.com/ManageOauthClients

    2. Create an Authorized API Client

       • Client ID: client_id from json file, when you created a Key in service account

       • API Scopes: (comma-delimited) --> list of scopes are in https://developers.google.com/identity/protocols/oauth2/scopes

21. For Google Sheets, add the following scopes:

    • https://www.googleapis.com/auth/spreadsheets

    • https://www.googleapis.com/auth/spreadsheets.readonly

    • https://www.googleapis.com/auth/drive

    • https://www.googleapis.com/auth/drive.file

    • https://www.googleapis.com/auth/drive.readonly

    • For Google Ads, add the following scopes:

    • https://www.googleapis.com/auth/adwords

Next Step:

When importing tables into Data Prep Studio via the Google Sheets and Google Ads connectors, set the following Other connection option: OAuthJWTCert=[Location of JWT Cert File];OAuthJWTSubject=[JWT Subject];OAuthJWTCertType=PEMKEY_FILE;OAuthJWTCertPassword=[JWT Cert Password];OAuthJWTCertSubject=*;OAuthJWTIssuer=[JWT Issuer];OAuthClientId=[OAuth Client ID];OAuthClientSecret=[OAuth Client Secret]

 

References:

• http://cdn.cdata.com/help/RLE/odbc/pg_oauthcustomappcreate.htm

• http://cdn.cdata.com/help/DZE/odbc/pg_oauthcustomappcreate.htm

• https://developers.google.com/adwords/api/docs/guides/authentication#prerequisites