Connect to Snowflake with Active Directory

[chazguerrero]

Our snowflake is backed by azure ad. is there a way to connect this way?

[hankinsoft]

Hi. Unfortunately not currently. #406 is a similar issue and the underlying issue is that the snowflake connector I'm using (libsnowflakeclient) does not support alternative connection types. While its something I would like to get added at some point, its not something I'm currently able to commit to or provide an ETA on.

[hankinsoft]

As mentioned in #859, Snowflake support in SQLPro currently only provides username and password authentication and this will mostly likely not change in 2013. There is a possibility MFA would work if you specify your password in the format of <password_string><passcode_string> (similar to how the snowflake documentation suggests for an ODBC connection) but it's not something I'm currently supporting.

[jaredx435k2d0]

@hankinsoft are you saying that the passcode_in_password=True parameter is specified in the driver's connect() method?

Relevant text from that docs link:

To use a Duo-generated passcode instead of the push mechanism, one of the following parameters must be specified for the driver in the connect() method: passcode_in_password=True

Where: ... If passcode_in_password=True, then the password and passcode are concatenated, in the form of <password_string><passcode_string>.

[jaredx435k2d0]

Ok, I just did some testing here:

If I enable Duo Push, then I can log into Snowflake via SQLPro Studio. The Duo Push prompt comes up, I hit allow, and then SQLPro Studio connects, and I can see my databases (and Snowflake version) in the Database Tree.

The issue is that it seems to try to connect many times. Right after the initial connection, like 5-7 more Duo Pushes queued up. Even if I accept them all as quickly as I can, I can never see any objects beneath the database level. If I try right-clicking and selecting "Refresh", I don't get another Duo Push and the refresh still won't show me anything.

That said, I can execute queries just fine (including SHOW SCHEMAS IN MY_DB_NAME) without issue. It seems that I just have to approve one Duo Push per tab / session.

I tried just appending my code to the end of my password, but that gives me:

I also executed alter account set allow_client_mfa_caching = true;, according to the docs here, but steps 2 and 3 of that look to be SQLPro Studio code changes:

2. In the client connection string, update the authenticator value to authenticator = username_password_mfa.
3. Add the package or libraries needed by the driver or connector

I recognize that up above, you state you're not supporting this right now.

That said, it honestly looks like it's almost working by default. Would it be reasonable to request a spike task on this? I think that at least I personally could convince my organization (requiring us to all switch to SSO) to let us continue to use the app if we could use MFA.

It seems like either doing steps 2 and 3 above OR setting passcode_in_password=True "in the driver connect() method" may do it.

[hankinsoft]

It seems like either doing steps 2 and 3 above OR setting passcode_in_password=True "in the driver connect() method" may do it.

Step 3 is not currently an option as it will not work with the snowflake library I am required to use.

I'm going to do a SQLPro for Snowflake build where you can set the following login parameters:

• passcode
• passcode in password
• authenticator

I believe you would either set passcode, OR passcode in password and then include the passcode as part of your password as specified here. It will also let you set the authenticator.

If you find them helpful, I will add them to Studio as well.

[hankinsoft]

This testing build: https://sqlprostudio.s3.us-east-1.amazonaws.com/temp/SQLProSnowflake.2023.04.app.zip

Will let you choose 'Advanced' -> 'Parameters' when editing a connection. Here you can specify passcode, passcode in password, and authenticator. If you have existing connections open when you modify these, you MIGHT need to close and re-open the app as the database pool might re-use those existing connections with the previous parameters.

[jaredx435k2d0]

Thank you so much! Excited to try it out

But, when trying to open / install it, I'm getting the following error:

It's trying to get me to do a trial in the app store (maybe because it's SQLPro for Snowflake instead of SQLPro Studio?):

I've got a license key for SQLPro Studio, but not for SQLPro for Snowflake. I tried that key, but it didn't take it.

[hankinsoft]

There should be an x at the very top right of the ‘try sqlpro dialog’. If you click that you can continue.

On Fri, Jan 20, 2023 at 8:15 PM jaredx435k2d0 @.***> wrote:

Thank you so much! Excited to try it out

But, when trying to open / install it, I'm getting the following error: [image: image] https://user-images.githubusercontent.com/111536894/213827954-53607ebb-5acd-4d3c-a29a-c8caa29c2ea5.png

It's trying to get me to do a trial in the app store (maybe because it's SQLPro for Snowflake instead of SQLPro Studio?): [image: image] https://user-images.githubusercontent.com/111536894/213827982-8d4b3b90-f9f4-4171-9deb-8ceead037588.png

I've got a license key for SQLPro Studio, but not for SQLPro for Snowflake. I tried that key, but it didn't take it.

— Reply to this email directly, view it on GitHub https://github.com/hankinsoft/SQLPro/issues/661#issuecomment-1399093923, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAYDTUGHX4CXDCIMPJMTIDDWTMTCXANCNFSM475J654A . You are receiving this because you were mentioned.Message ID: @.***>

[jaredx435k2d0]

Thanks for that tip.

Tried the build. Unfortunately, the behavior was the exact same.

I tried only filling out the passcode parameter. I also tried appending my passcode to my password and setting Passcode in password to: on "on" True "True"

But got this each time:

[hankinsoft]

Tried the build. Unfortunately, the behavior was the exact same.

That's unfortunately. I'm going to use those settings in incase someone else finds them useful in the future.