Snowflake is able to interact with non-Snowflake tools/systems via “ Snowflake integration object”. For example, a “Snowflake S3 storage integration” enables Snowflake read and write S3 securely. a “Snowflake API integration” enables Snowflake call an Rest API endpoint.
Based on this doc, Snowflake supports four integration types:
- API integration
- Notification integration
- Security integration
- Storage integration
We have discussed API integration and storage integration in previous posts:
- Password Expiring Email Notifications for Snowflake Users (API Integration)
- Ingesting Data Into Snowflake (1): Snowflake Stage (Storage Integration)
In this post we’re looking at security integration. We’ll check out notification integration later on.
Snowflake security integration object points to an identity provider of your choice: Okta, Ping, Azure etc. The identity provider does user authentication or authorization or both when logging in Snowflake. The identity provider uses SAML or OAuth protocols to communicate with user client tools.
Want a little bit refresh about SAML and OAuth? Check out this post…
This post talks about Single Sign On (SSO) via SAML. We’ll configure Snowflake security integration to use Okta as IDP for user authentication.
1 Sign up Okta trial account and create an Okta SSO application for a Snowflake account
Use Gmail or work email register a 30-day trial account at Okta. Username to login Okta is the email address.
After logging in, use “Admin” button at top-right so you gain privileges to create Okta application. An Okta application is a standalone IDP service for one specific app i.e. Snowflake, Workday, ServiceNow etc.
Add a “SSO app” from Admin dashboard
Use “Browse App Catalog” to search“Snowflake”
Configure Snowflake app by providing “Application label” (Okta app name), Subdomain (our Snowflake account identifier) and click “Next”.
Choose “SAML 2.0” in “Sign-On Options” tab and uncheck “Disable Force Authentication”. Before clicking “Done” button, make sure you click button “View Setup Instructions” to open further steps in new browser page.
2 Configure Snowflake security integration following above “Setup Instruction” page
The “Setup Instruction” page is generated by Okta for this specific Okta application.
We should have done all before step 6. Double check that…Then copy following SQL statement from step 6 to Snowflake worksheet.
Replace the three placeholder values “[the value from step 6]” with following real values generated for this application.
- “Entity ID” maps to “SAML2_ISSUER” which is the unique ID of the Okta application who issues the SAML user assertion to Snowflake after authenticating user.
- “IDP SSO URL” maps to “SAML2_SSO_URL”. It is the REST API entry point of the IDP service.
- “Authentication Certificate” maps to “SAML2_X509_CERT”. It is IDP/this app’s public key used by Snowflake security integration to secure SAML communication between Snowflake(client) and Okta app(IDP server). Also, this certificate is used by Snowflake integration to verify SSO/SAML Assertion signature issued and signed by Okta SSO application. (Similarly for OAuth access token signature verification…we’ll discuss in future.)
Finally our security integration code is like this. Remember adding single quotes to the values! Go ahead and run it.
use role accountadmin;
CREATE SECURITY INTEGRATION OKTAINTEGRATION
TYPE = SAML2
ENABLED = TRUE
SAML2_ISSUER = 'http://www.okta.com/exk4jzkgx0OBIxobd697'
SAML2_SSO_URL = 'https://trial-8533056.okta.com/app/snowflake/exk4jzkgx0OBIxobd697/sso/saml'
SAML2_PROVIDER = 'OKTA'
SAML2_X509_CERT = 'MIIDqjCCApKgAw...f+LDjO'
SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'OKTA SSO'
SAML2_ENABLE_SP_INITIATED = TRUE;At this point, Snowflake login page should have present you an extra button “Sign in using OKTA SSO”.
So we can still use current user credential in Snowflake to login. But what user should we use to login using “OKTA SSO”?
We should use user credentials in Okta. The following final step is to configure Okta users for authentication and how to map Okta users to Snowflake users so they can use Snowflake with proper permissions.
3 Configure Okta users/people
Our SSO app only uses Okta users for authentication and then Snowflake uses Snowflake users for authorization. How does this work?
3.1 Assign Okta users to Okta App
Okta user is an email address. In this post we have used “fengliplatform@gmail.com” to sign up the trial account. Now we need to assign this user to our Okta SSO app so the app is able to access the user credential. Use following “Assign Users to App” button to do so (or from app page to “Assign app to people”).
Verify from application details for available/valid users for this app.
Important, Okta username(email address) will map to “login_name” of a Snowflake user. This is done by Snowflake security integration once Okta user has been authenticated. Snowflake then provide proper permission for this user to continue.
Currently, we are using a string to login Snowflake which is a default case.
If we “Sign in user OKTA SSO” now, Okta user “fengliplatform@gmail.com” can not find a matching login_name in Snowflake. We’re getting following error
SAML response is invalid or matching user is not found.
Contact your local system administrator.To resolve this, we’ll need to change Snowflake user’s login_name to the same email address so our Okta user gets authorized in Snowflake.
alter user fengliplatform set login_name = 'fengliplatform@gmail.com';
show users;Try it out again— clicking “Sign in using OKTA SSO” button
Success!
As we setup MFA in Okta, we’re using SMS code along with Okta SSO now!
After providing SMS code, we can continue working in worksheets again!
Note, Okta users can be imported as batch from existing AD/LDAP directories or from Snowflake. Check Okta doc for more info.
Bonus: what if certificate of SAML server/app on IDP side is expiring…
1> Rotate cert in IDP for our SAML app to get new cert.
2> modify security integration to use new cert.
alter security integration set SAML2_X509_CERT = <new cert string>Happy Reading!
