Connect-SFApp - Unable to get account client ID for account

[lziegler]

Hi,

A number of PowerShell scripts that I've successfully used (under Linux) to extract Snowsight worksheets successfully until about 4 Oct 2023 started failing after that.

It fails for all of my Snowflake accounts, regardless whether they're hosted in AWS or Azure, and irrespective of the authentication method, e.g.:

$appContext = Connect-SFApp -Account $sfAccount -UserName $user -SSO
$appContext = Connect-SFApp -Account $sfAccount -UserName $user -Password (ConvertTo-SecureString $password -AsPlainText -Force)

Sample output:

cmdlet Connect-SFApp at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Password: ************
Account 'XXXXX' in region 'australia-east.azure' is accessible at 'https://XXXXX.australia-east.azure.snowflakecomputing.com' and served by application server 'https://apps-api.c1.australiaeast.azure.app.snowflake.com' in main application 'https://app.snowflake.com'
Connect-SFApp: /home/lziegler/Documents/Snowflake/download_worksheets.ps1:37
Line |
  37 |      $appContext = Connect-SFApp -Account $sfAccount -UserName $user
     |                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Unable to get account client ID for account XXXXX
ERROR: Failed to connect to Snowflake!

A brief look in sfsnowsightextensions source code indicates that this issue might be caused by an API change to Snowsight:

// Get the client ID of Snowsight for this region
Tuple<string, string> deploymentSnowSightClientIDRedirectResult = SnowflakeDriver.OAuth_Start_GetSnowSightClientIDInDeployment(appUserContext.MainAppUrl, appUserContext.AppServerUrl, appUserContext.AccountUrl);
if (deploymentSnowSightClientIDRedirectResult.Item1.Length == 0 || deploymentSnowSightClientIDRedirectResult.Item2.Length == 0)
{
	throw new ItemNotFoundException(String.Format("Unable to get account client ID for account {0}", appUserContext.AccountName));
}

Thanks,
Lukas

[rishabh-a7da6]

same issue

I can see that we are using several API calls in different functions, According to me flow is

1. Start authentication to get clientId and oauth-nonce cookie information(start-oauth/snowflake)
2. Get Redirection code after success login(either via credentials oauth - oauth/authorization-request)
3. Get access token(AuthTokenSnowsight) using code and other parameters. (complete-oauth/snowflake)

After getting AuthTokenSnowsight we can use this token to do all functions related to snowsight worksheets for ex

1. Get all worksheets - v0/organizations
2. Get specific worksheet - v0/queries/worksheetId
3. Create Worksheet etc.

from above 3 steps, i am able to complete 2nd and 3rd from API call itself but since module is failing at step 1 I am not able to get client_id for that i used Oauth and created a security integration in snowflake by myself and got client id from there.

I am also able to get Redirection code using that client_id but since from first step we need two things, First client_id(I have that) and second oauth-nonce which I am not able to get.

Also this api version v0 seems new. I checked but couldn't find any documentation. I was able to find api/v2/statements/ which is used to retrieve data from snowflake using access and refresh token.

I also tried using access_token in oauth(api/v2/statements/) flow but that access token didn't work for v0 api calls. It will be really helpful if we can get any documentation from this new API version from author or from anyone if he/she has any idea about it.

[sfc-gh-mybarra]

@lziegler @rishabh-a7da6 thanks for filing this issue. We are aware of changes to the Snowsight UI that will eventually cause breaking changes to snowsightextensions. In short, snowsightextensions is reaching end of life.

Snowflake has recently moved to using a global URL as the preferred format for authenticating to an account. You can read about it in the Snowflake Documentation. If you haven't attempted to connect using the org-account name method, or the account locator noted in the same document, you can try one of those two methods.

[remigious-J]

@lziegler @rishabh-a7da6 were you able to get this issue resolved.

@sfc-gh-mybarra I tried both org-account name method and the account locator, It doesn't seem to work. This was working for me till yesterday and by the time I emailed you. It throwed an error the next day.

[lziegler]

@remigious-J I tried with all formats suggested in the Snowflake Documentation, and a few more permutations, to no avail unfortunately.

[remigious-J]

