OCA Snowflake Connection Setup for Power BI with Key-Pair Authentication via Simba Gateway
To set up a Snowflake connection for Power BI Desktop using key-pair authentication via Simba Gateway, perform the following steps:
Prerequisites
Simba Gateway and Simba Universal ODBC Driver (32-bit or 64-bit, matching your PowerBI Desktop architecture) installed on each computer that generates Power BI reports.
Snowflake account configured for key-pair authentication (.p8 private key file and passphrase).
Access to the Angles application server (for example, https://localhost:44394/api/viewapi).
-
Select the setup that matches your environment:
Setup A: Angles Hub and the Simba Gateway run on the same machine.
Setup B: Angles Hub runs on a server, and the Simba Gateway runs on user computers.
Note: The steps in this guide apply to both Setup A and Setup B. Only the section Put the Key File on Each User Computer (Setup B Only) is additional for Setup B. Customers using Setup A can skip that section.
How it works
Angles Hub stores the private key you upload. When a report connects, the Simba connector requests the connection details from Angles Hub and opens the key file on the computer where it runs. If Angles Hub runs on a different machine, the same key file must also be present on that computer, in a folder you select.
Create a Snowflake Connection in Angles Hub
To create a Snowflake connection, sign in to the Angles Hub and perform the following:
In Angles Hub, navigate to the connection configuration and create a new Snowflake connection.
-
Enter the following parameters:
Username: Indicates a meaningful name for this connection that will be displayed in the application. For example, Snowflake_Demo_Connection. Record this name exactly, because it will also be used as the Simba Gateway profile name, and the Power BI report will refer to it.
Server: Indicates the Snowflake server hostname or fully qualified domain name (FQDN) for your Snowflake instance (for example, xy12345.us-east-1.snowflakecomputing.com).
Port: Indicates the port number for the Snowflake connection (typically 443 for HTTPS).
Account: Indicates your Snowflake account identifier (for example, xy12345).
Database: Indicates the name of the Snowflake database you want to connect to.
Warehouse: Indicates the name of the Snowflake warehouse that will process queries.
In the Private Key File field, enter the full path to the rsa_key.p8 file on your machine.
In the Private Key Passphrase field, enter the passphrase you set during key generation.
Save and test the connection in Angles Hub to confirm that it can reach Snowflake.
Put the key file on each user computer (applicable only for Setup B)
This section has two parts. The administrator steps are performed by insightsoftware, once for each Angles Hub server. The user steps are performed by each user on their own computer.
Administrator steps (performed by insightsoftware, once for each Angles Hub server):
-
Select a folder that exists on every user computer. By default, use C:\snowflakekeys.
By default, the key folder path is C:\snowflakekeys. If the customer wants to use a different folder path, they must contact the insightsoftware team.
The folder path refers to the user computers, not the server, and it applies to every user of this Angles Hub server. Complete these steps before users create their Simba Gateway profiles, because each profile stores the key path that Angles Hub returns when it is created.
-
In the Angles Hub settings file for the production environment (for example, settings.Production.json), provide the following and save the file:
SnowflakeSettings”: { “ClientPrivateKeyDirectory”: “C:\snowflakekeys” }
Restart Angles Hub server.
User steps (performed by each user who generates Power BI reports on their own computer, where the Simba Gateway runs):
Open the Snowflake connection in Angles Hub. The connection page shows the exact path for the key file, for example C:\snowflakekeys\rsa_key.p8.
-
Create the folder (C:\snowflakekeys) if it does not exist, and copy the same key file, with the same name, into it.
The Simba connector service runs as LocalSystem, which can read this folder by default.
Create the Simba Gateway Connection Profile
To create the Simba Gateway connection profile, perform the following:
Open the Simba Gateway application. The Simba-Gateway Guided Setup window is displayed.
-
Go to Connectors > Connector Catalog. Install the Snowflake connector if not already installed.
If the Snowflake connector is already installed you can skip this step.
Select Connection Profiles > Add Connection Profile. The Select a Connector window is displayed.
Select Snowflake. The Angles for Oracle Configuration page is displayed.
Enter the Angles Hub URL and select Connect. Perform the authentication. The Create Angles Connection Profile window is displayed.
Select the Snowflake connection that you have created in Step 1 from the dropdown and select Create. The connection profile is created.
Note: Do not paste a raw connection string containing private_key=... into the Properties tab, because the connection will fail. ... If you enable, change, or turn off ClientPrivateKeyDirectory, delete the profile and create it again by following the steps above while Angles Hub runs the new value.
Generate the Power BI Report in Angles Hub
To generate a Power BI report, sign in to the Angles Hub and perform the following steps:
Ensure to add user mappings before generating the reports.
Click Reports > Reports. The Reports tab is displayed.
In Connections, select the Snowflake connection setup in Step 1.
Click the Generate icon corresponding to the report you want to generate. The Select a BI Tool window is displayed.
In Personalization, select the required personalization settings from the list.
In BI Tool Type , select Power BI.
-
In Connectivity Mode, select the required data connectivity mode from the list.
Note: This option is not applicable if you select BI Tool Type as Tableau.
In Security Policy, select the required value from the list.
Click Download.
Open the .pbit Report in Power BI Desktop
To open the .pbit report in Power BI, perform the following:
Double-click the
.pbitfile to open it in Power BI Desktop.When prompted for
SimbaGateway.Contentscredentials, select Anonymous (not Basic). The DSN profile handles Snowflake authentication internally.If prompted for Privacy Level, select Organizational (or per your organization's policy). The report should load data successfully.
Register or Verify the ODBC (Optional)
Optionally, if you face any issues while genrating or opening the Power BI report, verify the ODBC data source.
To register or verify the ODBC data source, perform the following:
Go to ODBC Data Sources (32-bit or 64-bit, matching PowerBI Desktop) and run as administrator. The ODBC Data Source Administrator window is displayed.
-
Go to User DSN or System DSN, verify whether Simba Universal ODBC Driver is added.
If it is added, select Simba Gateway > Configure.
If it is not added, select Add, select Simba Universal ODBC Driver, and select Finish.
The Simba Universal ODBC Driver DSN Setup page is displayed.
-
In Data Source Name, enter a meaningful name that identifies this Snowflake connection.
If you created a Snowflake application in Step 1 with the username SNOWFLAKE_DEMO_CONNECTION, enter SNOWFLAKE_DEMO_CONNECTION as the data source name in this field for easy identification.
In Connection Profile, select SNOWFLAKE_DEMO_CONNECTION.
Ensure that Gateway Management Server is configured as <local>.
Leave the Properties tab empty. Angles Hub supplies the key path and passphrase automatically. A fixed path in this tab would direct every connection to the same key file
- Select Test Connection to verify that the connection from ODBC through Simba Gateway to Snowflake is established.