Call a private API from Snowflake
Glenn Gillen
VP of Product, GTM
Your data is business critical, and business critical data shouldn't be accessible via public API endpoints. Too often though we're forced to compromise on security in the name of convenience. A common example of that is when we want to allow third-party services to access self-hosted APIs that exist within our own private network.
If you're a Snowflake customer you don't need to compromise!
In under 10 minutes I'll show you how to setup a private point-to-point connection that doesn't require you to manage IP allow lists, open firewall ports, or setup services like PrivateLink.
Quick links
No more public APIs
Whether you're providing a custom built interface to your business systems via an API or self-hosting a service with an accessible API (e.g., JIRA, GitHub, etc.) there's a good reason these are deployed within a private subnet. Granting access to this "internal" network is often easy for team members, you get them to connect first to a VPN and then they're able to access all of your corporate assets. When you're only trying to connect two services there's no need to be connecting two entire networks and trying to make them look like one. You can instead establish a point-to-point connection between just the services that need to connect.
Once a private point-to-point connection is established from Snowflake to your system(s), you'll be able to query your private data using SQL. Imagine being able to join data in your Snowflake warehouse to data in your private APIs! You could even join results from something like JIRA, or join data across multiple APIs as though that were tables in your warehouse! Or you could send POST requests to insert new data. All from Snowflake. All using SQL. All without exposing any of your systems to the public internet.
In this post I'm going to show you how to securely access a private API, hosted in a private network, in just a few minutes. We will:
- Install and configure the Call Private API connector.
- Make a stored procedure call to retrieve data as if it was a table
select. - Make a stored procedure call to post data to the API.
Install and configure the Call Private API Connector
Get the app
The Call Private API 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.
Configuration
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.
Configuring the application
Configure connection details
Click "Get started" to open the Snowflake setup screen.
Take the contents of the file snowflake.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 API access
In this screen we need to select an authorization method. For this tutorial you
can select Digest and continue. The other options available to use are:
NoneNo credentials are required to access the private API.BasicThe private API is accessible with a user name and password.DigestThe private API is accessible with a user name and password, but they are not transmitted in clear text.Bearer tokenThe private API is accessible with a token passed with anAuthorizationheader.API KeyThe private API is accessible with an API Key passed with a specific extension header (for exampleX-api-key).
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 your credentials button, enter user for the user name and password for the user password.
Toggle the Grant access to your Ockam Project button and approve the connection by
pressing Connect.
Start the application
Press "Next" and wait for the application to start. Once started, you should be able to see the server logs.
You can also notice a tab containing the logs for an Ockam node. That Ockam node is a portal inlet:
- Which accesses a relay named
apiin the Ockam project. - Declares that only services with the attribute
apican be connected to the inlet.
Setup an Ockam outlet on the API side
Testing the connection
Next steps
In summary, we are now able to securely access a private API, using Snowflake stored procedures, without exposing any ports to the internet.
If you'd like to explore some other capabilities of Ockam I'd recommend:
- Creating private point-to-point connections with any database
- Adding security as a feature in your SaaS product
Previous Article
How to build in security as a SaaS feature
Next Article
Authenticate & authorize every access decision