The mandate was clear: provide our mobile sales team with near real-time analytics directly from our core Snowflake data warehouse on their React Native application. The problem was the equally clear mandate from the security team: the Snowflake instance must remain within a private AWS VPC, accessible only via PrivateLink, with absolutely no direct public internet exposure. On top of that, every action must adhere to Zero Trust principles, meaning the mobile client is inherently untrusted, and every single query must be individually authenticated and authorized by a backend system we control.
This immediately ruled out embedding Snowflake credentials or using its client-side SDKs in the React Native app. The solution required a secure intermediary, a trusted component sitting within our infrastructure that could bridge the untrusted client world with the protected data world. Our initial whiteboard session leaned towards a serverless approach with AWS Lambda, but the performance overhead of cold starts and the complexities of managing the Snowflake .NET driver’s native dependencies in a Lambda package felt like a significant operational burden for the low-latency experience we needed.
We settled on a more controlled alternative: a dedicated, containerized C# ASP.NET Core service. This approach gave us fine-grained control over the runtime, dependency management, and crucially, the ability to implement robust connection management and caching strategies. It would act as a secure data forwarder, an authenticated and authorized proxy responsible for translating secure requests from the mobile app into precise, parameterized queries against Snowflake within the protected VPC.
Network and Security Foundation
Before a single line of C# was written, the infrastructure had to be established. The core of this architecture is the network segmentation.
graph TD
subgraph User Device
A[React Native App]
end
subgraph Public Internet
B[API Request w/ JWT]
end
subgraph AWS Cloud
subgraph Public Subnet
C[Application Load Balancer]
end
subgraph Private Subnet
D[C# ASP.NET Core Service]
end
subgraph AWS Managed
E[PrivateLink Endpoint]
F[Snowflake Service]
end
end
A -- HTTPS --> B
B --> C
C -- Forwards Traffic --> D
D -- Snowflake Protocol over PrivateLink --> E
E -- Securely Connects --> F
The C# service resides in a private subnet. The only way it can be reached from the outside is through an Application Load Balancer (ALB) in a public subnet. The critical piece is the communication to Snowflake. We configured AWS PrivateLink for our Snowflake account, which creates an Elastic Network Interface (ENI) within another private subnet in our VPC. Our C# service communicates with Snowflake exclusively through this private endpoint. There is no route to the public internet from the service’s subnet for Snowflake traffic, effectively air-gapping the data layer from a network perspective.
The second pillar of the foundation is authentication. Snowflake’s Key Pair Authentication was the only viable choice. It allows our C# service to authenticate as a specific Snowflake user without ever storing a password. We generated a 2048-bit RSA key pair.
# Generate private key (encrypted with a passphrase)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes256 -out rsa_key.p8
# Generate the public key from the private key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pubThe public key’s content (excluding the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- headers) was assigned to a dedicated service user in Snowflake.
-- Create a dedicated role and user for the forwarder service
CREATE ROLE FORWARDER_SERVICE_ROLE;
CREATE USER FORWARDER_SERVICE_USER
PASSWORD = NULL -- Cannot be used with password auth
LOGIN_NAME = 'forwarder_svc'
DISPLAY_NAME = 'C# Data Forwarder Service'
DEFAULT_ROLE = FORWARDER_SERVICE_ROLE
DEFAULT_WAREHOUSE = 'COMPUTE_WH';
-- Assign the public key to the user
ALTER USER FORWARDER_SERVICE_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkq...'; -- Paste public key content here
-- Grant necessary permissions to the role
GRANT ROLE FORWARDER_SERVICE_ROLE TO USER FORWARDER_SERVICE_USER;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE FORWARDER_SERVICE_ROLE;
GRANT USAGE ON DATABASE SENSITIVE_DATA_DB TO ROLE FORWARDER_SERVICE_ROLE;
GRANT USAGE ON SCHEMA SENSITIVE_DATA_DB.ANALYTICS TO ROLE FORWARDER_SERVICE_ROLE;
GRANT SELECT ON TABLE SENSITIVE_DATA_DB.ANALYTICS.SALES_PERFORMANCE TO ROLE FORWARDER_SERVICE_ROLE;The encrypted private key (rsa_key.p8) and its passphrase are treated as high-value secrets, stored in AWS Secrets Manager, and injected into the C# service’s runtime environment at startup. They never touch source control.
The C# Secure Data Forwarder Implementation
The ASP.NET Core service is the heart of this solution. Its responsibilities are threefold:
- Authenticate the incoming request from the React Native app via a JWT Bearer token.
- Authorize the request based on a predefined catalog of allowed queries. Raw SQL from the client is strictly forbidden.
- Execute the corresponding parameterized query against Snowflake and return the results.
Here’s the project structure breakdown:
/SecureDataForwarder
|-- appsettings.json
|-- Program.cs
|-- /Controllers
| |-- QueryController.cs
|-- /Models
| |-- QueryRequest.cs
| |-- ApiError.cs
|-- /Services
| |-- ISnowflakeQueryService.cs
| |-- SnowflakeQueryService.cs
|-- /Security
| |-- AllowedQueriesRepository.cs
| |-- QueryDefinition.cs
|-- /Tests
| |-- ... unit and integration testsConfiguration and Startup
Configuration is loaded from appsettings.json and environment variables. Secrets, like the private key passphrase, are loaded from AWS Secrets Manager in a production environment.
appsettings.Development.json:
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"Jwt": {
"Issuer": "https://your-auth-provider.com/",
"Audience": "data-forwarder-api"
},
"Snowflake": {
"Account": "your_account.aws_region", // Use the private link URL here, e.g., your_account.privatelink
"User": "FORWARDER_SERVICE_USER",
"PrivateKeyPath": "C:\\path\\to\\your\\rsa_key.p8",
"PrivateKeyPassphrase": "YOUR_SECRET_PASSPHRASE" // Injected from secrets manager in prod
}
}Program.cs:
This wires up all the services, including JWT authentication, dependency injection for our custom services, and logging.
using System.Text;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.IdentityModel.Tokens;
using SecureDataForwarder.Services;
using SecureDataForwarder.Security;
var builder = WebApplication.CreateBuilder(args);
// 1. Configure Logging
builder.Logging.ClearProviders();
builder.Logging.AddConsole();
// 2. Configure Services
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(); // Useful for development
// Bind Snowflake configuration
builder.Services.Configure<SnowflakeConnectionOptions>(builder.Configuration.GetSection("Snowflake"));
// Register our custom services
builder.Services.AddSingleton<AllowedQueriesRepository>();
builder.Services.AddScoped<ISnowflakeQueryService, SnowflakeQueryService>();
// 3. Configure JWT Authentication
var jwtConfig = builder.Configuration.GetSection("Jwt");
builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.Authority = jwtConfig["Issuer"];
options.Audience = jwtConfig["Audience"];
options.TokenValidationParameters = new TokenValidationParameters
{
ValidateIssuer = true,
ValidateAudience = true,
ValidateLifetime = true,
ValidateIssuerSigningKey = true,
// In a real scenario, the signing key is fetched from the issuer's .well-known endpoint.
// For brevity, we are assuming symmetric key, but asymmetric is production standard.
};
});
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
// Add Authentication and Authorization middleware
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();
app.Run();
// Add a public record for integration testing
public partial class Program { }
public class SnowflakeConnectionOptions
{
public string Account { get; set; }
public string User { get; set; }
public string PrivateKeyPath { get; set; }
public string PrivateKeyPassphrase { get; set; }
}The Query Abstraction Layer
To prevent SQL injection, we don’t accept raw SQL from the client. Instead, the client sends a QueryId and a dictionary of parameters. The AllowedQueriesRepository service maps this ID to a predefined SQL statement. This acts as an allow-list, ensuring only vetted queries can run.
Security/QueryDefinition.cs:
namespace SecureDataForwarder.Security;
public class QueryDefinition
{
public string Sql { get; }
public HashSet<string> AllowedParameters { get; }
public QueryDefinition(string sql, IEnumerable<string> allowedParameters)
{
Sql = sql;
AllowedParameters = new HashSet<string>(allowedParameters, StringComparer.OrdinalIgnoreCase);
}
}Security/AllowedQueriesRepository.cs:
using System.Collections.Concurrent;
namespace SecureDataForwarder.Security;
// In a real application, this might be loaded from a database or a config file.
// For this example, we'll hardcode it for simplicity and security.
public class AllowedQueriesRepository
{
private readonly ConcurrentDictionary<string, QueryDefinition> _queries;
public AllowedQueriesRepository()
{
_queries = new ConcurrentDictionary<string, QueryDefinition>(StringComparer.OrdinalIgnoreCase);
// Define the queries that the API is allowed to execute.
_queries.TryAdd("GetSalesByRegion", new QueryDefinition(
@"SELECT REGION, SUM(SALE_AMOUNT) AS TOTAL_SALES
FROM SENSITIVE_DATA_DB.ANALYTICS.SALES_PERFORMANCE
WHERE SALE_DATE >= TO_DATE(:startDate, 'YYYY-MM-DD')
AND SALE_DATE <= TO_DATE(:endDate, 'YYYY-MM-DD')
GROUP BY REGION
ORDER BY TOTAL_SALES DESC;",
new[] { "startDate", "endDate" }
));
_queries.TryAdd("GetPerformanceForAgent", new QueryDefinition(
// This query demonstrates adding a parameter based on the authenticated user's identity.
@"SELECT PRODUCT_ID, COUNT(*) AS UNITS_SOLD, SUM(SALE_AMOUNT) AS REVENUE
FROM SENSITIVE_DATA_DB.ANALYTICS.SALES_PERFORMANCE
WHERE AGENT_ID = :agentId
GROUP BY PRODUCT_ID
ORDER BY REVENUE DESC;",
new[] { "agentId" } // Note: This parameter will be enforced to match the JWT claim.
));
}
public QueryDefinition? GetQueryById(string queryId)
{
_queries.TryGetValue(queryId, out var query);
return query;
}
}The Core Snowflake Service Logic
The SnowflakeQueryService is where the connection is made and the query is executed. It’s constructed with a strong focus on security and resource management.
Services/ISnowflakeQueryService.cs:
using System.Security.Claims;
using SecureDataForwarder.Models;
namespace SecureDataForwarder.Services;
public interface ISnowflakeQueryService
{
Task<object> ExecuteQueryAsync(QueryRequest request, ClaimsPrincipal user);
}Services/SnowflakeQueryService.cs:
This is the most critical piece of code. It handles key loading, parameter validation, and secure execution.
using System.Data;
using System.Security.Claims;
using Dapper; // Using Dapper for convenience to map results to dynamic objects
using Microsoft.Extensions.Options;
using SecureDataForwarder.Models;
using SecureDataForwarder.Security;
using Snowflake.Data.Client;
namespace SecureDataForwarder.Services;
public class SnowflakeQueryService : ISnowflakeQueryService
{
private readonly ILogger<SnowflakeQueryService> _logger;
private readonly SnowflakeConnectionOptions _options;
private readonly AllowedQueriesRepository _allowedQueries;
public SnowflakeQueryService(
ILogger<SnowflakeQueryService> logger,
IOptions<SnowflakeConnectionOptions> options,
AllowedQueriesRepository allowedQueries)
{
_logger = logger;
_options = options.Value;
_allowedQueries = allowedQueries;
}
public async Task<object> ExecuteQueryAsync(QueryRequest request, ClaimsPrincipal user)
{
// 1. Authorization: Find the query definition from our allow-list.
var queryDefinition = _allowedQueries.GetQueryById(request.QueryId);
if (queryDefinition == null)
{
_logger.LogWarning("Attempted to execute non-existent query ID: {QueryId}", request.QueryId);
throw new UnauthorizedAccessException($"Query ID '{request.QueryId}' is not allowed.");
}
// 2. Parameter Validation
ValidateParameters(request, queryDefinition);
// 3. Inject user-context parameters and enforce security
var finalParameters = new DynamicParameters();
foreach (var param in request.Parameters)
{
finalParameters.Add(param.Key, param.Value);
}
// Example of enforcing a security constraint:
// If query needs agentId, we take it from the JWT claim, not the client request.
if (queryDefinition.AllowedParameters.Contains("agentId"))
{
var agentIdFromToken = user.FindFirstValue("agent_id_claim"); // The name of your claim
if (string.IsNullOrEmpty(agentIdFromToken))
{
throw new UnauthorizedAccessException("User token is missing required 'agent_id_claim'.");
}
// Overwrite or add the agentId to ensure the user can only query their own data.
finalParameters.Add("agentId", agentIdFromToken);
}
// 4. Create connection and execute
try
{
using var connection = CreateSnowflakeConnection();
await connection.OpenAsync();
_logger.LogInformation("Successfully connected to Snowflake for user {User}", _options.User);
var result = await connection.QueryAsync(queryDefinition.Sql, finalParameters);
return result;
}
catch (SnowflakeDbException ex)
{
_logger.LogError(ex, "Snowflake database error while executing query ID {QueryId}. SQLState: {SQLState}", request.QueryId, ex.SqlState);
// Avoid leaking detailed database errors to the client.
throw new InvalidOperationException("An error occurred while processing the data request.");
}
catch (Exception ex)
{
_logger.LogError(ex, "An unexpected error occurred in SnowflakeQueryService for query ID {QueryId}", request.QueryId);
throw; // Rethrow to be handled by global error handler.
}
}
private void ValidateParameters(QueryRequest request, QueryDefinition queryDefinition)
{
foreach (var paramName in request.Parameters.Keys)
{
if (!queryDefinition.AllowedParameters.Contains(paramName))
{
_logger.LogWarning("Request for query {QueryId} contained a disallowed parameter: {ParameterName}", request.QueryId, paramName);
throw new ArgumentException($"Parameter '{paramName}' is not allowed for query ID '{request.QueryId}'.");
}
}
}
private IDbConnection CreateSnowflakeConnection()
{
// The private key itself is read from the path provided in the configuration.
// This is a sensitive operation and should have tight file permissions.
var connectionString = new StringBuilder();
connectionString.Append($"account={_options.Account};");
connectionString.Append($"user={_options.User};");
connectionString.Append($"private_key_file={_options.PrivateKeyPath};");
connectionString.Append($"private_key_pwd={_options.PrivateKeyPassphrase};");
return new SnowflakeDbConnection(connectionString.ToString());
}
}The API Controller
The controller is thin, serving as the entry point. It handles deserialization of the request, calls the service layer, and manages the HTTP response.
Models/QueryRequest.cs:
using System.ComponentModel.DataAnnotations;
namespace SecureDataForwarder.Models;
public class QueryRequest
{
[Required]
public string QueryId { get; set; }
public Dictionary<string, object> Parameters { get; set; } = new();
}Controllers/QueryController.cs:
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SecureDataForwarder.Models;
using SecureDataForwarder.Services;
namespace SecureDataForwarder.Controllers;
[ApiController]
[Route("api/[controller]")]
[Authorize] // Requires a valid JWT for all actions in this controller
public class QueryController : ControllerBase
{
private readonly ILogger<QueryController> _logger;
private readonly ISnowflakeQueryService _queryService;
public QueryController(ILogger<QueryController> logger, ISnowflakeQueryService queryService)
{
_logger = logger;
_queryService = queryService;
}
[HttpPost]
public async Task<IActionResult> Post([FromBody] QueryRequest request)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
try
{
// The User object (ClaimsPrincipal) is populated by the authentication middleware
var result = await _queryService.ExecuteQueryAsync(request, User);
return Ok(result);
}
catch (UnauthorizedAccessException ex)
{
_logger.LogWarning(ex, "Authorization failed for query {QueryId}", request.QueryId);
return Forbid();
}
catch (ArgumentException ex)
{
_logger.LogWarning(ex, "Invalid arguments for query {QueryId}", request.QueryId);
return BadRequest(new ApiError { Message = ex.Message });
}
catch (Exception ex)
{
_logger.LogError(ex, "An unhandled exception occurred while processing query {QueryId}", request.QueryId);
return StatusCode(500, new ApiError { Message = "An internal server error occurred." });
}
}
}React Native Client Snippet
The client-side implementation is straightforward. Assuming a user has already authenticated and a valid JWT is stored securely, the app simply needs to call our new endpoint.
import axios from 'axios';
const API_BASE_URL = 'https://api.your-company.com';
// Assume getAuthToken() securely retrieves the stored JWT
const getAuthToken = async (): Promise<string | null> => {
// Implementation depends on your secure storage solution
// e.g., react-native-keychain
return '... a valid JWT ...';
};
interface QueryParams {
[key: string]: string | number | boolean;
}
export const executeSecureQuery = async (queryId: string, parameters: QueryParams) => {
const token = await getAuthToken();
if (!token) {
throw new Error('Authentication token not found.');
}
try {
const response = await axios.post(
`${API_BASE_URL}/api/query`,
{
queryId,
parameters,
},
{
headers: {
'Content-Type': 'application/json',
Authorization: `Bearer ${token}`,
},
},
);
return response.data;
} catch (error) {
if (axios.isAxiosError(error)) {
console.error('API Error:', error.response?.status, error.response?.data);
if (error.response?.status === 401 || error.response?.status === 403) {
// Handle token expiration or unauthorized access, e.g., force re-login
}
} else {
console.error('An unexpected error occurred:', error);
}
// Re-throw or handle as appropriate for your app's UX
throw error;
}
};
// Example usage in a React component
const fetchSalesData = async () => {
const data = await executeSecureQuery('GetSalesByRegion', {
startDate: '2023-01-01',
endDate: '2023-03-31',
});
// Process and display data
};Limitations and Future Trajectory
This architecture, while secure and effective, introduces the C# service as a stateful bottleneck. The current implementation opens a new connection to Snowflake for every request, which is not scalable. The immediate next step would be to implement a robust connection pooling mechanism within the SnowflakeQueryService to reuse connections and reduce latency. For applications with very high read throughput, a caching layer (like Redis) could be introduced in the C# service to cache results of common queries for a short TTL, further reducing load on the Snowflake warehouse.
The query allow-list, while secure, is static and requires a code deployment to update. A more flexible system might load these QueryDefinition objects from a secure, version-controlled source like a dedicated database table or a configuration service. This would allow data analysts to add new, safe query patterns without involving the backend engineering team for every change, though it would require a rigorous review process for any new SQL added. The service itself is a single point of failure; a production deployment would require running multiple container instances behind the load balancer for high availability.
