ARA-C01 Question 315
Single answerJDBCA company is migrating a Java-based reporting application from an on-premises database to Snowflake. The application uses the Snowflake JDBC driver with a connection pool and executes many short-lived queries from interactive users. During testing, architects observe that users frequently reauthenticate, some requests fail after idle periods, and query latency increases when the pool opens new physical connections. The security team also requires federated SSO through the corporate IdP rather than embedded user passwords. Which approach best addresses both the performance and authentication requirements for this JDBC-based application?
- A
Configure the JDBC connection string to use the externalbrowser authenticator and rely on users to complete interactive login for each pooled connection as needed.
- B
Use key-pair authentication in the JDBC application for all end users, and disable connection pooling so each request creates a fresh authenticated session.
- C
Use an OAuth-based or native Okta-supported federated authentication approach appropriate for the JDBC driver, and configure the application to reuse pooled JDBC connections to reduce connection establishment overhead.
- D
Store Snowflake username and password credentials in the application server, increase the login timeout, and let the JDBC driver automatically convert those credentials into SSO tokens for pooled sessions.
Show answer and explanation
Correct answer: C
Explanation
The best answer is Option 3 because the scenario has two separate architectural issues: authentication method and connection efficiency. For authentication, the requirement is federated SSO through the corporate IdP, which points to supported JDBC-compatible federation patterns such as OAuth or native Okta integration, depending on the organization's identity architecture. For performance, the application uses many short-lived queries, so repeatedly creating physical JDBC connections increases latency; a properly managed connection pool mitigates that overhead by reusing existing connections and sessions where appropriate.
Option 1 is a poor fit because external browser authentication is designed for user-interactive flows and is typically not ideal for unattended or pooled server-side application connectivity. Option 2 misapplies key-pair authentication and makes performance worse by removing pooling. Option 4 violates the SSO requirement and assumes behavior the JDBC driver does not provide.
Relevant Snowflake guidance includes JDBC driver authentication options, support for federated authentication patterns such as OAuth and native Okta, and general best practices for reducing connection overhead in application architectures that issue frequent short-lived requests.
- A. Incorrect.
Incorrect. The externalbrowser authenticator is intended for interactive authentication flows and is generally unsuitable for server-side pooled application connections handling many short-lived requests. It can create repeated user prompts and operational friction, especially when the pool must establish or refresh physical connections after idle periods. This does not efficiently address latency from new connections in a pooled service architecture.
- B. Incorrect.
Incorrect. Key-pair authentication is commonly used for service accounts, not as a scalable substitute for federated end-user SSO in an interactive reporting application. Disabling connection pooling would worsen the observed performance problem because opening new physical JDBC connections and authenticating repeatedly adds overhead. This option addresses neither the latency issue nor the stated enterprise SSO requirement appropriately.
- C. Correct.
Correct. For enterprise applications using the Snowflake JDBC driver, a federated authentication model such as OAuth or supported native Okta integration is appropriate when the security requirement is SSO through the corporate identity provider. At the same time, reusing pooled JDBC connections helps reduce the cost of repeatedly establishing new connections and sessions, which is important for many short-lived queries. This aligns with Snowflake best practices to minimize unnecessary connection creation while using supported non-password authentication mechanisms for applications.
- D. Incorrect.
Incorrect. Simply storing usernames and passwords in the application server conflicts with the stated requirement to use federated SSO rather than embedded passwords. Increasing login timeout does not solve connection-establishment overhead, and the JDBC driver does not automatically transform basic username/password authentication into a federated SSO model for pooled sessions. This reflects a common misconception that password-based login can be treated as equivalent to IdP-backed SSO.