Snowflake MFA token caching not working

[mbreeze]

If I connect to Snowflake as per https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-snowflake  

(using Snowflake as my IDp - without Microsoft AAD integration), and MFA token caching is enabled https://docs.snowflake.com/en/user-guide/security-mfa.html#label-mfa-token-caching  

ALLOW_CLIENT_MFA_CACHING  = true

ALLOW_ID_TOKEN  = true

I get 2 DUO push notifications for each dataset connected to PowerBI, each time the dataset is refreshed. MFA Tokens are not cached.

Due to delays or missing push notifications, the Snowflake account gets locked.

I have tested the Windows ODBC driver with the following authenticator option in a new DSN and it caches tokens perfectly:

https://docs.snowflake.com/en/user-guide/odbc-parameters.html#additional-connection-parameters  

authenticator=username_password_mfa

However it doesn't seem to work in the Simba Snowflake ODBC driver that is natively installed in PowerBI:

C:\Program Files\Microsoft Power BI Desktop\bin\ODBC Drivers\Simba Snowflake ODBC Driver

even if I add it to a microsoft.snowflakeodbc.ini file:

[Driver]
authenticator=username_password_mfa

[v-robertq-msft]

Hi, 

If you want to get a solution for this kind of connection problem as soon as possible, I suggest you to open a support ticket to get direct help from the technical support team of Microsoft.

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

https://powerbi.microsoft.com/en-us/support/

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

[mbreeze]

Done - I've raised a support ticket. 

[v-robertq-msft]

OK, thanks for the response!

[Danielatlanta]

@mbreeze Did you find a resolution for this? If so, would love to know the resolution steps. Thanks.

[aldredd]

This is a major issue for us - we've fully adopted Snowflake, but if we can't reliably use PowerBI, we'll have to drop it from our Org.

@v-robertq-msft , is the previous support request a generic one for this issue, or should we raise a separate one ourselves?

[MAlviar]

This is also happening in our environment. Is there a resolution to the issue?

We have an open ticket with Microsoft, but they are saying it is a Snowflake problem.

[aldredd]

@MAlviar ,

I raised a support ticket, and after much back & forth they acknowledged that it was a MSFT issue, that it was in the backlog to look at, but no ETA. (I literally shared the Snowflake ODBC Driver documentation with them to show them that this was a poor implementation of the driver on MSFTs part). I'm not convinced they actually believe this is an issue though, and just said that to get me to go away.

I also spoke with our account manager at MSFT, who just suggested we use Synapse - really helpful. I've also spoken to Snowflake to ask them to chase MSFT about it, but they don't seem interested either.

Solution Options;

1) Install the ODBC driver from Snowflake directly, setup a local DSN with that driver and (importantly) add the username_password_mfa parameter. Then in PowerBI, select ODBC, and point at that DSN. Not ideal, as it's a bit of messing around, and you lose the ability to do Direct Query (import only), but does resolve the MFA issue (which further demonstrates this is a MSFT issue)

2) Create your own Snowflake custom connector using the Power Query Connector SDK (https://learn.microsoft.com/en-us/power-query/install-sdk), and implement it correctly.

Our plan longer term is 2), but for now, we're doing 1) ... whilst also evaluating alternative BI platforms

[MAlviar]

Thank you @aldredd for sharing your experience, and the possible workarounds.  I'll discuss these with my team.

[Michael_12345]

I think we have the same problem, and this is very frustrating. I was impressed when I switched on MFA for my account and my first connection to Snowflake from Power BI did a "Send me a Push" notification in Duo on my phone.

I approved and it appeared to connect OK and start downloading schema/data information. But, I quickly noticed it was sending multiple MFA push notifications and despite me approving all of them, it just keeps sending them.

I'd rather have my users authenticating more securely. Please help!

[MAlviar]

@Michael_12345 ,

Our team has worked with both Snowflake and MS PowerBI to find a solution to this issue, trying out the different workarounds suggested (both at the Snowflake config and also in PowerBI). Sadly, it boiled down to Microsoft saying that their ODBC driver for Snowflake does not handle MFA/DUO connections properly, and asked us to file a product enhancement request.

Moving forward, we have decided on this workaround:
a) Checked and identified which IP addresses are used by the PowerBI users to connect to Snowflake
b) Created a Network Policy in Snowflake that would limit user connections to those IP addresses identified in (a) --->  aka IP pinholing
c) Disabled MFA on those PowerBI users

And since PowerBI users do not have MFA enabled in their accounts, they do not receive the multiple DUO prompts anymore.