@lziegler thank you. Is there is any other options that you have to download the snowflake worksheet and import it in an automated way. Any help would be much appreciated. Thanks.

[joshuataylor]

I believe it has to do with it not following directs, as https://github.com/Snowflake-Labs/sfsnowsightextensions/blob/main/UtilityDrivers/SnowflakeDriver.cs#L865C35-L865C35 has redirects turned off.

I get this log:

Account 'foo1234' in region 'ap-southeast-2' is accessible at 'https://foo1234.ap-southeast-2.snowflakecomputing.com' and served by application server 'https://apps-api.c1.ap-southeast-2.aws.app.snowflake.com' in main application 'https://app.snowflake.com'

Connect-SFApp: Unable to get account client ID for account foo1234

However if you follow the redirect (eg using cURL), the header is set on the redirected page (oauth/authorize).

[remigious-J]

I believe it has to do with it not following directs, as https://github.com/Snowflake-Labs/sfsnowsightextensions/blob/main/UtilityDrivers/SnowflakeDriver.cs#L865C35-L865C35 has redirects turned off.

I get this log:

Account 'foo1234' in region 'ap-southeast-2' is accessible at 'https://foo1234.ap-southeast-2.snowflakecomputing.com' and served by application server 'https://apps-api.c1.ap-southeast-2.aws.app.snowflake.com' in main application 'https://app.snowflake.com'

Connect-SFApp: Unable to get account client ID for account foo1234

However if you follow the redirect (eg using cURL), the header is set on the redirected page (oauth/authorize).

@joshuataylor even though redirect is set to true, it will fail while trying to login.

youraccount.snowflakecomputing.com/session/v1/login-request
This post method does not work with auth token anymore.

[joshuataylor]

Hi!

I've done some research into the OAuth flow + response for Snowsight, and have documented my findings here.

I believe this should work well for those that have a "simple" setup, for everything else a more complex workflow of app.snowflake.com -> start-oauth -> etc.

I have also created an example Python implementation here.

Usage:

python snowsight_basic.py <ACCOUNT_NAME> <REGION> <USERNAME> <PASSWORD>

[remigious-J]

@joshuataylor this is great. Can we do this for SSO login?

[joshuataylor]

I'll have a quick look, but I believe so.

[CumulusLenticularis]

@joshuataylor Hi, nice work with snowsight_basic.py. Can I ask you if the login could be extended by 2FA Duo Security please? response_data = json.loads(response.read().decode()) returns - see attachment.

[joshuataylor]

I'll take a look, we also use duo internally for SF only. Not 100% sure, but I think the workflow would be to prompt the user for their duo token or something during authentication.

[CumulusLenticularis]

Final terminal log:

[joshuataylor]

Okay more auth notes, trying to login to Snowsight is a pain :).

Private Key Auth

Using a Private Key would be great (as MFA is a pretty common requirement), but sending this payload:

{"data": {"ACCOUNT_NAME": "<ACCOUNT_NAME>", "LOGIN_NAME": "<LOGIN_NAME>", "CLIENT_APP_ID": "Snowflake UI", "CLIENT_APP_VERSION": 20231219004219, "AUTHENTICATOR": "SNOWFLAKE_JWT", "TOKEN": "xxx"}}

Always returns a token/masterToken, which is used for drivers/"classic console".

No matter what I try I can't get this to a redirectURI or any other way that I could use to authenticate within Snowsight.

I've tried disableDirectLogin=false and other tricks such as setting the client environment, but nothing seems to work.

Does anyone know a way to authenticate using a token/masterToken to the endpoints at https://apps-api.c1.<REGION>.aws.app.snowflake.com, such as /bootstrap?

We could use a csrfToken from an unauthenticated /bootstrap request for future requests.

It seems this requires a cookie.

MFA (Duo Auth)

Important

Create a test account for Duo when testing, so you don't lock out your main account!

Snowflake MFA Docs

Seems this can be either via Push to Mobile, Phone Call or Text Message.

You choose which when setting up.

I've tested with Push Notification, I believe the other authentication methods should be similar.

MFA Passcode

The Using MFA with SnowSQL SF Documentation explains how you can send the passcode that the Duo apps shows you in the login-request.

