External network access and private connectivity on Microsoft Azure

This topic provides configuration details to set up outbound private connectivity to an external service by way of external network access. The primary differences between the outbound public connectivity and outbound private connectivity configurations are that, with private connectivity, you must do the following operations:

  • Create a private connectivity endpoint. This step requires the ACCOUNTADMIN role.

  • Create the network rule to use the PRIVATE_HOST_PORT property. This property includes the Azure URL and port number, which enables the connection from Snowflake to Microsoft Azure to go through the Microsoft Azure internal network, avoiding the public Internet.

Billing and pricing

This feature is available for Snowflake accounts that are Business Critical Edition (or higher).

You pay for private endpoint and data processed charges, which are aggregated and appear as Outbound Privatelink in your bill.

Configure external network access

These steps are unique to using outbound private connectivity with external network access on Microsoft Azure:

  1. Call the SYSTEM$PROVISION_PRIVATELINK_ENDPOINT system function to provision a private connectivity endpoint in your Snowflake VNet to enable Snowflake to connect to an external service using private connectivity:

    USE ROLE ACCOUNTADMIN;

SELECT SYSTEM$PROVISION_PRIVATELINK_ENDPOINT(
  '/subscriptions/1111-22-333-4444-55555/resourceGroups/external-access/providers/Microsoft.Sql/servers/externalaccessdemo',
  'externalaccessdemo.database.windows.net',
  'sqlServer'
);
    Copy

  2. Create a database and schemas to store the network rule, secret, and procedure:

    CREATE DATABASE ext_network_access_db;
CREATE SCHEMA secrets;
CREATE SCHEMA network_rules;
CREATE SCHEMA procedures;
    Copy

  3. Create a network rule, specifying the PRIVATE_HOST_PORT property to enable private connectivity:

    CREATE OR REPLACE NETWORK RULE ext_network_access_db.network_rules.azure_sql_private_rule
   MODE = EGRESS
   TYPE = PRIVATE_HOST_PORT
   VALUE_LIST = ('externalaccessdemo.database.windows.net');
    Copy

  4. Create a secret to securely store the access credentials:

    CREATE OR REPLACE SECRET ext_network_access_db.secrets.secret_password
   TYPE = PASSWORD
   USERNAME = 'my-username'
   PASSWORD = 'my-password';
    Copy

  5. Create an external access integration, specifying the network rule from the previous step:

    CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION azure_private_access_sql_store_integration
   ALLOWED_NETWORK_RULES = (ext_network_access_db.network_rules.azure_sql_private_rule)
   ALLOWED_AUTHENTICATION_SECRETS = (ext_network_access_db.secrets.secret_password)
   ENABLED = TRUE;
    Copy

  6. Create a procedure to connect to the external service:

    CREATE OR REPLACE PROCEDURE ext_network_access_db.procedures.connect_azure_sqlserver()
   RETURNS TABLE()
   LANGUAGE PYTHON
   RUNTIME_VERSION = 3.10
   HANDLER = 'connect_sqlserver'
   EXTERNAL_ACCESS_INTEGRATIONS = (azure_private_access_sql_store_integration)
   SECRETS = ('cred' = ext_network_access_db.secrets.secret_password)
   IMPORTS=('@demo/pytds.zip')
   PACKAGES = ('snowflake-snowpark-python','pyopenssl','bitarray','certifi')
AS $$
import pytds
import certifi
import _snowflake
from snowflake.snowpark import types as T

def connect_sqlserver(session):
   server = 'externalaccessdemo.database.windows.net'
   database = 'externalaccess'
   username_password_object = _snowflake.get_username_password('cred');


   # Create a connection to the database
   with pytds.connect(server, database, username_password_object.username, username_password_object.password, cafile=certifi.where(), validate_host=False) as conn:
         with conn.cursor() as cur:
            cur.execute("""
            SELECT O.OrderId,
                  O.OrderDate,
                  O.SodName,
                  O.UnitPrice,
                  O.Quantity,
                  C.Region
            FROM Orders AS O
            INNER JOIN Customers AS C
               ON O.CustomerID = C.CustomerID;""")
            rows = cur.fetchall()

            schema = T.StructType([
                  T.StructField("ORDER_ID", T.LongType(), True),
                  T.StructField("ORDER_DATE", T.DateType(), True),
                  T.StructField("SOD_NAME", T.StringType(), True),
                  T.StructField("UNIT_PRICE", T.FloatType(), True),
                  T.StructField("QUANTITY", T.FloatType(), True),
                  T.StructField("REGION", T.StringType(), True)
               ])

            final_df = session.createDataFrame(rows, schema)

            return final_df
   $$;
    Copy

  7. Call the procedure to connect to the external service:

    CALL ext_network_access_db.procedures.connect_azure_sqlserver();
    Copy

Repeat these steps for each external network access configuration that requires private connectivity.

If you no longer need the private connectivity endpoint for the external network access integration, call the SYSTEM$DEPROVISION_PRIVATELINK_ENDPOINT system function.

Language: English