Installing and configuring the connector Using SQL Commands¶

Setting Up OAuth¶

You can configure the Snowflake Connector for ServiceNow® to use OAuth for authenticating to the ServiceNow instance:

• In ServiceNow, you must set up the instance to support using OAuth with the code grant flow.

• In the Snowflake Connector for ServiceNow®:

  • The connector uses a security integration with TYPE = API_AUTHENTICATION to connect Snowflake to the ServiceNow instance.

    The security integration specifies the ServiceNow OAuth client ID, client secret, and the endpoint URL for authenticating to the ServiceNow instance.

  • The connector uses a Snowflake secret object to manage sensitive information, including the authentication credentials.

    In the case of using OAuth for authentication, the connector stores the ServiceNow OAuth refresh token, the refresh token expiration time, and the name of the security integration in the Snowflake secret object.

To set up the Snowflake Connector for ServiceNow® to use OAuth, follow these steps:

1. Configure your ServiceNow instance to use the OAuth with code grant flow.

   • If your ServiceNow instance already uses the OAuth code grant flow and you would like to use that instance with the Snowflake Connector for ServiceNow®, note the client ID, client secret, and endpoint URL that corresponds to the OAuth token.

     For more information, see Manage OAuth tokens. After noting this information, create a security integration in the next step.

   • If you would like to use a different ServiceNow instance, access or create the instance and configure the instance to use OAuth with the code grant flow as shown in Set up OAuth and Create an endpoint for clients to access the instance.

2. Create an application registry in ServiceNow and use it to configure the connector.

   1. Login to your ServiceNow instance, then select Homepage.

   2. Search for OAuth, then select Application Registry.

   3. Select New, then select Create an OAuth API endpoint for external clients.

      This displays a configuration page for the application registry as shown in the following image:

      

   4. In ServiceNow, enter a name for the OAuth application registry in the Name field.

   5. In ServiceNow, in the Redirect URL field, enter the following:

      https://apps-api.c1.<cloud_region_id>.<cloud>.app.snowflake.com/oauth/complete-secret
      

      Copy

      Where:

      cloud_region_id

      Specifies the identifier for the cloud region of your Snowflake account.

      cloud

      Specifies the identifier for the cloud platform (e.g. aws, azure, or gcp).

   6. If required, in ServiceNow, update the values in the Refresh Token Lifespan and Access Token Lifespan fields.

      • Snowflake recommends setting the lifespan of the access token to at least 600 seconds.

      • For the lifespan of the refresh token, specify a value that is 7776000 (90 days).

   7. In ServiceNow, select Submit.

      The OAuth application registry appears in the list of application registries.

   8. In ServiceNow, select the application registry you just created.

      Note that ServiceNow generated values for the Client ID and Client Secret fields. You use these values in the next section when creating a security integration.

Generating OAuth Refresh Token¶

To generate the OAuth refresh token:

1. Ensure that you have performed the tasks outlined in Setting Up OAuth.

2. Send an HTTP request to the /oauth_token.do endpoint of your ServiceNow instance, as explained in the REST OAuth Example in the ServiceNow documentation.

   For example, if you are using curl to send the HTTP request:

   curl -d "grant_type=password" --data-urlencode "client_id=<client_id>" --data-urlencode "client_secret=<client_secret>" --data-urlencode "username=<username>" --data-urlencode "password=<password>" -X POST https://<instance_name>.service-now.com/oauth_token.do
   

   Copy

   Where

   instance_name

   Specifies the name of your ServiceNow instance.

   client_id and client_secret

   Specify the values you obtained when setting up the ServiceNow endpoint.

   username and password

   Specify the credentials for your ServiceNow instance.

   Note

   The example above uses the data-urlencode command-line flag in curl to URL-encode the client secret, username, and password in the HTTP request sent to ServiceNow.

   If you are using a different tool to send the HTTP request, make sure that you URL-encode these values in the request.

   The body of the HTTP response contains a JSON object. Get the refresh token from the refresh_token field in this object:

   {"access_token":"abcd1234","refresh_token":"cdef567","scope":"useraccount","token_type":"Bearer","expires_in":1799}
   

   Copy

Creating a Security Integration¶

A security integration is a Snowflake object that provides an interface between Snowflake and a third-party OAuth 2.0 service.

Use the CREATE SECURITY INTEGRATION command to create a security integration as shown in the following example:

