Installing and Configuring the Connector Using SQL Commands

This topic describes how to use SQL commands to install and configure the connector. It assumes that you have already performed the procedures outlined in Preparing Your ServiceNow Instance.

Setting Up OAuth

Note

If you plan to use basic authentication instead of OAuth, you can skip this section and continue to Creating a Secret Object

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 ServiceNow Connector, 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:

      Displays the Application Registry page in ServiceNow.
    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;
Copy

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 Schema for the Secret Objects

First, create a database and schema to store the secret object by running the CREATE DATABASE and CREATE SCHEMA commands. The names of the schema and database must be valid object identifiers.

For example, to create the database secretsdb and the schema apiauth for the secret object, run the following commands:

USE ROLE accountadmin;
CREATE DATABASE secretsdb;
CREATE SCHEMA apiauth;
Copy

Creating a Custom Role to Manage the Secret

Next, create a custom role to manage the secret (assuming that you do not want to use an existing role) and grant the role the privileges needed to create the secret.

  1. Using the USERADMIN system role, run the CREATE ROLE command to create a custom role to manage the secret. For example, to create the custom role secretadmin for managing the secret, run the following commands:

    USE ROLE useradmin;
    CREATE ROLE secretadmin;
    
    Copy
  2. Using the SECURITYADMIN system role, run the GRANT <privileges> TO ROLE command to grant the following privileges to the custom role:

    For example:

    USE ROLE securityadmin;
    GRANT USAGE ON DATABASE secretsdb TO ROLE secretadmin;
    GRANT USAGE ON SCHEMA secretsdb.apiauth TO role secretadmin;
    GRANT CREATE SECRET ON SCHEMA secretsdb.apiauth TO role secretadmin;
    GRANT USAGE ON INTEGRATION servicenow_oauth TO role secretadmin;
    
    Copy
  3. Using the USERADMIN system role, run the GRANT <privileges> TO ROLE command to grant the custom role to the user who creates the secret. For example, to grant the role to the user servicenow_secret_owner, run the following commands:

    USE ROLE useradmin;
    GRANT ROLE secretadmin TO user servicenow_secret_owner;
    
    Copy

Creating a Secret

Next, create a secret to enable Snowflake to authenticate to the ServiceNow instance using OAuth with the code grant flow.

Note

If you plan to use basic authentication instead of OAuth, see the note below instead.

To create a secret object, run the CREATE SECRET command with the following parameters:

  • Set TYPE to OAUTH2.

  • Set OAUTH_REFRESH_TOKEN to the OAuth refresh token that you retrieved in Generating OAuth Refresh Token.

  • Set OAUTH_REFRESH_TOKEN_EXPIRY_TIME to the refresh token expiration timestamp in UTC timezone. You can calculate this by adding the token lifespan from ServiceNow to the date when the token was issued. By default, the token expires in 100 days.

  • Set API_AUTHENTICATION to the name of the security integration that you created in Creating a Security Integration:

For example, to create a secret named service_now_creds_oauth_code that uses the security integration named servicenow_oauth, run these commands:

USE ROLE secretadmin;
USE SCHEMA secretsdb.apiauth;
CREATE SECRET servicenow_creds_oauth_code
  TYPE = OAUTH2
  OAUTH_REFRESH_TOKEN = '34n;vods4nQsdg09wee4qnfvadH'
  OAUTH_REFRESH_TOKEN_EXPIRY_TIME = '2022-01-06 20:00:00'
  API_AUTHENTICATION = servicenow_oauth;
Copy

To modify the properties of an existing secret (e.g. to update the OAuth refresh token), use the ALTER SECRET command.

Note

If you plan to use basic authentication (rather than OAuth), run the CREATE SECRET command to create a secret with TYPE set to PASSWORD. Set USERNAME and PASSWORD to the username and password of the ServiceNow user that you plan to use to authenticate to the ServiceNow instance. For example:

USE ROLE secretadmin;
USE SCHEMA secretsdb.apiauth;
CREATE SECRET servicenow_creds_pw
  TYPE = PASSWORD
  USERNAME = 'jsmith1'
  PASSWORD = 'W3dr@fg*7B1c4j';
Copy

If multi-factor authentication is enabled for this user, you must provide the MFA token together with password as described in REST API in the ServiceNow documentation.

If you need to modify the properties of an existing secret (e.g. to update the password), use the ALTER SECRET command.

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.

Note

To run the Snowflake Connector for ServiceNow without a dedicated warehouse, pass in an empty string when asked to supply a value for the 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:

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

Note

