Snowflake security integrations and the one with type of “API_AUTHENTICATION”

Published in

Snowflake Builders Blog: Data Engineers, App Developers, AI/ML, & Data Science

6 min readJun 7, 2024

Roses are in full blossom, Markham ON, June 6 2024

Snowflake security integration object represents a security service: authentication service or authorization service etc. (Just like storage integration represents an external storage.)

This security service can be either outside of Snowflake

SAML2 service,
OAuth2/OpenID Connect(OIDC) service

or inside Snowflake

Snowflake OAuth2 service
SCIM service

Security integration keeps the service connection information. Security integration is used to authenticate or authorize users when they try to access Snowflake data.

1 Recap of other types of security integration

1.1 Security integration for SAML2 service

For example, “SAML2” type security integration provide SAML2 service information — user login requests (on Snowsight) will be forwarded to do SSO authentication for Snowflake.

CREATE SECURITY INTEGRATION OKTAINTEGRATION
    TYPE = SAML2
    ENABLED = TRUE 
    SAML2_ISSUER = 'http://www.okta.com/exk4jzkgx0OBIxobd697'
    SAML2_SSO_URL = 'https://trial-8533056.okta.com/app/snowflake/exk4jzkgx0OBIxobd697/sso/saml'
    SAML2_PROVIDER = 'OKTA'
    SAML2_X509_CERT = 'MIIDqjCCApKgAw...f+LDjO'
    SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'OKTA SSO'
    SAML2_ENABLE_SP_INITIATED = TRUE;

(Check this post for details.)

1.2 Security integration for OAuth2 service

Another example for “OAUTH” type and “EXTERNAL_OAUTH” type security integration — they define OAuth server info and client ID and secret etc. OAuth client/application use it to request access token from given OAuth server before they can access Snowflake data.

-- 1 Snowflake OAuth - creates OAuth server in Snowflake account
CREATE SECURITY INTEGRATION sf_oauth_security_integration
  TYPE=OAUTH
  ENABLED=TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE='CONFIDENTIAL'
  OAUTH_REDIRECT_URI='https://oauth.pstmn.io/v1/browser-callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400;
describe integration sf_oauth_security_integration; 
-- describes OAuth client ID/secret associated with this secirity integration.

-- 2 External OAuth service
create security integration external_oauth_okta
    type = external_oauth
    enabled = true
    external_oauth_type = okta
    external_oauth_issuer = 'https://app1.okta.com'
    external_oauth_rsa_public_key = 'MIIBI...AQAB'
    external_oauth_token_user_mapping_claim = 'sub'
    external_oauth_snowflake_user_mapping_attribute = 'login_name'
    external_oauth_any_role_mode = 'ENABLE';

(check this post for details about Snowflake OAuth. Check Snowflake doc for external OAuth.)

Note, one external OAuth security integration maps to one OAuth issuer/service and vise versa. Snowflake uses issuer info in received access token to allocate according security integration with given “external_oauth_issuer” to handle data access requests.

1.3 Security integration for SCIM service

One more example is SCIM type security integration which starts a SCIM server in Snowflake. When SCIM client/app calls Snowflake SCIM API they need to provide access token which is generated by this security integration.

create or replace security integration my_scim_integration
    type=scim
    scim_client='generic'
    run_as_role='MY_SCIM_ROLE';

-- generate SCIM API access token
select system$generate_scim_access_token('MY_SCIM_INTEGRATION');

(Check this post for details.)

2 API_AUTHENTICATION type security integration

This is the last added type of security integration by now. This is used in Snowflake connector for ServiceNow workflow.

The purpose of this security integration is to provide OAuth2 server and client info to get access token. This OAuth2 access token will be used to authorize external API access.

Note, this security integration provides required secret when calling external API services from Snowflake. This is different from security integrations in section 1 that validate access to Snowflake data.

2.1 Secret for the API access

Production Rest API endpoint should be protected which means secret should be required when the API is being called. (for example ServiceNow APIs are being called to read table data.) API server side validate this secret to allow or deny access to specific API endpoint or a group of API endpoints.

The secret can be a static API key or a dynamic OAuth access token which expires after a period of time. In our case, we’ll prepare OAuth access token of ServiceNow API for Snowflake connector to access ServiceNow table data.

2.1.1 OAuth service at ServiceNow

We’ll need to create an “OAuth Application” in ServiceNow OAuth Registry service.

“All” -> search “application registry” and choose “Create an OAuth API endpoint for external clients”

This “OAuth Application”, maps to remote Snowflake connector, assigns OAuth client ID/secret for the connector and returns OAuth access token to a so called “Redirect URL” representing the connector client end. The connector would first ask access token and then can call ServiceNow API providing the access token.

