Skip to main content
All CollectionsIntegrations
SSO for Direct Connection
SSO for Direct Connection

Direct connect to a database with an SSO-enabled account

S
Written by Serge Gershkovich
Updated over a month ago

Single-sign-on (SSO) is a convenient and secure way of accessing your applications using a single set of login credentials.

SqlDBM provides two types of (independent) SSO logins:

  • Authentication to the SqlDBM account for project access

    • Please review the instructions in this guide.

  • Authentication to the database/cloud data platform for direct connect

    • Configure it by following the steps in this guide

Both types of SSO require a Small or Standard Enterprise license.

Currently, SSO for Direct Connection is only available for

  • Snowflake

    • Azure AD/Entra ID Oauth

    • Okta Oauth

  • Google BigQuery

    • Oauth

More databases and methods are coming soon.

Enable SSO in the Cloud Data Platform

SSO-enabled accounts must be configured using the instructions provided by your cloud data platform. For currently supported platforms, links to instructions can be found here:

Configure SSO direct connect details in SqlDBM

SSO configuration in SqlDBM must be performed by an account admin or alternate admin. Once configured, all users will have the SSO login method available as an option in Direct Connect.

As an admin, head to the account menu, click on "SSO settings" and proceed to the parameters described below.

Obtain the details from your chosen external provider using the instructions below.

Snowflake

Azure AD/Entra ID OAuth

ClientId: This is the Application (client) ID of your Azure AD application.

  • Go to the Azure portal.

  • Click on "Azure Active Directory" in the left-hand menu.

  • Click on "App registrations".

  • Click on the name of your application.

  • Copy the "Application (client) ID" - this is your ClientId.

RedirectUri: This will be hardcoded

AuthorizationEndpoint: This is the endpoint where your application sends a request to get an authorization code or access token. Please make sure to add this endpoint to your Azure AD Redirect URI for the client configuration.

  • The Azure AD authorization endpoint has a standard format: https://login.microsoftonline.com/{tenant}/oauth2/v2.0/authorize, where {tenant} is the directory (tenant) ID of your Azure AD application.

TokenEndpoint: This is the endpoint where your application sends a request to exchange an authorization code for an access token.

  • The Azure AD token endpoint also has a standard format: https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token, where {tenant} is the directory (tenant) ID of your Azure AD application.

The {tenant} can be found in the Azure portal:

  • Click on "Azure Active Directory" in the left-hand menu.

  • Click on "Properties".

  • Copy the "Directory ID" - that's your {tenant}.

Remember to replace {tenant} with the actual tenant ID in the endpoints.

Click "Save" once all the information has been provided.

Okta Oauth

ClientId:

  • Login to Okta

  • Navigate to "Applications" → "Applications" in the left-hand menu

  • Click on "Browse App Catalog"

  • Click on the name of your application

  • Under the "General" tab you will find your Client Id

RedirectUri: This will be hardcoded

From the left menu, navigate to "Security" -> "API"

Click on the "Metadata URI link", which will give you access to the authorization and token endpoints.

AuthorizationEndpoint: This is the endpoint where your application sends a request to get an authorization code or access token.

TokenEndpoint: This is the endpoint where your application sends a request to exchange an authorization code for an access token.

Click "Save" once all the information has been provided.

Google BigQuery

Oauth

  1. Open the Google Cloud console.

  2. In the left menu of the Google Cloud console, under APIs & Services, click Credentials.

  3. From the upper right of the Credentials page, click Create credentials, and from the dropdown, click OAuth client ID.

  4. In the OAuth client ID screen, enter the following details:

    1. For Application type, click Web application.

    2. For Name, enter a meaningful name — for example, SqlDBM .

    3. Under Authorized JavaScript origins, click Add URI and enter the SqlDBM app URL https://app.sqldbm.com/

    4. Under Authorized redirect URIs, click Add URI and enter your SqlDBM endpoint URI — https://app.sqldbm.com/login/sso/oauth

    5. Click Create to finish setup.

  5. From the corresponding OAuth client created dialog, copy the Client ID and Client secret and store it in a secure location.

Using SSO in Direct Connect

Once SSO details have been provided, users can choose between user/pass and SSO login methods in the Direct Connect options.

Provide your Server URL and click "Connect to Snowflake." (or other supported platform)

Select the account you wish to use and proceed with DB/Schema selection and the rest of the reverse engineering process.

or

For more details on reverse engineering, see the related post at the bottom of this page.

Snowflake user/role considerations

Snowflake's External OAuth can be configured to allow a pre-defined list of roles or permit the user to choose any role upon connection. SqlDBM provides a "Role" parameter in the Direct Connect dialog but users should ensure that its use does not conflict with the OAuth token settings.

External Oauth role

For full flexibility, SqlDBM recommends setting external_oauth_any_role_mode = 'ENABLE' in your token to allow the Role parameter to be used.

If no role is provided, our direct connect service will default to the user's predefined default role in Snowflake.

However, if external_oauth_any_role_mode role is enabled, no role is specified, and no default role set for the user in Snowflake, you may encounter an error.

Please refer to Snowflake's documentation to ensure proper role configuration and to avoid access issues.

Audience

The audience set in your external Oauth provider and Snowflake external auth configuration must match (see sample scripts below.) Failure to configure this will result in an “Invalid OAuth token” error.

Testing the external token

Test your Snowflake Oauth configuration by using the provided system function SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN

Sample scripts

Sample security integration script using Azure:

create security integration external_oauth_azure
    type = external_oauth
    enabled = true
    external_oauth_type = azure
    external_oauth_issuer = 'https://log.msft.com/asdf/v2.0'
    external_oauth_jws_keys_url = 'https://log.msft.com/asdf/dc/v2.0/keys'
    external_oauth_token_user_mapping_claim = 'upn'
    external_oauth_snowflake_user_mapping_attribute = 'login_name';
    external_oauth_any_role_mode = 'ENABLE'
    external_oauth_audience_list = 'https://asdf.com/asdfas-asdf-49dc-asdf-asdf'


Sample security integration script using Okta:

create security integration external_oauth_okta
	type = external_oauth 
        enabled = true
	external_oauth_type = okta
	external_oauth_issuer = 'https://xx.okta.com/oauth2/xxx'
	external_oauth_jws_keys_url = 'https://xx.okta.com/oauth2/xxx/vl/keys' 
	external_oauth_token_user_mapping_claim = 'sub' 
	externat_oauth_snowftake_user_mapping_attribute = ‘login_name‘ 
	external_oauth_any_role_mode = ‘ENABLE’
	external_oauth_audience_list=('https://x.snowflakecomputing.com/')

The Scope

The scope indicates the role assigned during login. For example, scopes like 'session:role:analyst' and 'session:role-any' determine which role the session will assume by default.

For Azure, click the copy button to copy and then paste the entire authentication API ID for the scope. For Okta, simply copy and paste the scope name (e.g. session:role-any).

Setup Hints

Please ensure that the application ID URI is part of the SF audience list in your configuration.

Please convert the authentication settings of the client from a Web platform to a Single Page Authentication platform (Authorization Code flow with PKCE).

The following screenshots should help:

1. Incorrect configuration:

2. Conversion screen:

3. Correct configuration:

See also:

Did this answer your question?