Categories:

System Functions (System Information)

SYSTEM$GET_LOGIN_FAILURE_DETAILS

Returns a JSON object that represents an unsuccessful login attempt associated with External OAuth, SAML, or key pair authentication. The JSON object contains the error associated with the failed login attempt.

Syntax

SYSTEM$GET_LOGIN_FAILURE_DETAILS('<uuid>')
Copy

Arguments

uuid

A string representing a UUID. The UUID appears after the error message that is returned from a failed login event associated with External OAuth, SAML, or key pair authentication.

Returns

Returns the following elements in a JSON object:

Key

Data Type

Value Description

clientIP

STRING

The IP address from where the failed login request originated. For example, "10.211.55.1".

clientType

STRING

The client software reported by the client. For example, "JDBC_DRIVER". This value is not verified. If the client does not report this value, then this value is "OTHER".

clientVersion

STRING

The version of the client software reported by the client. For example, "2.9.0". This value is not verified. If the client does not report this value, the this value is null.

username

STRING

The username associated with the failed login event. If the system cannot find the username, or the error occurred before the system found the username, then this value is null.

errorCode

STRING

The error associated with the failed login event. For a description of the error, refer to External OAuth errors, SAML errors, or JWT token errors. If the error is OVERFLOW_FAILURE_EVENTS_ELIDED, then the number of failed login attempts is too high.

timestamp

NUMBER

The date and time, in Unix timestamp format, when the failed login event occurred.

Usage notes

Only administrators that have a MONITOR privilege assigned to their role can use this function.

Error descriptions

This section provides descriptions for errors returned by the SYSTEM$GET_LOGIN_FAILURE_DETAILS function.

External OAuth errors

Error

Description

EXTERNAL_OAUTH_INVALID_SIGNATURE

Invalid signature algorithm or issue validating signature.

EXTERNAL_OAUTH_MISSING_ISSUER

Cannot extract issuer (an iss claim) from the access token.

EXTERNAL_OAUTH_JWS_INVALID_TYPE

Invalid type of access token.

EXTERNAL_OAUTH_JWS_INVALID_FORMAT

Malformed access token.

EXTERNAL_OAUTH_ACCESS_TOKEN_ISSUER_NOT_FOUND

Cannot find security integration associated with the issuer.

EXTERNAL_OAUTH_ACCESS_TOKEN_EXPIRED

Access token expired.

EXTERNAL_OAUTH_MISSING_AUDIENCE

Cannot extract audience (an aud claim) from the access token.

EXTERNAL_OAUTH_AUDIENCE_VALIDATION_FAILED

Audience of the access token does not match any of the audiences defined in the security integration.

EXTERNAL_OAUTH_ACCESS_TOKEN_ISSUER_NOT_ENABLED

Security integration is disabled.

EXTERNAL_OAUTH_JWS_CANT_RETRIEVE_PUBLIC_KEY

Cannot retrieve the public key from the authorization server to validate the access token.

EXTERNAL_OAUTH_USER_CLAIM_MISSING

Cannot extract user mapping claim from the access token.

EXTERNAL_OAUTH_ACCESS_TOKEN_NOT_YET_VALID

Token is not valid yet. A timestamp with a iat or nbf claim indicates the token is valid in the future.

SAML errors

Error Code

Error

Description

390133

SAML_RESPONSE_INVALID

The SAML response was invalid for an unspecified reason, although it is most likely malformed (this is also used if there is an error on parsing).

390165

SAML_RESPONSE_INVALID_SIGNATURE

The SAML response contains an invalid Signature.

390166

SAML_RESPONSE_INVALID_DIGEST_METHOD

The SAML response contains an invalid “DigestMethod” attribute or omits it entirely.

390167

SAML_RESPONSE_INVALID_SIGNATURE_METHOD

The SAML response contains an invalid “SignatureMethod” or omits it entirely.

390168

SAML_RESPONSE_INVALID_DESTINATION

The “Destination” attribute in the SAML response does not match a valid destination URL on the account.

390169

SAML_RESPONSE_INVALID_AUDIENCE

The SAML response does not contain exactly one audience or the audience URL does not match what we expect the audience URL to be.

390170

SAML_RESPONSE_INVALID_MISSING_INRESPONSETO

The “InResponseTo” attribute in the SAML assertion is missing.

390171

SAML_RESPONSE_INVALID_RECIPIENT_MISMATCH