This should be the preferred method for users, as you can type the code from their app, and you don't need to go through the hassle of authenticating, downside is you need their app.

@CumulusLenticularis if you are using their mobile app, you'll just need to add the passcode parts below to the Python code and you'll be logged in! 🎉

Downside, you'll need to regenerate the passcode in the app each time, or cache the login token and reuse that.

Payload looks like this to login-request:

{"data": {"ACCOUNT_NAME": "<ACCOUNT_NAME>", "LOGIN_NAME": "<LOGIN_NAME>", "CLIENT_APP_ID": "Snowflake UI", "CLIENT_APP_VERSION": 20231219004219, "PASSWORD": "<PASSWORD>", "EXT_AUTHN_DUO_METHOD": "passcode", "PASSCODE": "<PASSCODE>"}}

EXT_AUTHN_DUO_METHOD / PASSCODE are the extra parts.

Valid Response:

{
  "data" : {
  "nextAction" : "EXT_AUTHN_DUO_BEYOND",
  "inFlightCtx" : "ver:1-hint:foobar",
  "authnMethod" : "PASSWORD_MFA",
  "additionalAuthnData" : {
    "DUO_BEYOND_SIGN_REQUEST" : "TX|AAA=|BBB:APP|CCC=|DDD",
    "DUO_BEYOND_API_HOST" : "api-xxx.duosecurity.com",
    "DUO_SIGN_REQUEST" : "TX|AAA=|BBB:APP|CCC=|DDD",
    "DUO_API_HOST" : "api-xxx.duosecurity.com"
  }
},
  "code" : "390128",
  "message" : "Duo Security authentication is successful.",
  "success" : false,
  "headers" : null
}

Then you pass in the inFlightCtx to login-request, note this is not inside the data object!:

{
    "data":
    {
        "CLIENT_APP_ID": "Snowflake UI",
        "CLIENT_APP_VERSION": 20231221140452
    },
    "inFlightCtx": "ver:1-hint:xxx"
}

This returns you the response as usual, with redirectURI having the complete-url.

MFA Workflow

I'm still looking into seeing if we can use this, as setting the CLIENT_APP_ID to Snowflake UI allows logging into Snowsight, anything else returns a token/masterToken.

Using just the username/password, the login workflow looks like this:

Step 1: Authentication as normal (Username/Password)

When authenticating with Duo, the response is:

{
  "data": {
    "nextAction": "EXT_AUTHN_DUO_BEYOND",
    "inFlightCtx": "ver:1-hint:foobar",
    "authnMethod": "USERNAME_PASSWORD",
    "additionalAuthnData": {
      "DUO_BEYOND_SIGN_REQUEST": "TX|BBB=|CCC:APP|AAA=|DDD",
      "DUO_BEYOND_API_HOST": "api-71cb7a1c.duosecurity.com",
      "DUO_SIGN_REQUEST": "TX|BBB=|CCC:APP|AAA=|DDD",
      "DUO_API_HOST": "api-xxx.duosecurity.com"
    }
  },
  "code": "390124",
  "message": "Duo Security authentication is required.",
  "success": false,
  "headers": null
}

Snowflake MFA Docs
Code 390124 - EXT_AUTHN_REQUESTED - Duo Security authentication is required.

Step 2: Duo Web Auth

Duo then requires you to verify your token. It would be really handy to be able to query Duos API to ask it to send a specific method, instead of asking the user, which can be done via their API...

But alas, the way Snowflake does it is they redirect to:

https://api-xxx.duosecurity.com/frame/web/v1/auth?tx=TX|BBB=|CCC&parent=https%3A%2F%2F<ACCOUNT_NAME>.<REGION>.snowflakecomputing.com%2Fconsole%2Flogin%23%2F&v=2.8

BBB=|CCC - The same BBB=|CCC as above, the string after TX| and before :APP|.

Step 3: Duo Auth Callback

Once the user has confirmed their identity through Duo, they are redirected back to Snowflake.

[remigious-J]

@joshuataylor Could you please help with the SSO login method?

[joshuataylor]

