I need some help trying to access an external API from a Snowflake SQL Worksheet. Here is my complete code with building the integration and the python function that makes the API call:
USE ROLE SYSADMIN;
USE SCHEMA TALKDESK.BRONZE
CREATE OR REPLACE NETWORK RULE api_network_rule
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('belnick.talkdeskid.com');
CREATE OR REPLACE SECURITY INTEGRATION talkdesk_security_integration
TYPE = api_authentication
AUTH_TYPE = oauth2
OAUTH_CLIENT_AUTH_METHOD = client_secret_post
OAUTH_CLIENT_ID = <ID>
OAUTH_CLIENT_SECRET = <SECRET>
OAUTH_TOKEN_ENDPOINT = 'https://belnick.talkdeskid.com/oauth/token'
OAUTH_GRANT = 'CLIENT_CREDENTIALS'
OAUTH_ALLOWED_SCOPES = ('[]')
ENABLED = true;
CREATE OR REPLACE SECRET api_key_secret
TYPE = OAUTH2
API_AUTHENTICATION = talkdesk_security_integration
OAUTH_REFRESH_TOKEN = <TOKEN>
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION talkdesk_integration
ALLOWED_NETWORK_RULES = (api_network_rule)
ALLOWED_AUTHENTICATION_SECRETS = (api_key_secret)
ENABLED = true;
CREATE OR REPLACE FUNCTION get_data()
RETURNS text
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'refresh_token'
EXTERNAL_ACCESS_INTEGRATIONS = (talkdesk_integration)
PACKAGES = ('requests')
SECRETS = ('api_key'= api_key_secret)
AS
$$
import _snowflake
import requests
import json
session = requests.Session()
api_key = _snowflake.get_oauth_access_token('api_key')
def refresh_token():
url="https://belnick.talkdeskid.com/oauth/token"
payload = { "grant_type": "client_credentials" }
headers = {
"accept": "application/json",
"Authorization": f"Basic {api_key}",
"content-type": "application/x-www-form-urlencoded"
}
response = session.post(url, data=payload, headers=headers)
return response.json()['access_token']
$$;
Everything works fine until the function creation. The error that I get is the following:
SQL execution error: OAuth2 Access token request failed with error 'Failed to parse OAuth server response'.
The refresh_token
function works just fine when executed using python in an IDE.
Does anyone have any idea about what could be wrong with my configurations?