Hello,
Is it possible to connect Foundry to Snowflake using Azure AD as the authentication service, i.e. obtain a token from Azure AD, and use this token to connect to Snowflake?
Thanks,
Dimitri
Hi @dlandes
In theory yes, but I have only seen documentation on integration with AWS IdPs.
Alternatively,you can write your own OAuth flow in Foundry. You need an external transform and a Third-party application (TPA) with client credential grant:
- Call Azure AAD with client id/secret and get a token
- Call multipass and get a Foundry Token
- Inject the secret in the Snowflake data connection
import logging
import time
from transforms.api import transform_df, Output, TransformContext, configure
from transforms.external.systems import external_systems, Source
from pyspark.sql import DataFrame, types as T
logger = logging.getLogger(__name__)
@configure(profile=["KUBERNETES_NO_EXECUTORS"])
@external_systems(
azure_login_api=Source("RID_REST_API_DATA_CONNECTION"),
foundry=Source("RID_REST_API_DATA_CONNECTION")
)
@transform_df(
Output("RID_LOGS")
)
def compute(
ctx: TransformContext, azure_login_api: Source, foundry: Source
) -> DataFrame:
ms_connection = azure_login_api.get_https_connection()
ms_client = ms_connection.get_client()
token_azure= (
ms_client
.post(
ms_connection.url + "YOUR_ENDPOINT",
headers={
"Content-Type": "application/x-www-form-urlencoded"
},
data={
"client_id": "...",
"client_secret": "..."
"grant_type": 'client_credentials',
"scope": "..."
}
)
.json()
.get("access_token")
)
f_connection = foundry.get_https_connection()
f_client = f_connection.get_client()
foundry_token = (
f_client
.post(
sg_connection.url + "/multipass/api/oauth2/token",
headers={
"Content-Type": "application/x-www-form-urlencoded"
},
data={
"grant_type": "client_credentials",
"client_id": "TPA_ID",
"client_secret": "TPA_SECRET"
}
)
.json()
.get("access_token")
)
response = (
f_client
.post(
f"https://{YOUR_DEPLOYMENT_URL}/api/v2/connectivity/connections/{SNOW_FLAKE_RID}/updateSecrets?preview=true",
headers={
"Content-type": "application/json",
"Authorization": f"Bearer {foundry_token}"
},
json={
"secrets": {"PASSWORD": token_azure}
}
)
)
....
Then you run the ingestion from snowflake when the secrets are uploaded in the Snowflake data connection. Hope it helps.
Here’s some relevant links:
https://docs.snowflake.com/en/user-guide/oauth-ext-custom
https://www.palantir.com/docs/foundry/api/connectivity-v2-resources/connections/update-secrets-for-connection
@dlandes can you explain a little bit more what’s your end goal in terms of workflow?
Are you trying to make queries that impersonate the logged in Foundry user in Snowflake?
Thanks @HugoRodrigues @nicornk.
In short, our customer would like to set up a direct integration between Foundry and Snowflake using Virtual Tables, and would like to use their AAD instance to manage access, roles and permissions for our user in Snowflake, in line with their existing processes.
My understanding is that the OAuth option that comes out of the box offers to basically use Foundry as the authentication service and does not provide the option to use an existing third party authentication service such as AAD – is my understanding correct?
The idea of writing our own OAuth flow in Foundry is probably our best bet here – thank you for the code sample. I will try to obtain more details from them about which AAD endpoint we would use and what their policy is in terms of token expiration, which will inform how frequently we should run the external transform. Let me know if you’d like any additional clarification.
@HugoRodrigues your code sample is using “PASSWORD” as the secret name for the Snowflake token but I get a SecretNamesDoNotExist error. Do you know what name the token exists under?
This is a known issue, and I believe the Product Development team already has a ticket to address it. The code I shared pertains to integrating PostgresDB with Azure Entra ID (AAD). However, in the case of MongoDB, the username is in lowercase. You may want to check with your colleagues the source code, as I encountered the same issue. Only solved it by raising a platform issue.
@HugoRodrigues How is configured your Postgres source ? Because I don’t see any OAuth auth option in the config of the source ? Are you using a basic auth config and then in your script updating the password ?
The native driver from the Palantir Postgres connection doesn’t support OAuth with EntraID out of the box - just username and password. In essence, you need a technical user in the database and retrieve a static secret. However, depending on the company policies, Postgres Databases in the cloud might not allow for technical users - only Service principals. In that scenario, you need to write your “own” OAuth in an external transform and insert the token retrieved from EntraID into the password of the Postgres data connection before the ingestions.
@HugoRodrigues @Guillaume unfortunately Snowflake does not let me login via username/password using the token as the password – this workaround might work for Postgres but not for Snowflake as far as I can tell… Am I out of options?
Hmm… as a last resort, you can check if CDATA - Palantir’s partner on data connection - has somehow a driver for snowflake that support OAuth.
https://www.cdata.com/
Snowflake Integrations: Drivers & Connectors for Snowflake: https://www.cdata.com/drivers/snowflake/
@dlandes
If you are open to writing code you can leverage the spark snowflake connector (which is installed by default in foundry transforms environment) to read or write from snowflake. There, you can pass the oauth token in the connection options.
You will need to use the egress policies for the snowflake url and the internal stage bucket of your account as inputs into your transform.
You‘ll loose the benefits of a first class Source but gain full flexibility.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.