johndutchover / snowflake-azure-ipwhitelisting

Script to Whitelist Azure IPs automatically in Snowflake Network Policy

Snowflake Azure IP Whitelisting

This tool will whitelist the Azure IPs from the published Azure IP Ranges and Service Tags by Microsoft. The Script runs whitelisting only if the IPs are changed based on the file checksum.

Features

• Downloads Azure IPs and runs Whitelisting only on IP changes (based on file checksum)
• Supports Key pair Authentication
• Config driven to support multiple Azure Service Tag IDs

Setup

Snowflake

• Create separate Database SNOWUTILS and Schema IP_WHITELIST for the Script

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS SNOWUTILS;
CREATE SCHEMA IF NOT EXISTS SNOWUTILS.IP_WHITELIST;

• Create Tables IP_CHECKSUM

USE ROLE SYSADMIN;
USE DATABASE SNOWUTILS;
USE SCHEMA IP_WHITELIST;

CREATE TABLE IP_CHECKSUM (
    DATE TIMESTAMP_NTZ(9),
    CHECKSUM VARCHAR(16777216)
);

• Add the permissions for the objects to role SECURITYADMIN

GRANT USAGE ON DATABASE SNOWUTILS TO ROLE SECURITYADMIN;
GRANT USAGE ON SCHEMA SNOWUTILS.IP_WHITELIST TO ROLE SECURITYADMIN;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLE SNOWUTILS.IP_WHITELIST.IP_CHECKSUM TO ROLE SECURITYADMIN;

Network Policy

• Create the Network Policy if it does not exist. Refer [Snowflake's documentation] (https://docs.snowflake.com/en/sql-reference/sql/create-network-policy.html)

USE ROLE $role_name;
CREATE [ OR REPLACE ] NETWORK POLICY <name>
   ALLOWED_IP_LIST = ( [ '<ip_address>' ] [ , '<ip_address>' , ... ] )
   [ BLOCKED_IP_LIST = ( [ '<ip_address>' ] [ , '<ip_address>' , ... ] ) ]
   [ COMMENT = '<string_literal>' ]

For Security restriction, it is recommended to use SECURITYADMIN or create separate role dedicated for Network automation. It is also recommended to have a separate Network Policy for Azure Services associated with particular service accounts.

• Create a Role NETWORKADMIN

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS NETWORKADMIN;

• Provide NETWORKADMIN role OWNERSHIP access on the Network Policy

GRANT OWNERSHIP on NETWORK POLICY <name> TO ROLE $role_name COPY CURRENT GRANTS;

Service User Account

• Create a Service Account User if it does not exist. Refer [Snowflake's documentation] (https://docs.snowflake.com/en/sql-reference/sql/create-user.html)
• Generate Key pair Authentication with encrypted private key. Refer [Snowflake's documentation] (https://docs.snowflake.com/en/user-guide/key-pair-auth.html)

Script

• Install and Configure Python

Tested with Python 3.8.10

• Update the config.json with the Snowflake details

Key	Description	Example
snowflake.account	URL of the Snowflake Account	abc12345.us-east-1.azure
snowflake.user	Username of the Service Account	srv-ipwhitelist
snowflake.pkey	Path of the Private Key	rsa_key.p8
snowflake.database	Configuration Database	SNOWUTILS
snowflake.warehouse	Warehouse to execute the Script	COMPUTE_WH
snowflake.schema	Configuration Schema	IP_WHITELIST
snowflake.role	Role of the Service Account	SECURITYADMIN
whitelist.url	URL of the Azure IP JSON Files (https://docs.microsoft.com/en-us/azure/virtual-network/service-tags-overview#discover-service-tags-by-using-downloadable-json-files)
whitelist.policy	Snowflake Network Policy Name	Azure_Policy
whitelist.keys	ID of the Azure Services from the Service Tags. It is recommended to filter only IDs for the services needed

• Set the environment variable PRIVATE_KEY_PASSPHRASE with the Password of encrypted Key pair

Windows: SET PRIVATE_KEY_PASSPHRASE=Password

Linux: EXPORT PRIVATE_KEY_PASSPHRASE=Password

• Install the Python Dependencies

pip install -r requirements.txt

• Execute the python script ip_whitelist.py python ip_whitelist.py

• Schedule the script using a scheduler

Contribution

This project uses GitHub Issues to track bugs and feature requests. Please search the existing issues before filing new issues to avoid duplicates. For new issues, file your bug or feature request as a new Issue.

Please use PR against an bug or feature request for Contribution.

Third Party Packages

The script would not be possible without the following third party packages and all those that maintain and have contributed.

Package	License	URL
snowflake-connector-python	Apache 2.0	https://pypi.org/project/snowflake-connector-python/
snowflake-connector-python[pandas]	Apache 2.0	https://pypi.org/project/snowflake-connector-python/
beautifulsoup4	MIT	https://pypi.org/project/beautifulsoup4/
requests	Apache 2.0	https://pypi.org/project/requests/

Legal

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this tool except in compliance with the License. You may obtain a copy of the License at: http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

This is an Open Source developed tool and not an official Microsoft or Snowflake offering. This tool is not endorsed by Microsoft or Snowflake.

This project may contain trademarks or logos for projects, products, or services. Any use of third-party trademarks or logos are subject to those third-party’s policies. SNOWFLAKE is a trademark of Snowflake Computing, Inc in the United States and/or other countries. MICROSOFT is a registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.