Any project involving data replication has at least two interesting technical problems:

1. How do you reliably and securely connect point A to point B?

2. What strategy do you use to merge and reconcile created, updated, and deleted records? 

Omnata is a native app product company connecting Snowflake to the enterprise with a strict goal of ‘no middleware’. Given the constraint, we found question one was far more challenging for databases than for SaaS applications. With a myriad of deployment types and security postures, how could we connect to databases but avoid the burden of handling customer data and credentials? 

We wanted to create a product experience that felt as simple and error-proof as our direct connections between Snowflake and SaaS apps, but with databases, we were operating in highly secure, fire-walled environments. We needed to develop a novel solution—especially if we were going to help you without asking you to stand up new infrastructure.

Let’s talk about how we use ngrok in the Omnata Sync Native App, to simplify your integration architecture on Snowflake, with all the security you expect.

Detailing the problem

As you well understand, databases almost always operate behind robust firewalls. In our business, we don’t have the benefit of operating through established, static, publicly accessible endpoints, like a cloud service and its API.

Still, many prospects and customers came to us with a particular scenario: They had migrated to using Snowflake as their central data warehouse but still had to ingest data from databases, like MSSQL Server. These all operated behind firewalls, whether in a cloud VPC or on-premise. To help them move quickly, our solution needed to work without requiring major infrastructure reconfiguration or exceptions in security policy, which would take months—if they were approved at all.

Unfortunately, there is no way for Snowflake to “reach through” the firewall to fetch new data. The same goes for the other way—you can’t get a dedicated IP from Snowflake to push data toward, and even if you could, there are no ways to open specific ports for ingress. Most of our customers didn’t want to try port forwarding either, as they felt it left them too vulnerable to threat actors and malicious attacks.

The answer to that technical problem of point A -> point B started to clarify: We needed a fully managed, reliable, secure, and well-known tunnelling service to build upon.

Tunnelling our way to a solution

After researching the available tunnelling solutions, we designed a new architecture connecting from the Omnata native app in Snowflake to customers’ databases using the following fundamentals:

1. During your onboarding, we create an ngrok endpoint, plus mTLS certificates and authentication tokens, on your behalf using the ngrok API.

2. We walk you through installing, configuring, and running the ngrok agent as a service on your database server.

3. We establish a secure TLS tunnel between Snowflake and your database server, with enforced mTLS, which uses a client certificate stored exclusively in Snowflake.

4. Inside of this ngrok tunnel, we create another TLS tunnel from Omnata’s Sync Engine directly to your SQL Server using its TLS certificate, offering you a second layer of encryption and ensuring the ngrok service has no visibility of the payload. 

Ensuring data security

We believe ngrok to be a reliable and secure solution for secure tunneling, but we’re also operating in a theater of data security where zero-trust is most often the default. 

With our tunnel-in-a-tunnel architecture, you can use ngrok in a trustless fashion. While your data passes through their network, they can never read or analyze it, as it’s been encrypted using your database’s TLS certificate. In fact, the trustless nature of Omnata extends to our product as well—no one has access to your data or certificates but your authorized users inside your Snowflake instance.

Seamless product experience

ngrok enabled us to create a click-through product experience, even while establishing a rather complex networking setup on your behalf, thanks to its flexible API.

We leverage its API heavily to create endpoints, ensure you've correctly configured ngrok agents to accept traffic, and establish secure tunnels without you having to build new infrastructure or learn complex networking primitives. All you have to do is install the lightweight ngrok agent near each database server, and we take care of the technical nuance on your behalf.

The whole connection flow takes minutes and saves our users weeks and months of negotiations with NetOps or SecOps teams to open ports in the firewall, if they would even consider it at all.

Greater sync flexibility

Omnata runs inside your Snowflake account and pulls data from source databases, as opposed to many legacy ETL tools that utilize a push agent installed behind a customer firewall. While also being able to handle scale to hundreds of millions of records, the architecture enables Omnata to be more flexible in its sync strategies, supporting CDC, high-watermark and full-refresh. Omnata can satisfy a broader set of integration use cases whether it’s replicating on a schedule, migrating from legacy to Snowflake, or maintaining a constant connection to alternate data stores. 

Try Omnata Sync for database replication to Snowflake

Thanks to ngrok, we’ve handily solved the second technical problem around securely tunneling data between two remote systems while maintaining, if not improving, your security posture.

You can install Omnata Sync and a free trial of our SQL Server connector today from the Snowflake Marketplace. We also have SaaS connectors available today, and more database connectors like Postgres and MySQL on our roadmap.