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.

Solution overview

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.

RedshiftAzureADIntegration1

The solution contains the following steps:

  1. You configure a JDBC or ODBC driver in SQL client to use Azure AD federation.
  2. The client makes an authentication request to the enterprise application in Azure using Azure AD credentials.
  3. The Azure enterprise application queries Azure AD and generates a SAML response, which includes the IAM roles assigned to the user.
  4. The client receives the SAML response.
  5. The SAML assertion goes to the AWS federation endpoint, which invokes the AssumeRoleWithSAML API of AWS STS and generates temporary IAM credentials.
  6. You use the temporary credentials to connect to the Amazon Redshift cluster.

Prerequisites

This blog post assumes that you have the following:

On Azure AD side:

On Amazon Redshift side:

Walkthrough overview

This walkthrough consists of the following three sections:

  1. Setting up the Azure enterprise non-gallery application using single sign-on (SSO) with SAML.
  2. 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.
  3. 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:

  1. Log in to Azure Portal.
  2. Choose Enterprise applications.RedshiftAzureADIntegration2
  3. Choose New application.
  4. Choose Non-gallery application.
  5. For Name, enter Redshift.
  6. Choose Add.RedshiftAzureADIntegration3
  7. Under Manage¸ choose Single sign-on.
  8. In the Basic SAML Configuration section, for Entity ID and Reply URL, enter https://signin.aws.amazon.com/saml.RedshiftAzureADIntegration4
  9. In the User Attributes & Claims section, choose Edit.
  10. For Additional claims, Add new claim with the following values (if they do not exist):
    • For , choose user.groups [SecurityGroup].
    • For , choose user.userprincipalname.
    • For , choose user.assignedroles
    • For , choose user.userprincipalname.
    • For , enter trueIIt 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.
      RedshiftAzureADIntegration5
  11. In addition to all of the claims added in previous step, Add new claim
    • For Manage claim, for Name, enter Role.
    • For Namespace, enter https://aws.amazon.com/SAML/Attributes/.
    • For Source, select Attribute.
    • For Source attribute, enter arn:aws:iam::<yourAWSAccount>:role/AzureSSO,arn:aws:iam::<yourAWSAccount>:saml-provider/AzureADProviderEEnsure “Roleclaim is configured properly. Make sure you change this to your own AWS account. We will setup role AzureSSO and AzureADProvider in AWS Identity Access Management.
      RedshiftAzureADIntegration6
  12. 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.
    RedshiftAzureADIntegration7
  13. 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.RedshiftAzureADIntegration8
  14. Record the Application (client) ID and Directory (tenant) ID.You use these values in the JDBC connection when you connect to Amazon Redshift.
    RedshiftAzureADIntegration9
  15. Under API permissions, choose Add a permission.
  16. Choose Microsoft Graph.
  17. Choose Delegated permissions
  18. For User, choose User.Read.
  19. Choose Add permission.This allows the Amazon Redshift enterprise application to grant admin consent to read user profile and perform login using SSO.
    RedshiftAzureADIntegration11
  20. Under Security, choose Permissions.
  21. Add Users/groups to the application or grant universal admin consent for the entire organization.RedshiftAzureADIntegration12
  22. Choose Certificates & secrets.
  23. Generate the client secret key.
  24. Record the client secret key to use when you configure the JDBC connection.RedshiftAzureADIntegration13

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 AzureADProvider
  • IAM role AzureSSO
  • IAM policy redshiftAccessPolicy, which you attach to the role AzureSSO to 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:

  1. Sign in to the AWS Management Console as an admin.
  2. Under Security, Identity, & Compliance, choose IAM.RedshiftAzureADIntegration14
  3. Choose Identity providers.RedshiftAzureADIntegration15
  4. Choose Create Provider.RedshiftAzureADIntegration16
  5. On the Configure Provider page, for Provider Type, choose SAML.
    • For Provider Name, enter AzureADProvider.
    • For Metadata Document, choose xml.RedshiftAzureADIntegration17
  6. Choose Next Step.
  7. 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:

  1. On the IAM console, choose Roles.RedshiftAzureADIntegration18
  2. Choose Create role.RedshiftAzureADIntegration19
  3. 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.RedshiftAzureADIntegration20
  4. 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.RedshiftAzureADIntegration21
  5. Choose Next: Permissions.You attach the policy to provide permission to Amazon Redshift after you create the policy.
  6. 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.
  7. Choose Next: Review
  8. On the Review page, for Role name, enter AzureSSO.
    • 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.RedshiftAzureADIntegration22
  9. 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.

  1. On the IAM console, choose Policies.RedshiftAzureADIntegration23
  2. Choose Create policy.
  3. On the Create policy page, choose JSON.RedshiftAzureADIntegration24
  4. For the policy, enter the following JSON:
    { "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>" ] } ]
    }

    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, cluster-1.
4<YOUR-ROLE-ID>The unique identifier of the role AzureSSO, which you create in the next section. You can enter the code aws iam get-role AzureSSO and use the RoleId value from the output. For more information, see IAM Identifiers.
5<YOUR-DB-GROUP>The database group that the user can join. For this post, use developer_grp.

 

  1. Choose Review policy.
  2. In the Review policy section, for Name, enter the name of your policy; for example, redshiftAccessPolicy.
  3. For Description, you can optionally enter a brief description of what the policy does.
  4. For Summary, review your policy components and make sure to resolve any warnings or errors.RedshiftAzureADIntegration25
  5. Choose Create policy.
  6. 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.

  1. On the Azure Portal, for your Amazon Redshift application, choose Single sign-on.
  2. Choose Test.RedshiftAzureADIntegration26
  3. 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 AzureSSO.

RedshiftAzureADIntegration27

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 1.2.37.1061 or later. To use Azure AD with ODBC, the Amazon Redshift ODBC driver must be version 1.4.10.1000 or later. For this walkthrough, use the driver with AWS SDK.

  1. Start SQLWorkbenchJ.
  2. On the Select Connection Profile page, choose Add Profile Group.
  3. 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.”RedshiftAzureADIntegration28
  4. For Driver, choose your Amazon Redshift driver.RedshiftAzureADIntegration29
  5. Next step is to construct JDBC URL and input it in “URL” field. You can do it by using the following pattern:jdbc:redshift:iam://<your-cluster-identifier-here>:<your-cluster-region-here>/<your-DB-name-here>RReplace <your-cluster-identifier-here>, <your-cluster-region-here>, and <your-DB-name-here> with the values matching your Amazon Redshift cluster. This post uses cluster-identifier, us-west-1, and dev.RedshiftAzureADIntegration30
  6. Choose Extended Properties.RedshiftAzureADIntegration31

Add the following key-value pairs tabulated in Extended Properties of SQLWorkbenchJ.

1plugin_nameTo use Azure AD federation, use com.amazon.redshift.plugin.AzureCredentialsProvider.
2idp_tenantThis 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.
3client_secretUse the client secret value that you created earlier.
4client_idThis is your client ID with hyphens of the Redshift application. Use the client ID that you recorded earlier.

RedshiftAzureADIntegration32

If you completed these steps correctly, you can connect to the Amazon Redshift cluster using your Azure AD credentials.

Summary

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

hspatelHarshida Patel is a Data Warehouse Specialist Solutions Architect with AWS.

 

 

 

 

PirAbdulPir 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.