When creating the “OAuth Application” as above, the required “Redirect URL” which is to accept access token can be find from target installed Snowflake connector in Snowflake. Once it’s created, take note for OAuth app name, auth and token end points, Client ID and secret.

Asking for access token is first step before calling ServiceNow API. There are multiple “workflows” or “grant types” to ask for access token: resource owner password credential grant, authorization code grant etc. Following curl command uses “resource owner password credential” workflow to ask for access token and refresh token from OAuth App in ServiceNow.

curl -d "grant_type=password"
    --data-urlencode "client_id=xxxx"
    --data-urlencode "client_secret=yyyy"
    --data-urlencode "username=servicenow_user1"        # ServiceNow user
    --data-urlencode "password=zzzz"
    -X POST https://app1.service-now.com/oauth_token.do # ServiceNow OAuth app

Output:
  {"access_token":"kM37xxxxL5b-b6riHrRABjHiaRxa9y6O8Ez6GFx-Q",
   "refresh_token":"HIuxw5yyyy9edyZ82jZqRCavEMTpTUPCy2I6uu6j0jVsVA",
   "scope":"useraccount",
   "token_type":"Bearer","expires_in":1800}

What happened:

1> Identifying myself as this client ID, I want to access ServiceNow data as this given user and with the permissions defined in the scope.

2> user authentication succeeds and consent is done for the scope — which data is allow to access.

3> OAuth access token is created for this client with info included

valid time (1800seconds)
scope
client info

4> Refresh token is created for this client (default valid for 8640000 seconds). Client can ask “https://app1.service-now.com/oauth_token.do” for new access token when it expires only providing this refresh token. No user/pwd (consent) needed. If refresh token expires, above “curl” command need to be used for new refresh token/access token with user/pwd.

2.1.2 Secret and “API_AUTHENTICATION” type security integration

Now look at the secret…it’s OAuth2 type secret that passes a refresh token to a security integration.

CREATE SECRET servicenow_oauth_secret
  TYPE = OAUTH2
  OAUTH_REFRESH_TOKEN = 'HIuxxxxVA'
  OAUTH_REFRESH_TOKEN_EXPIRY_TIME = '2024-07-14 18:00:00'
  API_AUTHENTICATION = servicenow_oauth_security_integration;

Refresh token is hardcoded — so when it expires, run above curl command to get new refresh token and update this secret. When only access token expires, ServiceNow API rejects Snowflake connector access. Snowflake connector uses refresh token to get new access token automatically when connector procedures are called.

Finally the security integration with “TYPE = API_AUTHENTICATION” is defined as follows: it defines what Client ID/secret to use to ask OAuth access token from the given OAuth server token endpoint.

CREATE or replace SECURITY INTEGRATION servicenow_oauth_security_integration
 TYPE = API_AUTHENTICATION
 AUTH_TYPE = OAUTH2
 OAUTH_CLIENT_AUTH_METHOD = CLIENT_SECRET_POST
 OAUTH_CLIENT_ID = 'xxxx'
 OAUTH_CLIENT_SECRET = 'yyyy'
 OAUTH_TOKEN_ENDPOINT = 'https://xxxx.service-now.com/oauth_token.do'
 ENABLED = TRUE;

This security integration is used by the secret to ask access token. The secret then returns access token to ServiceNow API call request (Snowflake API integration).

2.2 Snowflake API integration to call ServiceNow API for data access

Snowflake connector for ServiceNow calls ServiceNow API to pull data into Snowflake. The connector establishes connection from Snowflake to ServiceNow via API integration.

-- Connecting to given ServiceNow instance URL from Snowflake
CALL CONFIGURE_CONNECTION({
   'serviceNowUrl': 'https://xxxx.service-now.com',
   'secret': 'secretsdb.apiauth.servicenow_oauth_secret',
   'apiIntegrationName': 'servicenow_api_integration'
});

How is this Snowflake external API integration is defined?

It allows to access ServiceNow instance URL with a given secret. This secret, as described above, returns OAuth access token required by this SeviceNow API call via “API_AUTHENTICATION” type security integration.

CREATE API INTEGRATION servicenow_api_integration
  API_ALLOWED_PREFIXES = ('https://xxxx.service-now.com')
  ALLOWED_AUTHENTICATION_SECRETS = ('secretsdb.apiauth.servicenow_oauth_secret')
  ENABLED = TRUE;

So “API_AUTHENTICATION” type security integration in Snowflake connects to given OAuth service by identifying self as given client ID. The purpose is to get access token for external API calls initialed by Snowflake objects.

Happy Reading!