CREATE SECURITY INTEGRATION <name>
 TYPE = API_AUTHENTICATION
 AUTH_TYPE = OAUTH2
 OAUTH_CLIENT_AUTH_METHOD = CLIENT_SECRET_POST
 OAUTH_CLIENT_ID = '<client_id>'
 OAUTH_CLIENT_SECRET = '<client_secret>'
 OAUTH_TOKEN_ENDPOINT = 'https://<my_instance>.service-now.com/oauth_token.do'
 ENABLED = TRUE;

Where:

name

Specifies the name of security integration.

client_id

Specifies the value of the Client ID field you obtained from ServiceNow in the previous section.

client_secret

Specifies the value of the Client Secret field you obtained from ServiceNow in the previous section.

my_instance

Specifies the name of your ServiceNow instance. This is the first part of the hostname of your ServiceNow instance. For example, if the URL to your ServiceNow instance is:

https://myinstance.service-now.com

Copy

The name of your instance would be myinstance.

Creating a Secret Object¶

Create the Snowflake secret object that the Snowflake Connector for ServiceNow® uses for authentication.

Snowflake recommends using a dedicated role to manage the secret object, separate from the ACCOUNTADMIN role (the role that you use to install, configure, and run the connector). Snowflake also recommends storing the secret object in a dedicated database and schema. Note that you can choose any role to manage the secret, and you can choose any database and schema to store the secret.

To create a custom role to manage the secret, use the CREATE ROLE command. For information on the privileges that you can grant to a role, see Access Control Privileges.

The next sections explain how to create a secret object that is stored in a separate database and schema and that is managed by a custom role.

Creating a Warehouse¶

Snowflake recommends creating a warehouse dedicated for the connector. A dedicated warehouse allows for better cost management and resource tracking. To facilitate resource tracking, you can optionally add one or more tags to the dedicated warehouse.

For the connector warehouse, Snowflake recommends using a large-sized multi-cluster warehouse.

To create a large-sized warehouse named servicenow_conn_warehouse, run the following command:

USE ROLE accountadmin;
CREATE WAREHOUSE servicenow_conn_warehouse WAREHOUSE_SIZE = LARGE;

Copy

Creating a Database and Schema for the ServiceNow Data¶

Next, create a database and schema for the ServiceNow data. The Snowflake Connector for ServiceNow® ingests ServiceNow data into this database and schema.

When creating the database and schema, note the following:

• The names of the schema and database must be valid object identifiers.

• To control access to the ingested ServiceNow data in Snowflake, you can grant the privileges on the schema to the roles that should be allowed to access the data.

To create the database and schema, run the CREATE DATABASE and CREATE SCHEMA commands.

For example, to create the database dest_db and the schema dest_schema for the ServiceNow data, run the following commands:

USE ROLE accountadmin;
CREATE DATABASE dest_db;
CREATE SCHEMA dest_schema;

Copy

Creating an API Integration for Communicating with the ServiceNow Instance¶

Next, create an API integration for communicating with the ServiceNow instance. Run the CREATE API INTEGRATION command with the following syntax:

CREATE API INTEGRATION <integration_name>
  API_ALLOWED_PREFIXES = ('https://<servicenow_instance_name>.service-now.com')
  ALLOWED_AUTHENTICATION_SECRETS = (<secret_name>)
  ENABLED = TRUE

Copy

Where:

integration_name

Specifies the name of the API integration. The name must be a valid object identifier. The name must be unique among API integrations in your account.

API_ALLOWED_PREFIXES = ('https://servicenow_instance_name.service-now.com')

Specifies the URL to your ServiceNow instance. This limits the use of this API integration to the instance with the specified URL.

ALLOWED_AUTHENTICATION_SECRETS = (secret_name)

Specifies the list of the names of the secrets that are allowed for use in the scope of the API integration.

Set this to the name of the secret object that you created in Creating a Secret Object.

ENABLED = TRUE

Specifies whether this API integration is enabled or disabled. If the API integration is disabled, any external function that relies on it does not work.

TRUE

Allows the integration to run based on the parameters specified in the integration definition.

FALSE

Suspends the integration for maintenance. Any integration between Snowflake and a third-party service fails to work.

For example, to create the API integration named servicenow_api_integration for the ServiceNow instance named myinstance, run the following command:

USE ROLE accountadmin;
CREATE API INTEGRATION servicenow_api_integration
  API_ALLOWED_PREFIXES = ('https://myinstance.service-now.com')
  ALLOWED_AUTHENTICATION_SECRETS = (secretsdb.apiauth.servicenow_creds_pw)
  ENABLED = TRUE

