Snowflake for SQL Server users - Part 7 - Authentication

Snowflake for SQL Server users - Part 7 - Authentication

Authentication in Snowflake is based around user identities. That’s very similar to what we have today with SQL Server authentication (i.e. SQL Server logs you on), as opposed to Windows Active Directory authentication (i.e. Windows AD logs you on), or  Azure Active Directory authentication (i.e. AAD logs you on).

I can create users if I’m a member of either the SECURITYADMIN or ACCOUNTADMIN roles.

Snowflake has a single specific password policy:

  • at least 8 chars long
  • at least 1 digit
  • at least 1 uppercase and 1 lowercase character

Similar to SQL Server logins, I can force a user to change password the first time.

I’m not a fan of these types of password policies, and the NIST recommends against them. I would like to see Snowflake (and SQL Server) have a more up-to-date password policy in place.

Users can be disabled/enabled via ALTER USER and you can set default warehouse and role for a user. DESCRIBE USER and SHOW USERS are useful commands for checking out who is configured.

MFA

Multi-factor authentication is also supported. You should have at least your administrators enrolled for MFA.

Enabling MFA currently means using the Duo application on your phone or device for the extra authentication. To be really good citizens within Azure and within Google Cloud, I’d like to see them also support Microsoft Authenticator and Google Authenticator as well.

Integration/Federation

OAuth 2.0 is supported and you can also achieve a degree of single sign on by implementing federation. Snowflake seem to work closely with Octa for single sign on and MFA.

While Snowflake list Azure Active Directory as a potential integration, it really needs a lot more work to integrate properly with AAD. In particular, there is no concept of granting access to Snowflake or role membership within Snowflake to groups in AAD.

Because there is no concept of group membership, the DCL commands are GRANT and REVOKE, and there is no concept of a DENY as you can need when working with groups.

There is also no concept of a logon trigger.

My Authentication Wish List

I really hope they will address concepts like groups and logon/logoff triggers fairly quickly. I do not like to see database administrators getting involved with individual user access. They should manage at a group level, and someone else in the organization should be deciding who is or isn’t a member of a group.

Better still would be to see a detailed integration with Azure Active Directory (AAD) and let the directory service do the hard work. For example, there’s no way to enforce admins to use MFA or to implement detailed MFA-related policies for other users and so on.

I heard they are looking to implement AAD integration via Octa. Direct integration would be my preference. Full integration with AAD would provide what’s needed for this and much, much more.

 

For an index to all posts in this series, see the first post here.

2019-09-20