If you are reinstalling the connector, you can reuse the schema that you created for the previous installation of the connector. This is possible if the previous installation of the connector has already loaded data and you want to continue loading data into the same tables.

To continue loading data, do not modify the schema before reinstalling the connector. Do not change the definitions of the tables created by the previous installation of the connector.

Later, when you call the ENABLE_TABLES stored procedure, the stored procedure verifies that these required objects already exist and does not attempt to recreate them.

To determine where to resume the ingestion of data, the connector examines the event log table:

  • If the connector finds any rows of the type UPDATE or DELETE in the event log table, the connector uses the timestamp of the newest row (from the sys_updated_on or sys_created_on column) as the starting point for the ingestion.

  • If there are no rows of UPDATE or DELETE type in the event table, the connector uses the oldest value of the event_date column in the event log table.

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

Note

You do not need to set any of the other parameters in the CREATE API INTEGRATION statement.

Configuring Logging for the Connector

The ServiceNow Connector for Snowflake 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:

    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.

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. Set the FIREWALL_CONFIGURATION database property on the installed database. Include the URL prefix for your ServiceNow instance.

    For example, if your database is named my_connector_servicenow and the ServiceNow instance name is my-instance, run the following command:

    ALTER DATABASE my_connector_servicenow SET FIREWALL_CONFIGURATION = ('https://my-instance.service-now.com')
    
    Copy
  6. Run the USE DATABASE command to use the database for the connector. For example:

    USE DATABASE my_connector_servicenow;
    
    Copy
  7. 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.

  8. 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:

Starting and Stopping the Snowflake Connector for ServiceNow

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

Starting the Snowflake Connector for ServiceNow

Start the connector by calling the procedure named START_CONNECTOR. The arguments for this stored procedure are described below:

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

Where:

warehouse_name

Specifies the name of the warehouse for the connector.

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

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

dest_db_schema

Specifies the fully qualified name of the schema for the ServiceNow data in Snowflake (the schema that you created earlier).

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

<database_name>.<schema_name>
Copy

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

name_of_journal_table

Specifies the name of the table that contains information about deleted records. Refer to Preparing Your ServiceNow Instance for more information.

Note that information on deleted records is available only for tables that you set up to propagate deleted records.

To prevent the propagation of deleted records, specify an empty string for this argument.

connector_role

Specifies the name of the custom role for the connector role that you created earlier.

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

For example, to start the connector named my_connector_servicenow that uses the following:

  • the warehouse named servicenow_conn_warehouse

  • the schema named dest_db.dest_schema for the ServiceNow data in Snowflake

  • the ServiceNow table named sys_audit_delete for tracking deleted records

  • the custom role named connector_resources_provider

Run the following commands:

CALL START_CONNECTOR(
 'SERVICENOW_CONN_WAREHOUSE',
 'DEST_DB.DEST_SCHEMA',
 'sys_audit_delete',
 'CONNECTOR_RESOURCES_PROVIDER',
);
Copy

If the connector was started successfully, this stored procedure displays the following message:

Successfully started the Snowflake Connector for ServiceNow. The connector can be configured now.
Copy

Note

Once the connector is started, it’s not possible to rename passed warehouse, destination database, destination schema and custom role for the connector. 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.

Instead of renaming the warehouse, use CONFIGURE_WAREHOUSE stored procedure to change the warehouse used by the connector.

Stopping the Snowflake Connector for ServiceNow

To stop all tasks started by the connector, call the STOP_CONNECTOR stored procedure:

CALL STOP_CONNECTOR();
Copy

Note

The STOP_CONNECTOR stored procedure is not available until you resume the connector by calling the START_CONNECTOR stored procedure.

Stopping the connector disables interaction with it (e.g. enabling/disabling tables or configuring the connector) until the connector is resumed by calling the RESUME_CONNECTOR stored procedure.

Stopping the connector also stops any cost generation for the connector.

Resuming the Snowflake Connector for ServiceNow

To resume all tasks stopped by STOP_CONNECTOR stored procedure, call RESUME_CONNECTOR stored procedure:

CALL RESUME_CONNECTOR();
Copy

Note

The RESUME_CONNECTOR stored procedure is not available until you resume the connector by calling the START_CONNECTOR stored procedure.

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.

Note

The custom role that you created in Creating a Custom Role for the Connector does not have the privileges to run the configuration procedures or access the monitoring views.

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>;
Copy

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.

Note

This does not grant the role the privileges to drop the connector_name database or access the schema containing the ServiceNow data in Snowflake.

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;
Copy

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.

Note

Before configuring the connector to use a different warehouse, verify that the custom role used by the connector has the USAGE privilege for the new warehouse.