Copy

Configuring Logging for the Connector¶

The Snowflake Connector for ServiceNow® uses event tables to store error logs for the connector. To set up an event table follow Setting up an Event Table guide. If the event table isn’t set up, log and event information is discarded.

Creating a Custom Role for the Connector¶

Next, create a custom role for the connector, and grant the privileges needed to run the connector to that role.

This role allows the connector to:

• Create and manage tasks for ingesting the data.

• Use the dedicated warehouse.

• Access the secret and API integration in order to connect to the ServiceNow instance.

The role is also the owner of the tables and views containing the ServiceNow data ingested by the connector.

To create this role and grant the privileges:

1. Using the USERADMIN system role, run the CREATE ROLE command to create a custom role to own the data. For example, to create the custom role connector_resources_provider for owning the data, run the following commands:

   USE ROLE useradmin;
   CREATE ROLE connector_resources_provider;
   

   Copy

2. Using the ACCOUNTADMIN role, run the GRANT <privileges> to ROLE command to grant the following privileges to the custom role:

   • EXECUTE TASK on the account

   • One of the following, but not both:

     • EXECUTE MANAGED TASK

       Grant this privilege if you configured the connector without a dedicated warehouse.

     • USAGE on the warehouse that you created for the connector.

       Grant this privilege if you are using a dedicated warehouse for the connector.

   • USAGE on the database that you created for the ServiceNow data

   • USAGE, CREATE TABLE, and CREATE VIEW on the schema that you created for the ServiceNow data

   • USAGE on the API integration that you created for ServiceNow

   • USAGE on the database that you created for the secret

   • USAGE on the schema that you created for the secret

   • USAGE on the secret that you created

   For example, to grant the following privileges to the role named connector_resources_provider:

   • EXECUTE TASK on the account

   • EXECUTE MANAGED TASK on the account

   • USAGE on the warehouse servicenow_conn_warehouse

   • USAGE on the dest_db database

   • USAGE, CREATE TABLE, and CREATE VIEW on the dest_db.dest_schema schema

   • USAGE on the servicenow_api_integration integration

   • USAGE on the secretsdb database

   • USAGE on the secretsdb.apiauth schema

   • USAGE on the secretsdb.apiauth.servicenow_creds_oauth_code secret secret

   Run the following commands:

   USE ROLE accountadmin;
   
   GRANT EXECUTE TASK ON ACCOUNT TO ROLE connector_resources_provider;
   GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE connector_resources_provider;
   
   GRANT USAGE ON WAREHOUSE servicenow_conn_warehouse TO ROLE connector_resources_provider;
   
   GRANT USAGE ON DATABASE dest_db TO ROLE connector_resources_provider;
   GRANT USAGE ON SCHEMA dest_db.dest_schema TO ROLE connector_resources_provider;
   GRANT CREATE TABLE ON SCHEMA dest_db.dest_schema TO ROLE connector_resources_provider;
   GRANT CREATE VIEW ON SCHEMA dest_db.dest_schema TO ROLE connector_resources_provider;
   
   GRANT USAGE ON INTEGRATION servicenow_api_integration TO ROLE connector_resources_provider;
   
   GRANT USAGE ON DATABASE secretsdb TO ROLE connector_resources_provider;
   GRANT USAGE ON SCHEMA secretsdb.apiauth TO ROLE connector_resources_provider;
   GRANT USAGE ON SECRET secretsdb.apiauth.servicenow_creds_oauth_code TO ROLE connector_resources_provider;
   

   Copy

After you install the Snowflake Connector for ServiceNow®, you grant this role to the connector.

While it’s recommended to create separate, more fine-grained role(s) to access ingested data after ingestion has started, you can use this role to query ingested data and you can extend it with additional privileges. Conversely, revoking role privileges to objects required by the connector or changes in role ownership of tables and views with ingested data isn’t allowed and will break the connector.

Installing the Snowflake Connector for ServiceNow®¶

To install the connector:

1. Create a database for the connector instance using Snowsight. For more information on how to create the database, see Installing and Configuring the Connector with Snowsight.

2. Navigate to the SQL worksheet.

3. Log in as a user with the ACCOUNTADMIN role. For example:

   USE ROLE ACCOUNTADMIN;
   

   Copy

