Quickstarts

Access a Snowflake stage with WebDAV

Glenn Gillen

VP of Product, GTM

Quickstarts

Wouldn't it be nice to upload and manage files in a Snowflake stage the same way you would if they were files on your local machine? Wouldn't it be even better if it wasn't necessary to manage IP allow lists, open firewall ports, or setup services like PrivateLink to make that happen?

I'm about to walk you through setting this up, and it will take you less than 15 minutes to have everything working.

Introducing the Snowflake Stage Data with WebDAV Connector!

Quick links

Stage data with WebDAV Connector the Snowflake Marketplace

Snowflake 💙 WebDAV

WebDAV is a protocol for authoring and exchanging documents, based on the HTTP protocol.

One way to import data into Snowflake is to use the snow command line client or the snowflake-connector-python Python library to upload files and have them be mapped to tables for later processing. Unfortunately, both those tools require a specific installation, and an OAuth authentication, whereas HTTP commands can be easily executed with an ubiquitous utility like curl.

In this post I'm going to show you how to securely access a Snowflake stage in your account with WebDAV. In just a few minutes we will:

Create a stage associated to your Snowflake account.
Create a WebDAV server on Snowflake with a private encrypted connection.
Upload, list, download and remove files with WebDAV commands.
Mount the Snowflake stage as a filesystem on your local workstation.

Create a stage

This can be done with just a few commands in the Snowsight editor:

-- create a test database
CREATE DATABASE IF NOT EXISTS WEBDAV_TEST_DATABASE;
USE DATABASE WEBDAV_TEST_DATABASE;

-- create a test schema
CREATE SCHEMA IF NOT EXISTS WEBDAV_TEST_SCHEMA;
USE SCHEMA WEBDAV_TEST_SCHEMA;

-- create an internal stage
CREATE STAGE IF NOT EXISTS SECURE ENCRYPTION = (type = 'SNOWFLAKE_SSE');

The files contained in that stage can be listed with:

LIST @"SECURE";

And the stage should be empty for now!

Setup a WebDAV server inside Snowflake

We are now going to create a WebDAV server for the previous stage inside Snowflake. This will create a point-to-point connection between the two systems — without the need to expose any systems to the public internet!

Get the app

The Snowflake stage data with WebDAV Connector by Ockam is available in the Snowflake Marketplace.

Select a warehouse

The first screen you're presented with will ask you to select the warehouse to utilize to activate the app and can choose to change the application name.

Grant account privileges

Click the Grant button to the right of this screen. The app will then be automatically granted permissions to create a warehouse and create a compute pool.

Activate app

Once the permissions grants complete, an Activate button will appear. Click it and the activation process will begin.

Launch app

After the app activates you'll see a page that summarizes the privileges that the application now has. There's nothing we need to review or update on these screens yet, so proceed by clicking the Launch app button.

Setup your administrator access

We are now going to configure the application. We first need to:

Create an Ockam project and issue an enrollment ticket
Configure the user name and password which will be used to access the server.

Download Ockam Command

Run the following command on your local workstation:

This will install the Ockam Command and source in the required environment settings. If you've installed Ockam Command before you can skip this step.

Setup admin account

Once Ockam Command installation is complete you can run:

Wrapped up in this single ockam enroll command are several steps that will bootstrap your first project and get you ready to go. It will:

Generate an Ockam Identity and store its secret keys in a file system based Ockam Vault.
Create an account with Ockam Orchestrator.
Provision a trial Space and Project in the Orchestrator.
Make your Ockam Identity the administrator of your new Project.

Generate enrollment ticket for Snowflake

In this section we're going to provision an Ockam node that will run alongside the WebDAV started by the "Stage data with WebDAV - Connector" application, and provide one of the ends of our point-to-point connection.

We need to generate an enrollment ticket to allow a new Ockam Node to join the project that was just created. This node will run alongside the WebDAV server, inside your Snowflake account:

In this command we've set the new ticket to expire in 24 hours, and a usage count of 1. This means the generated ticket is valid for a single use, and that it's valid for 24 hours. A single usage ticket means there is low risk associated with mishandling this ticket after use; there's no means for an attacker to re-use it like an API token to access any system.

