Installing and configuring the connector Using SQL Commands¶
The Snowflake connector for ServiceNow® is subject to the Connector Terms.
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:
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.
Create an application registry in ServiceNow and use it to configure the connector.
Login to your ServiceNow instance, then select Homepage.
Search for OAuth, then select Application Registry.
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:
In ServiceNow, enter a name for the OAuth application registry in the Name field.
In ServiceNow, in the Redirect URL field, enter the following:
https://apps-api.c1.<cloud_region_id>.<cloud>.app.snowflake.com/oauth/complete-secret
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
, orgcp
).
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).
In ServiceNow, select Submit.
The OAuth application registry appears in the list of application registries.
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:
Ensure that you have performed the tasks outlined in Setting Up OAuth.
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
Where
instance_name
Specifies the name of your ServiceNow instance.
client_id
andclient_secret
Specify the values you obtained when setting up the ServiceNow endpoint.
username
andpassword
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}
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.comThe 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;
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.
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;
Using the SECURITYADMIN system role, run the GRANT <privileges> TO ROLE command to grant the following privileges to the custom role:
USAGE and CREATE SECRET on the schema that you created for the secret
USAGE on the security integration that you created earlier
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;
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;
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
toOAUTH2
.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 namedservicenow_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;
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 toPASSWORD
. SetUSERNAME
andPASSWORD
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';
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.
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;
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;
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
orsys_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
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
Note
You do not need to set any of the other parameters in the CREATE API INTEGRATION statement.
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:
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;
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 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
databaseUSAGE, CREATE TABLE, and CREATE VIEW on the
dest_db.dest_schema
schemaUSAGE on the
servicenow_api_integration
integrationUSAGE on the
secretsdb
databaseUSAGE on the
secretsdb.apiauth
schemaUSAGE 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;
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:
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.
Navigate to the SQL worksheet.
Log in as a user with the ACCOUNTADMIN role. For example:
USE ROLE ACCOUNTADMIN;
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 databasemy_connector_servicenow
, run the following command:GRANT ROLE connector_resources_provider TO DATABASE my_connector_servicenow;
Run the USE DATABASE command to use the database for the connector. For example:
USE DATABASE my_connector_servicenow;
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>' })
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
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>
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' });
If the connection was configured successfully, this stored procedure prints the following message:
Connection configured successfully
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.
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.
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>'
);
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>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 Snowflakethe ServiceNow table named
sys_audit_delete
for tracking deleted recordsthe 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' );
If the connector was started successfully, this stored procedure displays the following message:
Successfully started the |SNC|. The connector can be configured now.
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();
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();
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>;
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;
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>');
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.
Next steps¶
After installing and configuring the connector, perform the steps described in Setting Up data ingestion for your ServiceNow® data.