Snowflake Configuration

The following sections will configure Snowflake to access the API Gateway.

Login to Snowflake as ACCOUNTADMIN

Ensure that the current user can assume the Account Administrator role. This role must be created.

Create the Snowflake API Integration Object

Create Snowflake integration object to allow invoking Protegrity Function App from Snowflake.

1. From the Snowflake console worksheet, select the role ACCOUNTADMIN.

2. Paste the DDL test below.

3. Replace <Azure Tenant ID> and <API Gateway URL> with Azure Tenant ID and API Gateway URL from Install Protect Function via Azure Resource Manager.

4. Replace <Entra ID Application ID> with Entra ID Application ID from Register an Entra ID App.

5. Run DDL

   create or replace api integration protegrity_api
     api_provider = azure_api_management
     azure_tenant_id = '<Azure Tenant ID>'
     azure_ad_application_id = '<Entra ID Application ID>'
     enabled = true
     api_allowed_prefixes = ('<API Gateway URL>');
   

   Note

   The name of the object protegrity_api can be replaced with a name of your choice, however the name you choose must be used consistently throughout the installations below.

Link Snowflake to Active Directory

Snowflake will create an application that must be linked to Azure Active Directory giving it permission to access the API Management and Function App instances. Information about this integration should be retrieved from the Snowflake instance.

To link Snowflake to AD:

1. Run the following query in the console.

   DESCRIBE API INTEGRATION protegrity_api;
   

2. Record the following output values from the resulting query:

   • Azure Multi-Tenant App Name: ___________________
   • Azure Consent URL: ___________________

3. An Azure Account Administrator is required for the remaining steps to grant the Snowflake application access to the Azure Active Directory.

4. Open the Azure Consent URL in a web browser.

5. If redirected to the Snowflake website, then skip to step 9.

6. If prompted to login then enter the credentials of an Azure user or administrator.

7. Click Accept to grant Snowflake access to the Azure account.

8. Additional instructions may be shown if the current user is not authorized to add the Snowflake application.

9. Navigate to the Enterprise applications service within the Azure portal.

10. Select the application that matches the Azure Multi-Tenant App Name.

    Note

    If you do not find an exact match for the AZURE_MULTI_TENANT_APP_NAME, then search again using only the first several characters of this name (if the name contains an underscore, then do not include the underscore or any characters after the underscore)

11. Record the Application ID below.

    Azure Multi-Tenant App ID: ___________________

Update API policies

To update API policies:

1. Navigate to API Management services in the Azure portal.

2. Select the instance created previously.

3. Select the APIs option under APIs.

4. Select All APIs.

5. Click the </> icon next to Policies under Inbound processing.

6. Add the <required-claims> using the example below replacing <Azure Tenant ID>, <Entra ID Application ID>, and <Azure Multi-Tenant App ID> with values recorded in earlier steps.

   <policies>
       <inbound>
           <validate-jwt header-name="Authorization" failed-validation-httpcode="401">
               <openid-config url="https://login.microsoftonline.com/<Azure Tenant ID>/.well-known/openid-configuration" />
               <required-claims>
                   <claim name="aud" match="all">
                       <value><Entra ID Application ID></value>
                   </claim>
                   <claim name="appid" match="all">
                       <value><Azure Multi-Tenant App ID></value>
                   </claim>
               </required-claims>
           </validate-jwt>
       </inbound>
       <backend>
           <forward-request timeout="30" fail-on-error-status-code="true" />
       </backend>
       <outbound></outbound>
       <on-error>
       </on-error>
   </policies>
   

7. Click Save.

Update API Management Backend Function Key

API management allows specifying function key in the request to function app backend.

To update API function key:

1. From Azure console, navigate to Function App and select protect function app.

2. Go to Functions > App keys and record the value of default key under Host Keys (All functions) as <Protect Function app key>.

3. From the API management view, select the Backends option under APIs.

4. Select backend1.

5. Select the Authorization credentials option under Settings.

6. Under Headers locate the x-functions-key header.

7. For x-functions-key Value use the <Protect Function app key> recorded in step above.

8. Click Save.

Update Function App Configuration

The Function App configuration can be updated to verify that the request is coming from Snowflake.

To update Function App configuration:

1. Navigate to the Function App service in the Azure Portal and select the Protegrity-Protect-<name> item (there may be more than one).

2. Navigate to Settings > Environment variables.

3. Click Add.

4. Enter OPENID_APPID in the Name field.

5. Enter the value for Azure Multi-Tenant App ID in the Value field.

6. Click Apply then Apply and Confirm to finalize.

7. Repeat the above steps if there are multiple Function Apps.

Test Connectivity

Verify connectivity from Snowflake to Protect Function app.

1. Access the Snowflake SQL console.

2. Copy and paste the following snippet into a worksheet.

   CREATE OR REPLACE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_SAMPLE_POLICY(VAL VARCHAR)
       RETURNS VARCHAR(16777216)
       IMMUTABLE
       API_INTEGRATION = PROTEGRITY_API
       HEADERS = (
         'X-Protegrity-HCoP-Rules'=
         '{"jsonpaths":[{"op_type":"unprotect","data_element":"alpha"}]}'
       )
       CONTEXT_HEADERS = (CURRENT_USER,CURRENT_TIMESTAMP,CURRENT_ACCOUNT)
       COMMENT='Unprotects text using an alpha token type.'
       AS '<API Gateway URL>/api/Protect';
   

3. Replace the placeholder value indicated substituting your API Gateway URL captured in Install Protect Function via Azure Resource Manager

4. Run the following protect in the console:

   select pty_unprotect_sample_policy('UtfVk UHgcD!');
   

Troubleshooting

To review live requests, navigate to Application Insights service and select item with the same name as the protect function. Under Investigate, select Live Metrics. Wait for the dashboard to load, then go to Sample Telemetry pane on the right and look for the requests in question.

To review the full history of requests from Application Insights under Monitoring select Logs:

1. Select the Tables tab.
2. Hover over one of the table names under Application Insights header, for example exceptions.
3. Click on See preview data.
4. Click Use in editor.

You can also run the query directly in the query editor. For instance to select the 10 latest exceptions run the following query.

exceptions 
| where timestamp > ago(24h)
| order by timestamp
| limit 10

For more helpful platform-specific symptoms, refer to the Snowflake documentation: Platform Specific Symptoms