The Project Membership Credential that's issued will include
attributes that will be cryptographically attested to by the Project's Membership Authority. You can use these attributes to apply policies with Attribute Based Access Controls (ABAC) to allow or restrict specific actions and communication paths between nodes.

In this example we assign a single attribute of webdav.

The --relay webdav flag is a shortcut for creating a policy that allows this node to create a relay at the address webdav.

This relay will allow a WebDAV client to establish a secure end-to-end encrypted connection to the WebDAV server, running in your Snowflake account, without requiring you to expose any endpoints to the public Internet.

As the final part of this command we pipe the generated ticket to a file named webdav.ticket.

curl --proto '=https' --tlsv1.2 -sSfL \
  https://install.command.ockam.io \
  | bash && source "$HOME/.ockam/env"

Configuring the Snowflake app

Configure connection details

Click "Get started" to open the Snowflake setup screen.

Take the contents of the file webdav.ticket that we just created and paste it into "Provide the above Enrollment Ticket" form field in the "Configure app" setup screen in Snowflake.

Configure the stage access

In this screen, we need to enter:

The full name of the stage we want to access: WEBDAV_TEST_DATABASE.WEBDAV_TEST_SCHEMA.SECURE.

Grant privileges

To be able to authenticate with Ockam Orchestrator and then discover the route to our outlet, the Snowflake app needs to allow outbound connections to your Ockam project.

Toggle the Grant access to egress and reach your Project button and approve the connection by pressing Connect.

Toggle the Grant access to your user button, enter user for the user name and password for the user password.

Start the WebDAV server

Press "Next" and wait for the WebDAV server to start. Once started, you should be able to see the WebDAV server logs.

You can also notice a tab containing the logs for an Ockam node. That Ockam node is a portal outlet which:

Created a relay named webdav in the Ockam project.
Declares that only clients with the attribute webdav-client can access the relay.

Connect the WebDAV client

Generate an enrollment ticket for the WebDAV client

One end of our connection is now setup, it's time to connect the WebDAV server. We need to generate an enrollment ticket to allow another Ockam Node to join our project. This node will run on our local machine:

As we did with the earlier ticket, we've reduced the risk associated with mishandling a ticket by restricting it's permitted usage window. We've again set a usage count of 1, but with a tighter expiry time of just 2 hours.

This credential will include an attested attribute of webdav-client, corresponding to the attribute expected by the Ockam outlet node started in your Snowflake account.

The ticket is then saved to a file called webdav-client.ticket.

ockam project ticket \
  --usage-count 1 --expires-in 2h \
  --attribute webdav-client > webdav-client.ticket

Accessing the stage

Mounting as a filesystem (macOS)

This is where things get very cool, because we've got all the pieces in place to add our Snowflake Stage as though it's another drive/filesystem on our local workstation. I'm going to walk through how to do that on a Mac, but if you're on Windows you can follow these instructions and supply the same values I suggest below.

To get started open Finder.app, and either use the shortcut to connect to a server (⌘ + K) or select from the menu (Go > Connect to Server). In the dialog box that appears enter the server address of http://localhost:8001.

You can safely ignore any warning you may get about the connection being insecure. The Ockam portal connecting your system to your Snowflake Stage is secure and end-to-end encrypted. The warning is because the interface on your local machine is exposed as a http:// protocol rather than https://.

When presented with authentication screen enter in the name and password of user and password, as we configured earlier when configuring the Snowflake app.

Your Snowflake Stage will now be mounted just like any other filesystem! You can drag files from other folders, delete, or rename files just like you would any other files on your system. The changes will be reflected in your Snowflake Stage immediately.

Using the API (curl)

Next steps

With a few steps we've been able to mount our Snowflake Stage as a filesystem on our local workstate. Rather than connecting via a public interface, our client has connected over a private route using a mutually authenticated and end-to-end encrypted conenction. No ports have been exposed to the internet, no firewall ingress have been changed to allow access in, and yet we have two private systems now able to securely share files with each other.

To explore other capabilities that are possible with Ockam I'd suggest:

Secure & private connections to enterprise LLMs

Call a private API from Snowflake

Edit on Github

Table of content