In this article, I would like to talk about Redshift AD federation using Azure Browser plugin [ For Microsoft Azure Active Directory SSO with MFA – com.amazon.redshift.plugin.BrowserAzureCredentialsProvider ]
https://aws.amazon.com/blogs/big-data/federate-amazon-redshift-access-with-microsoft-azure-ad-single-sign-on/ – Steps mentioned in this doc only helps with non-browser plugin usage.
Initially followed steps mentioned in above log, but it did not help to troubleshoot all issues and it also doesn’t list steps using browser based plugin.
Haven’t done below steps mentioned in above blog
- Didn’t generate client secret
- Didn’t do provisioning step or IAM user with list roles permission – you can just give IAM:listRoles permission on IAM role side and keep same name for AD group, AWS role and Redshift group, so you should be fine, you don’t need to do provisioning step.
For browser plugin SAML workflow, please check this doc – https://docs.aws.amazon.com/redshift/latest/mgmt/options-for-providing-iam-credentials.html
Note: JDBC client login with Azure federation doesn’t have easy way to manage Redshift DB group membership. Meaning, if your organization is mid to large scale and having so many AD groups/Redshift DB groups, then its recommended to manage just user login (sso part) using Azure federation and manage group membership on Redshift side (backend commands).
Cons of managing Redshift join group on AD / IAM role side:
- Need sync between AD and Redshift – Meaning, you can’t track by AD group, user got what all permissions. you need to check IAM role mapped to AD group for checking redshift DB group permissions. You need custom utility to sync data between AD and AWS for better visibility.
- Auditing difficult
- Redshift doesn’t show up group membership based on user log on and log off
- DBgroups parameter not supported from sql workbench.
- User can assume only 1 role for a given session, so you can’t get user assigned to multiple redshift db groups from different IAM Roles.
High level steps:
Step 1: Create non-gallery based Azure application/registration with below configuration. For Amazon browser based plugin, don’t need to generate client secret. Put this app under “Mobile and desktop applications”.
Required claims:
Claim Name : Value
https://aws.amazon.com/SAML/Attributes - user.assignedroles
https://aws.amazon.com/SAML/Attributes/RoleSessionName - user.userprincipalname
Unique user identifier (Name ID) - user.userprincipalname
https://aws.amazon.com/SAML/Attributes/SessionDuration - “900”Additional claims:
http://schemas.microsoft.com/ws/2008/06/identity/claims/groups - user.groups [ApplicationGroup]
https://redshift.amazon.com/SAML/Attributes/DbUser - user.userprincipalname
https://redshift.amazon.com/SAML/Attributes/DbGroups - <<Groups assigned to the application >> This can’t be complete ARN of role or SAML provider, it should be just normal group name.
https://redshift.amazon.com/SAML/Attributes/AutoCreate - “true”you will also have some default claims like below, as per your organization AD configuration.
email address
givenname
name
surname
Download xml metadata document from from Azure side, because you will need that during SAML provider creation.
Step 2: Create SAML provider in AWS account using above exported xml metadata.
Step 3: Create IAM role:
Key things when you create IAM Role,
- In trust relationship, you should use above created SAML provider
- Conditions: Don’t put any condition here, leave it blank.
Your trust relationship should look like this, use amazon docs for actual syntax.
{
Effect : Allow
Principal : {
Federated: “<<ARN of SAML provider >>
},
Action : “sts:AssumeRoleWithSAML”
}IAM policy:
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"redshift:DescribeClusters"
],
"Resource": [
"arn:aws:redshift:<YOUR-REGION>:<AWS-ACCOUNT-NUMBER>:cluster:<YOUR-REDSHIFT-CLUSTER-NAME>"
]
},
{
"Effect": "Allow",
"Action": [
"redshift:GetClusterCredentials"
],
"Resource": [
"arn:aws:redshift:<YOUR-REGION>:<AWS-ACCOUNT-NUMBER>:dbname:<YOUR-REDSHIFT-CLUSTER-NAME>/dev",
"arn:aws:redshift:<YOUR-REGION>:<AWS-ACCOUNT-NUMBER>:dbuser:<YOUR-REDSHIFT-CLUSTER-NAME>/${redshift:DbUser}"
],
"Condition": {
"StringEquals": {
"aws:userid": "<YOUR-ROLE-ID>:${redshift:DbUser}"
}
}
},
{
"Effect": "Allow",
"Action": [
"redshift:CreateClusterUser"
],
"Resource": [
"arn:aws:redshift:<YOUR-REGION>:<AWS-ACCOUNT-NUMBER>:dbuser:<YOUR-REDSHIFT-CLUSTER-NAME>/${redshift:DbUser}"
]
},
{
"Effect": "Allow",
"Action": [
"redshift:JoinGroup"
],
"Resource": [
"arn:aws:redshift:<YOUR-REGION>:<AWS-ACCOUNT-NUMBER>:dbgroup:<YOUR-REDSHIFT-CLUSTER-NAME>/<YOUR-DB-GROUP>"
]
}
]
}Step 4: Create AD groups and Redshift local DB groups:
- Keep AD group, redshift local DB group and IAM role all with same name.
Make sure, you do role assignment in your Azure app registration for each AD group / AWS role.
Go to Azure app registration, Users and Groups settings and define this role and group assignments.
Before you test your connection to AWS using client apps, you can also do testing in Azure using WEB URL. Use SAML Tracer to trace info when you do web based testing, because you will see same SAML response when you connect from SQL Client apps.
Step 5: SQL Workbench client configuration details:
- Download JDBC Driver: Downloaded redshift-jdbc42-2.0.0.1\redshift-jdbc42-2.0.0.1.jar
Note: Download and extract all files of this driver in same folder. Also define CLASSPATH system environment variables pointing to your redshift-jdbc42-2.0.0.1.jar
Classname : com.amazon.redshift.Driver
Create new connection with below details,
Driver: Amazon Redshift (com.amazon.redshift.Driver)
URL: jdbc:redshift:iam://clustername or cluster endpoint:5439/sampledb
username: user email
password: user password
click on extended properties and define below values:
Extended properties:
- LogLevel 6
- LogPath - C:\yourpath
- plugin_name - com.amazon.redshift.plugin.BrowserAzureCredentialsProvider
- DSILogLevel - 6
- listen_port - 7890
- idp_tenant - << Get this id from your azure app registration >>
- client_id - << Get this id from your azure app registration >>Additional hints: You can also modify sqlworkbench.cmd file
Go to below section and add your organization proxy details, so while launch it will have that proxy enabled.
:gui
start “SQL Workbench/J” — you can add your proxy lines after -DSun.java2d config lines.
Troubleshooting section:
Driver logs – [JDBC Driver] SAML Error: invalid_grant: Error validating credentials due to invalid username or password
SQL Workbench reported error: [JDBC Driver] SAML error: invalid_grant: The use account XXX does not exist in the << azure AD >> directory. To sign into the this application, the account must be added to the directory
Something unusual has occurred to cause the driver to fail. Please report this exception [SQL State=99999]
Solution for above errors:
- Define your claims properly as mentioned in above steps.
- Change application category from “Web” to “Mobile and desktop applications”
- Don’t need to use client secret
- Added additional claims called ‘User’
ERROR [29 ConnectionTest] com.amazon.redshift.Driver.connect:com.amazonaws.SdkclientException: unable to execute HTTP request: established connection was aborted by the software in your host machine.
Solution for above error: Set proxy on your local machine. Refer sql workbench additional hint section.
Error [26 connection test] com.amazon.redshift.Driver.connect:com.amazonaws.services.securitytoken.model.InvalidIdentityTokenException: Invalid base64 SAMLResponse (Service: AWSOpenIdDiscoveryService; Status code: 400 Error code: AuthSamlInvalidSamlResponseException
Solution for above error: Correction of claim Attributes on Azure AD side
Error [36 Connection test] com.amazon.redshift.Driver.connect: com.amazonaws.services.securitytoken.model.AWSSecurityTokenServiceException: Not authorized to perform sts:AssumeRoleWithSAML (Service: AWSSecurityTokenService; Status code: 403; Error code: Access Denied)
Solution for above error: Remove condition from your IAM role.
IAM error retrieving temp credential: Each group name in DBGroups parameter list must be lowercase (Service: AmazonRedshift; status code: 400 Error code: InvalidParameterValue
Solution for above error: Your DBgroup shouldn’t have ARN listed.
Troubleshooting Amazon Redshift JDBC driver:
You can enable logging at driver level, see below parameters. Define these parameters in sql workbench extended properties, so you can check driver logs for more details.
DSILoglevel = 6
LogLevel = 2
LogPath = any path where you want to save the logs
Usage of SAML-tracer, Postman or RestMan plugins:
- When you test your login using Azure login URL, you can use SAML-tracer for checking SAML response.
- Used Restman or postman to check end to end SAML workflow and to debug above issues.
Steps for debugging issues using postman or Restman,
Put all debugging log level parameters in SQL Workbench extended properties. Try login using SQL Workbench, it will log everything in driver logs.
When you use browser based plugin to get CODE and Token using /oauth2/authorize and /oauth2/token, it sends below parameters.
Requested parameters,
CODE
requested_token_type
scope=openid
grant_type=authorization_code
resource
client_id
redirect_uri=http://localhost:7890/redshift/Get the CODE from driver log and supply all key/values in postman or Restman to trace internals. This code will be unique and change everytime.
For Azure AD authentication, it follows browser plugin workflow mentioned in this link https://docs.aws.amazon.com/redshift/latest/mgmt/options-for-providing-iam-credentials.html
Please provide your feedback.. Happy Reading….
Prashant Atri Blog 