4. Grant the custom role for the connector (the role that you created earlier) to the database that serves as an instance of the connector.

   For example, to grant the role named connector_resources_provider to the database my_connector_servicenow, run the following command:

   GRANT ROLE connector_resources_provider TO DATABASE my_connector_servicenow;
   

   Copy

5. Run the USE DATABASE command to use the database for the connector. For example:

   USE DATABASE my_connector_servicenow;
   

   Copy

6. Configure the connection to ServiceNow instance by using the CALL command to call the stored procedure named CONFIGURE_CONNECTION:

   CALL CONFIGURE_CONNECTION({
      'serviceNowUrl': '<servicenow_base_url>',
      'secret': '<secret_name>',
      'apiIntegrationName': '<api_integration_name>'
   })
   

   Copy

   Where:

   servicenow_base_url

   Specifies the URL of the ServiceNow instance that the connector should use. The URL should be in the following format:

   https://<servicenow_instance_name>.service-now.com
   

   Copy

   secret_name

   Specifies the fully qualified name of the secret object containing the credentials for authenticating to ServiceNow (the secret that you created earlier).

   You must specify the fully qualified name of the secret object in the following format:

   <database_name>.<schema_name>.<secret_name>
   

   Copy

   The names of the database, schema, and secret must be valid object identifiers.

   api_integration_name

   Specifies the name of the API integration for ServiceNow (the API integration that you created earlier).

   The name of the integration must be a valid object identifier.

   For example, to configure the connection to a ServiceNow instance that:

   • Has the URL https://myinstance.service-now.com.

   • Uses the secret stored in secretsdb.apiauth.servicenow_creds_oauth_code.

   • Uses the API integration named servicenow_api_integration.

   Run the following command:

    CALL CONFIGURE_CONNECTION({
       'serviceNowUrl': 'https://myinstance.service-now.com',
       'secret': 'SECRETSDB.APIAUTH.SERVICENOW_CREDS_OAUTH_CODE',
       'apiIntegrationName': 'SERVICENOW_API_INTEGRATION'
   });
   

   Copy

   If the connection was configured successfully, this stored procedure prints the following message:

   Connection configured successfully
   

   Copy

   Note

   Once the connector is configured, it’s not possible to change name of the passed secret and API integration. The connector references them by name. As a result, an attempt to drop or alter the name of these objects breaks the connector and stops it from working.

7. Start the connector as described in Starting the Snowflake Connector for ServiceNow®.

The newly created database is an instance of the connector and contains the following:

• Stored procedures that you use to configure the connector. See Setting Up Data Ingestion Using SQL Statements for more information.

• Views containing the logged messages and statistics for the connector. See About Monitoring the Connector for more information.

Starting and Stopping the Snowflake Connector for ServiceNow®¶

The following sections describe how to start, stop, and resume the connector.

CALL START_CONNECTOR(
  '<warehouse_name>',
  '<dest_db_schema>',
  '<name_of_journal_table>',
  '<connector_role>'
);

Successfully started the |SNC|. The connector can be configured now.

CALL RESUME_CONNECTOR();

Delegating Privileges for Configuring and Monitoring the Snowflake Connector for ServiceNow®¶

After you install the connector, only the ACCOUNTADMIN role can run the configuration procedures and access the views used to monitor the connector.

To delegate these privileges to another role, run the GRANT IMPORTED PRIVILEGES ON DATABASE ... TO ROLE ... command. This command uses the following syntax:

GRANT IMPORTED PRIVILEGES ON DATABASE <connector_name> TO ROLE <connector_config_role>;

Where:

connector_name

Specifies the name of the database that serves as an instance of the connector.

connector_config_role

Specifies the name of the role that should be granted privileges to configure and monitor the connector.

For example, to create a custom role named connector_config_role and grant that role the privileges to configure and monitor the connector instance named my_connector_servicenow, run the following command:

USE ROLE useradmin;
CREATE ROLE connector_config_role;

USE ROLE securityadmin;
GRANT IMPORTED PRIVILEGES ON DATABASE my_connector_servicenow TO ROLE connector_config_role;

Changing the Warehouse Used by the Connector (Optional)¶

If you want to change the warehouse used by the connector or add a dedicated warehouse, do this by calling:

CALL CONFIGURE_WAREHOUSE('<warehouse_name>');

Copy

Where:

warehouse_name

Specifies the name of the warehouse that the connector should use.

To configure the connector to run without using a dedicated warehouse, pass in an empty string for the warehouse name.

Next steps¶

After installing and configuring the connector, perform the steps described in Setting Up data ingestion for your ServiceNow® data.