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>')

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
394307	JWT_TOKEN_ACCOUNT_MISMATCH	The Snowflake account obtained from the token is not the same as the account in the request’s URL.
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:

Find the UUID in the error message:
```
Invalid  OAuth access token. [0ce9eb56-821d-4ca9-a774-04ae89a0cf5a]
```
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');
```
Find the error description in the External OAuth errors or SAML errors tables.