The “Recipient” attribute does not match a valid destination URL.

390172

SAML_RESPONSE_INVALID_NOTONORAFTER_VALIDATION

This typically indicates that the time in which the SAML assertion is valid has expired.

390173

SAML_RESPONSE_INVALID_NOTBEFORE_VALIDATION

This typically indicates that the time in which the SAML assertion is valid has not yet come.

390174

SAML_RESPONSE_INVALID_USERNAMES_MISMATCH

The login names do not match during re-authentication.

390175

SAML_RESPONSE_INVALID_SESSIONID_MISSING

During re-authentication, we were unable to find a session corresponding to the user.

390176

SAML_RESPONSE_INVALID_ACCOUNTS_MISMATCH

During re-authentication, the names of the accounts were found to not match.

390177

SAML_RESPONSE_INVALID_BAD_CERT

The x.509 certificate contained in the SAML response is either malformed or does not match the expected certificate.

390178

SAML_RESPONSE_INVALID_PROOF_KEY_MISMATCH

The proof keys do not match with respect to the authentication request ID.

390179

SAML_RESPONSE_INVALID_INTEGRATION_MISCONFIGURATION

The SAML IdP configuration is invalid.

390180

SAML_RESPONSE_INVALID_REQUEST_PAYLOAD

During authentication, using an invalid payload or using an invalid federated OAuth connection string.

390181

SAML_RESPONSE_INVALID_MISSING_SUBJECT_CONFIRMATION_BEARER

The Subject confirmation with Bearer method is missing and cannot be validated.

390182

SAML_RESPONSE_INVALID_MISSING_SUBJECT_CONFIRMATION_DATA

The Subject confirmation data is missing in the assertion.

390183

SAML_RESPONSE_INVALID_CONDITIONS

The SAML assertion is not valid for a reason that is different than the preceding conditions in this table.

390184

SAML_RESPONSE_INVALID_ISSUER

The SAML Response contained an issuer/entityID value different from the one configured in the SAML IDP Configuration.

JWT token errors

The following errors are associated with the JWT token used for key pair authentication.

Error Code

Error

Description

390144

JWT_TOKEN_INVALID

There is a general issue with the JWT token. For possible solutions, see Common Errors and Solutions.

394300

JWT_TOKEN_INVALID_USER_IN_ISSUER

The user name specified in the issuer does not exist in the Snowflake account. For possible solutions, see Common Errors and Solutions.

394301

JWT_TOKEN_MISSING_ISSUE_OR_EXPIRATION_TIME

The JWT token does not contain an issue time or an expiration time.

394302

JWT_TOKEN_INVALID_ISSUE_TIME

The JWT token was received by Snowflake more than 60 seconds after the issue time. For possible solutions, see Common Errors and Solutions.

394303

JWT_TOKEN_INVALID_EXPIRATION_TIME

The JWT token is expired.

394304

JWT_TOKEN_INVALID_PUBLIC_KEY_FINGERPRINT_MISMATCH

There is a mismatch between the public key fingerprint specified in the issuer and the one stored for the user in Snowflake. For possible solutions, see Common Errors and Solutions.

394305

JWT_TOKEN_INVALID_ALGORITHM

The JWT token was not signed with the RS256 algorithm.

394306

JWT_TOKEN_INVALID_SIGNATURE

Snowflake could not verify the signature provided by the JWT token. It is possible that the JWT was signed with a private key that is not paired with the provided public key. It is also possible that the JWT signature is corrupt or has been modified.

Examples

The following example teaches you how to use the SYSTEM$GET_LOGIN_FAILURE_DETAILS function with a UUID from a failed login attempt associated with External OAuth, SAML, or key pair authentication:

  1. Find the UUID in the error message:

    Invalid  OAuth access token. [0ce9eb56-821d-4ca9-a774-04ae89a0cf5a]
    
  2. Use the UUID as an argument to the SYSTEM$GET_LOGIN_FAILURE_DETAILS function, and extract the error using the JSON_EXTRACT_PATH_TEXT function:

    SELECT JSON_EXTRACT_PATH_TEXT(SYSTEM$GET_LOGIN_FAILURE_DETAILS('0ce9eb56-821d-4ca9-a774-04ae89a0cf5a'), 'errorCode');
    
    Copy
  3. Find the error description in the External OAuth errors or SAML errors tables.