Confirming SSO is login via Google etc? I know you can use Google Azure etc https://community.snowflake.com/s/article/configuring-g-suite-as-an-identity-provider

I think you won't be able to do this completely through the CLI, as the user probably have 2FA etc setup, and logging into say Google would be a pain .

A better way would be to spin up a local webserver, redirect to the normal login flow, then have the redirect URL somehow be the localhost URL. Python should be pretty easy for this, and most languages have a basic webserver you can use.

I believe this is how the drivers do it for SSO/MFA.

[joshuataylor]

As a personal user (eg no ties to an existing business, my dayjob doesn't have this setup), is there an easy way to setup OAuth for testing?

Seems as this is an "Enterprise feature", it's supported through the more commercial offerings:

1. Google
2. Azure

Supported identity providers

In addition to the native Snowflake support provided by Okta and AD FS, Snowflake supports using most SAML 2.0-compliant vendors as an IdP, including..

Okay cool!

I'll take more of a look into getting this setup, as in theory the authentication flow would be the same. 🤷

EDIT 1: Authentik and other offerings look like they'll work well for testing, and it's self hosted/cheap too.

[joshuataylor]

Initial notes

I've started writing my initial notes around how the Single Sign-On (SSO) authentication flow works, this endpoint is new, as Snowflake seems to be transitioning to this - it is a different endpoint compared to how the standard drivers currently authenticate.

I think the final step for authenticating to Snowsight is to figure out how to set CLIENT_APP_ID to Snowflake UI where possible, as this seems to have hardcoded logic in the backend, as it will return a redirectUri and other relevant goodies when provided with Snowflake UI.

I also believe it might be possible to authenticate on the Snowsight endpoint using the SAML token you get back from localhost, though this seems like a design flaw if possible?

I've also been using Auth0 as my test IdP (unaffiliated, was the easiest to get setup and it's free), but every provider should return the same, in theory.

Existing Drivers

Let's quickly cover how the existing drivers work for SSO (aka classic console); the snowflake-connector-python webbrowser.py](https://github.com/snowflakedb/snowflake-connector-python/blob/main/src/snowflake/connector/auth/webbrowser.py) has a great rundown of how it works.

Variable	Purpose
ACCOUNT_NAME	The Snowflake account name
LOGIN_NAME	By default, it is an email address.
REGION	The Snowflake region (us-east-1, etc)

1. In your code, create a socket that listens on localhost (aka a webserver) on a random dynamic port. This is pretty simple with most languages that have support for sockets etc.
2. POST to https://<ACCOUNT_NAME>.<REGION>.snowflakecomputing.com/session/authenticator-request with
   The web browser opens, the user authenticates.
