Data Platform (Bring Your Own BI and Data Share) Connection Options
Background
OneAdvanced Bring Your Own BI and Data Share are data services built on the OneAdvanced Data Platform. Both services enable you to securely access and consume data from OneAdvanced cloud-based products.
The Data Platform is built on modern cloud-native technologies, leveraging Snowflake for data storage, processing and security. Snowflake incorporates APIs and data-sharing capabilities to ensure seamless access for customers and integrations with BI tools.
More details can be found here - https://myworkplace.helpdocs.io/article/hsfhs2yeh6-data-platform-faqs
The purpose of this guide is to help you understand which of the available Snowflake connection methods is appropriate for your data access requirements, balancing security and convenience.
Note 1: This guide refers to ‘users’. In the context of Bring Your Own BI and Data Share, users have access to all data in the dataset, no user-specific row-level permissions are applied to users. It is assumed that users granted access are data analysts, data engineers etc who would normally be given access to all data.
Note 2: OneAdvanced does not provide direct access to Snowflake servers and virtual warehouses using www.snowflake.com, access is only provided using the methods described in this guide. OneAdvanced only provides access to Snowflake servers and virtual warehouses using ‘Reader Accounts’; no admin privileges are granted outside of OneAdvanced.
Understanding Snowflake Authentication Methods
The authentication methods OneAdvanced recommends are SAML and OAuth. These define how the applications/tools you are planning to use gain secure access to your data. We do not recommend key-pair authentication.
SAML (Security Assertion Markup Language): SAML is primarily for applications that support Single Sign-On (SSO). SAML uses a central identity provider (IdP) which holds details of all your users. We recommend OneAdvanced Identity as the IdP for Data Platform. When a user attempts to connect to Snowflake, if they have not done so already, they are required to log in to the central IdP. This step assures they are a valid user. Snowflake will then allow the user access to their data. An API authorisation client is required from the IdP, which will be created as part of the on-boarding process.
OAuth (Open Authorization): Think of OAuth as a secure "permission slip” allows an application to access your Snowflake data on your behalf without ever needing your actual Snowflake credentials. OneAdvanced recommends this authorisation method in use-cases involving machine-to-machine connections, such as MS SQL Server linked servers, only as it requires the sharing of secrets in other use-cases. An API authorisation client is required from the IdP, which will be created as part of the on-boarding process.
The Role of the Snowflake ODBC Connector
The Snowflake ODBC (Open Database Connectivity) connector acts as a universal translator. Many applications, especially those that don't provide a native Snowflake integration, use ODBC to communicate with databases.
When it's Required: You must have the Snowflake ODBC driver installed and configured on each PC or server for:
- Any direct connection from Microsoft Excel to Snowflake.
- Most custom desktop applications (e.g., built with Python, Java, .NET) that need to connect to Snowflake.
- Many third-party BI or ETL tools that don't have a specific Snowflake API integration.
- Linked SQL Servers or other database systems that need to query Snowflake data.
How it Works with Authentication: The ODBC driver itself supports both OAuth and SAML. The driver handles the secure handshake with Snowflake, leveraging either OAuth or SAML setup.
More details can be found here - https://docs.snowflake.com/en/developer-guide/odbc/odbc
Once installed, it will be the customer’s responsibility to ensure that the latest version is installed on all devices. If any errors are encountered when connecting to Snowflake, before contacting OneAdvanced support, ensure the latest version is being used.
Connecting Your Tools to Snowflake
This section covers the currently supported methods of connecting tools to Snowflake.
Power BI
Power BI provides a native data source connector to Snowflake. OneAdvanced currently only supports this connector in conjunction with OAuth and Azure AD.
Most Secure & Convenient (Recommended): OAuth with Azure AD
Use Case: If your organization uses Azure Active Directory (Azure AD) as its identity provider, Power BI Desktop and Service can directly leverage Azure AD OAuth for a seamless, single sign-on experience. You're likely already logged into Azure AD, and Power BI uses that existing session to connect securely to Snowflake.
How it works: In Power BI, select "Snowflake" as the data source, choose "Microsoft account", and Power BI will handle the authentication flow through your Azure AD login.
What Setup is Required: You will need to provide OneAdvanced with your Azure AD Entra ID. This will be securely associated with your OneAdvanced Identity Customer and Organisation references in Snowflake. Access is then granted to Snowflake data for all nominated users (currently a task performed by OneAdvanced).
Excel
Excel relies on Power Query (Get & Transform Data) for Snowflake connectivity, which in turn uses the ODBC connector.
Most Secure & Convenient (Recommended): ODBC with SAML
Use Case: The simplest, and most secure, approach is to implement ODBC with SAML utilising OneAdvanced’s Identity SAML API Client feature.
How it works: First, configure your Snowflake ODBC DSN on your machine to use SAML. Then, in Excel's Power Query, select "From ODBC" and choose that configured DSN. Power Query will initiate the respective SAML SSO flow, often opening a browser for authentication through OneAdvanced Identity. This avoids storing credentials directly in Excel and ODBC.
What setup is required:
- The Snowflake ODBC connector (https://docs.snowflake.com/en/developer-guide/odbc/odbc) must be installed and configured on each Data Share/BYOBI user’s PC. This can be achieved by a Group Policy.
- Access granted to Snowflake data for all nominated users (currently a task performed by OneAdvanced)
Other BI Tools & Linked SQL Servers
Many other Business Intelligence (BI) tools (e.g., Tableau, Qlik Sense) and database systems (like SQL Server for linked servers) will typically connect to Snowflake via the ODBC connector.
Most Secure & Convenient (Recommended): ODBC with SAML
Use Case: For any tool that supports ODBC with SAML (refer to the relevant documentation provided with your tool).
How it works: The tool connects to the pre-configured ODBC DSN. The DSN, in turn, handles the SAML authentication flow with Snowflake, ensuring secure access without embedding credentials directly in the tool's connection string where possible.
What Setup is Required:
- The Snowflake ODBC connector (https://docs.snowflake.com/en/developer-guide/odbc/odbc) must be installed on each Data Share/BYOBI user’s PC.
- Access granted to Snowflake data for all nominated users (currently a task performed by OneAdvanced)
ODBC with OAuth
Use Case: For any tool that doesn’t support ODBC with SAML, or when configuring a connection to Snowflake from a dedicated server (e.g. MSSQL Server Linked Servers).
How it works: The tool connects to the pre-configured ODBC DSN. The DSN, in turn, handles the OAuth authentication flow with Snowflake. OAuth will be configured to use ‘Client Credentials’ requiring non-user specific credentials to be maintained within the ODBC DSN. For this reason, this method should only be used within a secure environment. It is the customer's responsibility to secure any authorisation secrets.
What setup is required:
- You will be required to create a client credentials API Client using the OneAdvanced Identity Customer admin app (further details will be provided during the on-boarding phase) https://myworkplace.helpdocs.io/article/5p1xb6vo5v-api-clients
- The Snowflake ODBC connector (https://docs.snowflake.com/en/developer-guide/odbc/odbc) must be installed on the server from where the connection to Snowflake is made.
Disclaimer
These examples are not exhaustive. If none are applicable, please refer to the documentation associated with the tool you plan to connect to Snowflake.
OneAdvanced is also aware of third-party Snowflake ODBC drivers. Only the official Snowflake supplied driver is supported by OneAdvanced (https://docs.snowflake.com/en/developer-guide/odbc/odbc).
