SAML error: invalid_grant: BBSTS50056: The user account {EmailHidden} does not exist in

 Issue:

we are using SSO on aws  Redshift database login and we got the below error and I tried to connect to Redshift using SSO and got the below error,but local accounts able to make connections to database.Then found the issue Related to SAML assertion.

Something unusual has occurred to cause the driver to fail. Please report this exception.

  SAML error: invalid_grant: BBSTS50056: The user account {EmailHidden} does not exist in the 45f81a76-3477-44dd-b547-fgb3b6a146543 directory. 

To sign into this application, the account must be added to the directory. Trace ID: 45dd7dae-8355-45bc-b4f9-43df120f888 Correlation ID: 1fed5c1a-7c4f-4ffc-b234-672d069c15c1 Timestamp: 2022-02-28 05:28:14Z 

 Impact:

unable to login to Redshift database using SSO

Root cause:

Then found the issue Related to SAML assertion. Users unable to connect to Redshift DB because  the respective  AD account has been deleted on Azure Active directory.

looks like the AD accounts marked as deleted on AZURE Active directory.

Solution

we asked the cloud admin to enable the AD accounts on Azure active directory and issue resolved.

script to find out which database actively used in AWS redshift

 To identify which databases are actively used, you can run this SQL command as a superuser.

SELECT database,

  COUNT(*) as num_queries,

  AVG(DATEDIFF(sec,starttime,endtime)) avg_duration,

  MIN(starttime) as oldest_ts,

  MAX(endtime) as latest_ts

FROM stl_query

WHERE userid > 1

GROUP BY database; 

script to find out owner of the schema in aws Redshift

 script to find out owner of the schema in aws Redshift:

select nspname as schema, usename as owner

from pg_catalog.pg_namespace, pg_catalog.pg_user

where pg_catalog.pg_namespace.nspowner = pg_catalog.pg_user.usesysid

and pg_catalog.pg_user.usename ='us_sales';

   schema |  owner

----------+----------

 us_sales | dwuser

(1 row)

script to list all schema in AWS redshift database

 script to list all schema in AWS redshift database:

select s.nspname as table_schema,

       s.oid as schema_id,  

       u.usename as owner

from pg_catalog.pg_namespace s

join pg_catalog.pg_user u on u.usesysid = s.nspowner

order by table_schema;