3. Redirect to /fed/login
4. Redirect back to `http://localhost:PORT?token=xxxx, which has the token in the query parameter.
5. Now you have the token, POST to /session/v1/login-request with the following JSON body:

{
  "data": {
    "CLIENT_APP_ID": "WHATEVERCLIENTID",
    "CLIENT_APP_VERSION": "1.5.1",
    "SVN_REVISION": null,
    "ACCOUNT_NAME": "<ACCOUNT_NAME>",
    "LOGIN_NAME": "<LOGIN_NAME>",
    "AUTHENTICATOR": "EXTERNALBROWSER",
    "TOKEN": "<TOKEN>",
    "PROOF_KEY": "<PROOF_KEY_FROM_EARLIER>"
  }
}

This process returns a token and a masterToken

However the workflow for Snowsight uses a different endpoint, I have started documenting my findings - here in my WIP notes.

--

I believe the problem can be solved (authenticating via SSO, Private Key, etc), I'll have a bit more of a dig.

Sidenote, I've also somehow managed to somehow break the authentication endpoint a few times when sending valid data, which returned an incident number and some internal Java code :-).

I'll also provide a Python 3.x example implementation, I haven't worked on .NET Core in a few years and I hope people can read the notes and follow along with the Python implementation - it's pretty basic, the main challenge is figuring out the magic words.

[remigious-J]

@joshuataylor Thank you for your awesome work on this. Could you also help to update the sample implementation code?

[joshuataylor]

If i can get it to work with login other than username/password, i will.

[CumulusLenticularis]

@joshuataylor Hi Josh, DUO authentication works flawlessly. Thank you very much.

Worksheet response returns all necessary data. Except for one: folderName. The folderId item is present in the response, but the folderName item is missing where it should be. It's not a tragedy, but if you have any idea how to get the folderName as well, could I ask for your advice? Thank you very much.

DUO_login_py.txt

[joshuataylor]

I'll take a look once I figure out the authentication for SSO and hopefully Duo without passcode.

I'm hoping the documentation and finding for all these helps others build tools :-).

What I want to build after this is a plugin for Jetbrains Database Tools Plugin (Datagrip, plugin for Pycharm/IntelliJ etc) so

[danielodievich]

Original creator of this here. A former colleague of mine alerted me to this discussion.

First, it is amazing to see the lengthy discussion, showing that people are actually using this. Thanks folks!

The product team behind Snowsight never intended the Snowsight APIs to be accessed from outside of Snowsight/Numeracy app. I really wanted to, so I reverse-engineering auth (u/p + MFA and SSO + MFA) by carefully studying browser traffic in chrome debugger and in some cases where it wouldn't show things, in network monitoring tools. The product team was very, very reluctant to let this be released to the public and hence the warning on the homepage of this tool (This project is not an officially supported product of Snowflake. Use at your own risk.).

The auth flow held for almost 3 years but it is clear it has changed. What I can see from just a few minutes of exploration is that the ClientID lookup changed in the following way:

https://apps-api.c1.us-west-2.aws.app.snowflake.com/start-oauth/snowflake?accountUrl=https%3A%2F%2Frr87293.snowflakecomputing.com&classicUIUrl=&state=%7B%22csrf%22%3A%2273b90174%22%2C%22url%22%3A%22https%3A%2F%2Frr87293.snowflakecomputing.com%22%2C%22windowId%22%3A%22d0e646a1-5674-4235-9b21-bfd25cab2fc0%22%2C%22browserUrl%22%3A%22https%3A%2F%2Fapp.snowflake.com%2F%22%7D returns S8_SESSION cookie instead of oauth-nonce-##### cookie, invalidating logic in https://github.com/Snowflake-Labs/sfsnowsightextensions/blob/main/Commands/Authentication/ConnectAppCommand.cs#L263-L303.

Instead it appears to be redirecting to https://apps-api.c1.us-west-2.aws.app.snowflake.com/sessionmanager/login/oauth2/authorization/rr87293.snowflakecomputing.com?s8state=%7B%22csrf%22%3A%2273b90174%22%2C%22url%22%3A%22https%3A%2F%2Frr87293.snowflakecomputing.com%22%2C%22windowId%22%3A%22d0e646a1-5674-4235-9b21-bfd25cab2fc0%22%2C%22browserUrl%22%3A%22https%3A%2F%2Fapp.snowflake.com%2F%22%7D&s8AccountUrl=https%3A%2F%2Frr87293.snowflakecomputing.com, which then returns client_Id in URL and oauth-nonce-##### cookie. So an additional redirect to convert S8_Session to oath-nonce cookie is likely all you need.

That would likely be an easy fix - just add another call to the redirect and read the client_id and oauth nonce. From what I can tell, the subsequent steps haven't changed and so I expect that is the only fix you'd need.

I believe @sfc-gh-mybarra made it clear that this extension is no longer by him or @sfc-gh-rbacastow who for a while were caretakers. I am no longer at snowflake and I don't use Snowsight enough to want to debug this. I also don't have a single Snowflake instance that is behind SSO accessible to me, so any fixes I'd make would only apply to the non-SSO paths.

However, I think @joshuataylor is trying to reinvent this in his Python, so Joshua, are you up for that refactor? I'd be happy to do a Zoom collab for knowledge transfer and you can take it away?

[joshuataylor]

Thanks so much for the reply @danielodievich!

Yeah the login workflow has slightly changed, as now you need to ensure you always pass Snowflake UI as the CLIENT_APP_ID, as this will then give you the redirectUri you need.

Today I managed to get SSO+Duo working as well, which is great.

I provided a Python example implementation as (IMHO) it's a straight forward example others can use as a base to convert to another language, alongside the notes (once I've updated them).

@danielodievich I would love a zoom/video call, to sanity check my logic - thank you this is incredibly kind. Please email me at joshuataylorx@gmail.com .

[joshuataylor]

I've managed to get SSO working, so now the only thing left is Duo. Once i have more time again I'll look into it more, but the following works:

• Username & Password

Usage: python3 snowsight_basic.py -m password -a ACCOUNT_IDENTIFIER -u LOGIN_NAME -p PASSWORD

• SSO

Usage: python3 snowsight_basic.py -m sso -a ACCOUNT_IDENTIFIER -u LOGIN_NAME

• Duo (using Pass Code)

Usage: python3 snowsight_basic.py -m duo_passcode -a ACCOUNT_IDENTIFIER -u LOGIN_NAME -p PASSWORD --passcode 12345678

For Duo without passcode, I'll try to get to this ASAP.

snowsight_basic.py

[remigious-J]

@joshuataylor This is awesome. Thank you so much. is there is any reference for uploading worksheets in the document?

[joshuataylor]

So good news, I managed to get authentication to Snowsight working with:

• Username and Password
• Federated/SSO
• MFA (Duo, Push Notification)
• MFA (Duo, Passcode)
• Key-pair authentication (Authenticating with your private key)

I've changed the implementation code to go through the OAuth workflow, as that seems the be new prefered way of working.

I met with @danielodievich on my Wednesday, and had a great chat about the goals of the sfsnowsightextensions project, the changes that were made that has resulted in issues, and then going over the request/responses (I had just managed to get the auth flow to work the night before, so it was good timing!).

Once I have more time, I'll update the notes for the implementation code.

I've tried to patch sfsnowsightextensions, but I'm having a few issues with the client_id, as this now returns in the URL and not the body, so the apiGET will need to be updated.

All Requests:

1. Get the account, appServerUrl, region, url (app server URL):
   GET https://app.snowflake.com/v0/validate-snowflake-url?url=example12345.us-east-1&isSecondaryAccount=false

def validate_snowflake_url(account_identifier: str) -> dict:

2. Get the csrfToken:
   GET <APP_SERVER_URL>/bootstrap

3. start-oauth/snowflake with {{"csrf":"{0}","url":"{1}","browserUrl":"{2}"}}

This has been changed.

GET /start-oauth/snowflake?state=xx, returns the S8_SESSION=XXX cookie.

1. Redirects to <APP_SERVER_URL>/sessionmanager/login/oauth2/authorization/<INSTANCE_NAME>, with the oauth-nonce-xxx=XXX cookie.
2. Redirects to <INSTANCE_URL>/oauth/authorize.

https://github.com/joshuataylor/snowflake-notes/blob/refs/heads/main/examples/snowsight/python/snowsight_basic.py#L189

---

Then you have logic per authentication flow.

[joshuataylor]

I have a PR for this at #46 and would appreciate testing :). I have tested username+password and SSO.

[lziegler]

These changes fixed my problem for my Snowflake accounts, thanks!

@sfc-gh-mybarra, I've reviewed and approved the code, but I guess we're still waiting for your authoritative PR approval.

[sfc-gh-mybarra]

I requested approval to merge the pull request. This seems to be the first time that a pull request has come from a customer/user. I'll update you all once I have more information.

[sfc-gh-mybarra]

@joshuataylor @lziegler I posted a message in your pull request. Please review and acknowledge.
Additionally, I would like to offer you the opportunity to become the maintainer(s) of snowsight extensions. If you are interested in learning more, please feel free to schedule a meeting with me and I can provide further details. Please use the link to my calendar below.

https://calendar.google.com/calendar/u/0?cid=bWljaGFlbC55YmFycmFAc25vd2ZsYWtlLmNvbQ

[joshuataylor]

Apologies for the delay in getting back to you, I've been out due to surgery over the last week.

I'll send you an email in the next day or so.

[sfc-gh-mybarra]

@joshuataylor feel better. You can provide your acknowledgment in this thread when you have time.

[lziegler]

@sfc-gh-mybarra, I provided my acknowledgement on the pull request:
#46 (comment)