Product Documentation

Appearance

EE: All cloud platforms

CREATE EXTERNAL AUTHENTICATION ​

Configures a Yellowbrick Instance to authenticate with an external IDP. The information supplied in this command tells the instance how to validate a supplied JWT token, and how to map the named claim to a database username.

Authentication properties are stored in the system view sys.external_authentication.

CREATE EXTERNAL AUTHENTICATION name
ISSUER ‘string’
USER_MAPPING_CLAIM ‘string’
GRANT (role_name [, role_name] …)
GRANT_CLAIM 'grant_claim'
[ AUDIENCE ('string', [, 'string'] ...) ]
[ AZP 'string' ]
[ ALLOWED_ROLES (role_name [, role_name] …) ]
[ DISALLOWED_ROLES (role_name [, role_name] …) ]
[ KEY 'public_key' ]
[ AUTO_CREATE true | false ]
[ ENABLED true | false ]
ISSUER
The issuer identifier established in the IDP integration, created in Yellowbrick Manager or through Kubernetes CRDs. Typically this is the URL of the authorization server that issues the JWT. Required.
USER_MAPPING_CLAIM
A property-name in the payload of the JWT whose value is to map to a Yellowbrick database user. For example, email or preferred_username. Required.
GRANT
The names of Yellowbrick database roles that this database user should be a member of when auto-creating a role. Optional if there is a GRANT_CLAIM. The database roles need not be present when issuing this command.
GRANT_CLAIM
The name of a claim in the JWT that holds a list of database role names that the auto-created user is made a member of. Optional as long there is a GRANT.
AUDIENCE
A list of strings, any of which that must match the aud claim of the JWT. Optional. During JWT validation, one of the strings supplied in here must match. Note that if the aud claim in the JWT is a list of strings, then any of those strings must match the ones supplied here.
AZP
Name of the authorized party that must be present in the azp claim of the JWT. Optional.
ALLOWED_ROLES
List of primary login roles that will pass authentication. Optional. This check is skipped if the list is empty.
DISALLOWED_ROLES
List of primary login roles that will fail authentication. Optional. Can be used to selectively disallow named login roles.
AUTO_CREATE
If true, then automatically create the database user if it does not exist. Optional. If not specified, defaults to false.
ENABLED
If true, then this authentication is active. Optional. If not specified, defaults to false.
PUBLIC_KEY
The public key of the issuer for verification of JWTs. Optional. Normally the public key is fetched from the issuer on demand when validating JWTs; specifying a key here disables this behavior.

Microsoft Entra ID in Azure Government ​

Government tenants are configured through this command alone; no Government-specific product setting is involved. Only the issuer differs from a commercial tenant, and which issuer you use depends on the token version your application requests.

For v1.0 tokens the Government issuer is identical to the commercial one:

sql
CREATE EXTERNAL AUTHENTICATION my_gov_auth
  ISSUER 'https://sts.windows.net/{tenantId}/'
  USER_MAPPING_CLAIM 'upn'
  GRANT ('consumer')
  AUDIENCE ('{clientId}')
  AUTO_CREATE
  ENABLED;

For v2.0 tokens only the host differs from commercial, .us instead of .com:

sql
CREATE EXTERNAL AUTHENTICATION my_gov_auth
  ISSUER 'https://login.microsoftonline.us/{tenantId}/v2.0'
  USER_MAPPING_CLAIM 'email'
  GRANT ('consumer')
  AUDIENCE ('{clientId}')
  AUTO_CREATE
  ENABLED;

Operational notes:

  • The ISSUER string must equal the token's iss claim byte for byte, because the lookup is an exact match.
  • The database node needs egress to the issuer's discovery host: sts.windows.net and login.microsoftonline.us for v1.0, or login.microsoftonline.us alone for v2.0.
  • If egress to commercial endpoints is blocked, prefer the v2.0 issuer, or pin the key with PUBLIC_KEY, which bypasses network discovery entirely.
  • Clients acquire their tokens from the https://login.microsoftonline.us authority.

NOTE

The Yellowbrick Manager single sign-on wizard's Microsoft Entra AD provider type generates a commercial issuer host, which is wrong for a Government tenant. Either use the Microsoft Azure AD provider type, whose v1.0 issuer is the same in both clouds, or run the CREATE EXTERNAL AUTHENTICATION statement above by hand.