Federate Amazon Redshift access with Microsoft Azure AD single sign-on
Recently, we helped a large enterprise customer who was building their data warehouse on Amazon Redshift, using Azure AD as a corporate directory. Their requirement was to enable data warehouse users to use their corporate credentials to query data in Redshift. Doing so not only provided a better user experience — users can utilize their corporate credentials and do not have to remember extra passwords – but it also it made maintenance easier, because all corporate users are managed in a single place. The solution was to set up Azure AD federated access to Redshift.
You can use federation to centrally manage access to Amazon Redshift. This simplifies administration by allowing you to control user access at a central location and reducing the overhead of creating and maintaining database users. In addition to Active Directory Federation Service (ADFS), PingFederate, and Okta, Amazon Redshift also supports Microsoft Azure Active Directory (Azure AD) federation.
For more information about using ADFS with Amazon Redshift, see Federate Database User Authentication Easily with IAM and Amazon Redshift. For more information about integrating Azure AD, see Setting Up JDBC or ODBC Single Sign-on Authentication with Microsoft Azure AD.
This post illustrates how to set up federation using Azure AD and IAM. Azure AD manages the users and provides federated access to Amazon Redshift using IAM. You do not need to create separate database users in Amazon Redshift with this setup.
The solution to configure the federation between Azure AD and IAM to allow seamless access to Amazon Redshift using an SQL client contains the following components:
- Azure AD serves as the identity provider for user authentication.
- AWS serves as the service provider for authorization.
- Security Assertion Markup Language (SAML 2.0) simplifies the interaction between identity provider and service provider.
- AWS STS requests temporary credentials to connect to Amazon Redshift.
The following diagram illustrates the architecture of the solution.
The solution contains the following steps:
- You configure a JDBC or ODBC driver in SQL client to use Azure AD federation.
- The client makes an authentication request to the enterprise application in Azure using Azure AD credentials.
- The Azure enterprise application queries Azure AD and generates a SAML response, which includes the IAM roles assigned to the user.
- The client receives the SAML response.
- The SAML assertion goes to the AWS federation endpoint, which invokes the
AssumeRoleWithSAMLAPI of AWS STS and generates temporary IAM credentials.
- You use the temporary credentials to connect to the Amazon Redshift cluster.
This blog post assumes that you have the following:
On Azure AD side:
- You have Azure AD membership.
- You have a user “test” and is member of group called “developer_grp” in Azure AD. User test is part of the business intelligence team (For reference, the Create Azure users and groups in Azure Active Directory module walks through an example).
On Amazon Redshift side:
- You have an Amazon Redshift cluster, this Create a sample Amazon Redshift cluster documentation walks you through launching one.
- You have already created a database group named “developer_grp” in Amazon Redshift. A database group can be created by using the “Create Group” statement.
This walkthrough consists of the following three sections:
- Setting up the Azure enterprise non-gallery application using single sign-on (SSO) with SAML.
- Setting up the IAM provider and roles, which includes the following steps:
- Creating the SAML identity provider.
- Creating an IAM role for access to the Amazon Redshift cluster.
- Creating an IAM provider and an IAM role to use SAML-based federation.
- Testing the SSO setup.
- Configuring the JDBC client to use Azure AD user credentials to log in to the Amazon Redshift cluster. This post uses a JDBC client. However, you can use the same setup to support ODBC clients.
Setting Up Azure Enterprise Application with SAML single sign on
To set up the Azure enterprise application, complete the following steps:
- Log in to Azure Portal.
- Choose Enterprise applications.
- Choose New application.
- Choose Non-gallery application.
- For Name, enter
- Choose Add.
- Under Manage¸ choose Single sign-on.
- In the Basic SAML Configuration section, for Entity ID and Reply URL, enter
- In the User Attributes & Claims section, choose Edit.
- For Additional claims, Add new claim with the following values (if they do not exist):
https://redshift.amazon.com/SAML/Attributes/AutoCreate, enter “
true” IIt is noteworthy to mention here that value of “DbUser” and “DbGroups” must be lowercase, begin with a letter, contain only alphanumeric characters, underscore (‘_’), plus sign (‘+’), dot (‘.’), at (‘@’), or hyphen (‘-‘), and be less than 128 characters.
- In addition to all of the claims added in previous step, Add new claim
- For Manage claim, for Name, enter
- For Namespace, enter
- For Source, select Attribute.
- For Source attribute, enter
arn:aws:iam::<yourAWSAccount>:role/AzureSSO,arn:aws:iam::<yourAWSAccount>:saml-provider/AzureADProviderEEnsure “Role” claim is configured properly. Make sure you change this to your own AWS account. We will setup role
AzureADProviderin AWS Identity Access Management.
- For Manage claim, for Name, enter
- In the Single sign-on section, under SAML Signing Certificate, for Federation Metadata XML, choose Download. Save this file locally.Often, the name of the file is the same as the application name; for example, Redshift.xml. You use this file to configure the IAM identity provider in the next section. This file will be used to configure IAM Identity Provider in next section.
- On the App registrations page, choose the application Redshift. If you don’t see your application in the list, choose All apps from the drop-down menu and search for it. Register it if it is not registered.
- Record the Application (client) ID and Directory (tenant) ID.You use these values in the JDBC connection when you connect to Amazon Redshift.
- Under API permissions, choose Add a permission.
- Choose Microsoft Graph.
- Choose Delegated permissions
- For User, choose User.Read.
- Choose Add permission.This allows the Amazon Redshift enterprise application to grant admin consent to read user profile and perform login using SSO.
- Under Security, choose Permissions.
- Add Users/groups to the application or grant universal admin consent for the entire organization.
- Choose Certificates & secrets.
- Generate the client secret key.
- Record the client secret key to use when you configure the JDBC connection.
You have now set up the enterprise application in Azure. You are ready to set up the IAM provider and role to use this Azure federation.
Setting Up IAM Identity Provider and Roles in AWS
An IAM identity provider is an entity in IAM that describes an external identity provider (IdP) service that supports the SAML 2.0 standard. You use an IAM identity provider when you want to establish trust between an SAML-compatible IdP; for example, Azure AD. It is important to name this identity provider
AzureADProvider to match the SAML claims you created earlier.
For this walkthrough, you create the following in IAM:
- IAM SAML identity provider
- IAM role
- IAM policy
redshiftAccessPolicy, which you attach to the role
AzureSSOto provide permission to access the Amazon Redshift cluster
Creating the IAM SAML identity provider
To create your IAM SAML identity provider, complete the following steps:
- Sign in to the AWS Management Console as an admin.
- Under Security, Identity, & Compliance, choose IAM.
- Choose Identity providers.
- Choose Create Provider.
- On the Configure Provider page, for Provider Type, choose SAML.
- For Provider Name, enter
- For Metadata Document, choose xml.
- For Provider Name, enter
- Choose Next Step.
- Choose Create.
Creating the IAM role for access to the Amazon Redshift cluster
You now create a role for SAML-based federation. The name of this role needs to match what you named the SAML claim you created earlier:
AzureSSO. Complete the following steps:
- On the IAM console, choose Roles.
- Choose Create role.
- You will be directed to the Create role page where you can choose from several different options. To setup the Azure AD authentication on the Create role page, choose SAML 2.0 federation.
- Under Choose a SAML 2.0 provider:
- For SAML provider, choose AzureADProvider from the drop down.
- Select Allow programmatic and AWS Management Console access.
- For Attribute, keep at the default SAML:aud.
- For Value, keep at the default https://signin.aws.amazon.com/saml.
- For Condition, you can optionally add additional conditions of who can use SSO. For this walkthrough, do not add any conditions.
- Choose Next: Permissions.You attach the policy to provide permission to Amazon Redshift after you create the policy.
- Choose Next: Tags. For Add tags (optional), you can optionally add key-value tags to help better organize, track, or control access for this role. For this post, do not add any tags.
- Choose Next: Review
- On the Review page, for Role name, enter
- For Role description, you can optionally enter a brief description of what your role does.
- For Trusted entities, verify the ARN of the provider you specified earlier is correct.
- Choose Create role.
Creating the IAM Amazon Redshift access policy
In the following steps, you allow the role
AzureSSO to generate an AWS STS token to connect to Amazon Redshift by adding an IAM permissions policy
redshiftAccessPolicy to the role.
- On the IAM console, choose Policies.
- Choose Create policy.
- On the Create policy page, choose JSON.
- For the policy, enter the following JSON:
In the preceding code, replace the placeholders with the appropriate values. The following table provides an explanation of each placeholder.
|1||<YOUR-REGION>||The Region hosting your solution.|
|2||<AWS-ACCOUNT-NUMBER>||Your AWS account.|
|3||<YOUR-REDSHIFT-CLUSTER-NAME>||Your Amazon Redshift cluster name; for example,
|4||<YOUR-ROLE-ID>||The unique identifier of the role
|5||<YOUR-DB-GROUP>||The database group that the user can join. For this post, use
- Choose Review policy.
- In the Review policy section, for Name, enter the name of your policy; for example,
- For Description, you can optionally enter a brief description of what the policy does.
- For Summary, review your policy components and make sure to resolve any warnings or errors.
- Choose Create policy.
- Add this policy to the IAM role
AzureSSO. For instructions, see Adding IAM Identity Permissions (Console).
- Choose Managed IAM Policy.
Testing the SSO setup
You can now test the SSO setup. Complete the following steps.
- On the Azure Portal, for your Amazon Redshift application, choose Single sign-on.
- Choose Test.
- Choose Sign in as current user.
If the setup is correct, this brings you to the console (may be in next Tab for some browsers). The following screenshot shows that the user
test is signed in using the assumed role
After you verify the SSO setup, you are ready to connect the SQL client to Amazon Redshift using Azure AD federation.
Setting up your JDBC SQL client to use Azure AD federation
You can use any client tool that supports an Amazon Redshift JDBC or ODBC driver to connect to Amazon Redshift using Azure SSO. For this post, use the SQLWorkbenchJ client to connect to Amazon Redshift using JDBC. For instructions in installing SQLWorkbench/J, see Connect to Your Cluster by Using SQL Workbench/J.
To connect to the Amazon Redshift cluster using your Azure AD credentials, complete the following steps. If you are using another client, you can pass all these parameters in the JDBC URL as a URL parameter.
To use Azure AD with JDBC, the Amazon Redshift JDBC driver must be version 126.96.36.1991 or later. To use Azure AD with ODBC, the Amazon Redshift ODBC driver must be version 188.8.131.520 or later. For this walkthrough, use the driver with AWS SDK.
- Start SQLWorkbenchJ.
- On the Select Connection Profile page, choose Add Profile Group.
- Click on the “New Connection Profile.” This will open a new profile in the profile group you created. You can choose an appropriate name — we named it “Azure.”
- For Driver, choose your Amazon Redshift driver.
- Next step is to construct JDBC URL and input it in “URL” field. You can do it by using the following pattern:
<your-DB-name-here>with the values matching your Amazon Redshift cluster. This post uses
- Choose Extended Properties.
Add the following key-value pairs tabulated in Extended Properties of SQLWorkbenchJ.
|1||plugin_name||To use Azure AD federation, use
|2||idp_tenant||This is the tenant name of your company configured on your IdP (Azure). This value can either be the tenant name or the tenant unique ID with hyphens. For this walkthrough, use tenant ID that you recorded earlier.|
|3||client_secret||Use the client secret value that you created earlier.|
|4||client_id||This is your client ID with hyphens of the Redshift application. Use the client ID that you recorded earlier.|
If you completed these steps correctly, you can connect to the Amazon Redshift cluster using your Azure AD credentials.
In this post, we provided a step-by-step guide to configure and use Azure AD as your IdP to enable federation to an Amazon Redshift cluster.
About the Authors
Harshida Patel is a Data Warehouse Specialist Solutions Architect with AWS.
Pir Abdul Rasool Qureshi is a Solutions Architect at AWS. He enjoys solving complex customer problems in Databases & Analytics and deliver successful outcomes. Outside of work, he loves to spend time with his family, watch movies and play cricket whenever possible.