Federate access to your Amazon Redshift cluster with Active Directory Federation Services (AD FS): Part 1

This blog post was reviewed and updated May 2022, to include and comply with recently published Part 3 from this series.

Many customers request detailed steps to set up federated single sign-on (SSO) using Microsoft Active Directory Federation Services (AD FS) for Amazon Redshift. In this two-part series, you will find detailed steps to achieve federated SSO using AD FS.

Part 1 contains steps to set up a Windows 2016 domain controller and AD FS and test SSO to the AWS Management Console using a web browser.

Part 2 provides detailed steps to use the AD FS server you set up in Part1 to authenticate from SQL Workbench/J using a JDBC connection.

Part 3 provides detailed steps to use the AD FS server you set up in Part1 to authenticate from Amazon Redshift Query Editor V2.

Amazon Redshift supports user sign-on using federated SSO through identity providers compliant with SAML 2.0 (Security Assertion Markup Language) like AD FS, Ping Federate, and Okta. You can manage users and roles centrally for all your applications using an enterprise identity provider (IdP) and set up Amazon Redshift to use IdP authentication and assume roles at the time of login.

For more information about designing and enabling federation with Amazon Redshift at a high level, see Federate Database User Authentication Easily with IAM and Amazon Redshift. This series takes that approach further and dives deep into the details of setup and testing.

Overview of solution

This post contains detailed steps to set up a Windows 2016 domain controller using an Amazon Elastic Compute Cloud (Amazon EC2) instance, Active Directory Federation Service (AD FS) as IdP, and federated database user authentication with AWS Identity and Access Management (IAM) and Amazon Redshift. You create users and groups within your organization’s Active Directory and establish federated authentication using AD FS to the console using a web browser.

The following diagram illustrates the solution’s architecture.

The workflow includes the following steps:

1. A user browses to the IdP.
2. The IdP authenticates the user and returns a SAML assertion.
3. The client browser posts the SAML assertion to an AWS SAML endpoint.
4. The endpoint validates the assertion and sends back a redirect.
5. The client browser is sent to the console to sign in.

Walkthrough

In this post, you go through the following steps:

1. Set up an Active Directory (AD) domain controller using an AWS CloudFormation template on a Windows 2016 EC2 instance.
2. Configure federation in AD FS.
3. Configure AWS as the relying party with AD FS using an IAM SAML provider and SAML roles with an attached policy to allow access to the Amazon Redshift cluster.
4. Configure claim rules.
5. Test the SAML authentication using a web browser.

Prerequisites

For this walkthrough, you should have the following prerequisites:

• An AWS account.
• AWS permissions to provision Amazon EC2 instances and default VPC, deploy AWS CloudFormation templates, and create an IAM role and policy.
• For the purposes of this post, this could be a dev or test environment. Check with your administrator to ensure that you have access to these prerequisites.
• The ability to access remotely using Remote Desktop Protocol (RDP) for the Windows 2016 EC2 instance.
• Some familiarity with working on an Microsoft Windows environment, such as running MS PowerShell to follow along with this post.

Setting up the domain controller and AD FS

To set up your domain controller and AD FS, complete the following steps:

1. Launch the following CloudFormation template:This template provisions the EC2 instance and sets up a domain controller.

2. Enter a name for the stack, such as ADFS2016Redshift.
3. Provide the following parameters:
   • Amild – Keep as default
   • DomainDNSName – adfsredshift.com
   • DomainNetBIOSName – adfsredshift
   • InstanceType – Keep as default (m5.large)
   • KeyName: The .pem key pair in the Region for your IAM user.For instructions on creating a key pair, see Creating or importing a key pair.
   • RestoreModePassword – Enter a strong and memorable password of your choice
   • SourceCidrForRDP – Open (you restrict it through your security group later)

For production systems, it’s highly advised to change SourceCidrForRDP and restrict to only the IP range you want to allow access from. The format for the IP entry is similar to xxx.xxx.xxx.xxx/32 for a single IPv4 address.

4. Choose Next.
5. Select the check-box acknowledging that the template might cause AWS CloudFormation to create IAM resources on your behalf.
6. Choose Next.
7. Choose Create stack.

On the dashboard, you should see the status IN PROGRESS. You can see the template’s progress on the Events tab. When the deployment is successful, the status changes to CREATE_COMPLETE.

8. On the Amazon EC2 console, choose the Windows 2016 EC2 instance created by Cloudformation template.
9. In the description below, click on the security group, which will open it up.
10. From the Actions drop-down menu, choose Edit inbound rules.

11. Edit the inbound rules to restrict the IP range for RDP access and add a rule for HTTPS using your own IP range.

To find your IP in the rule, for Source, choose MyIP from the drop-down menu.

12. Choose Save.

13. After updating security group, go back to the Windows 2016 EC2 instance, from the Actions drop-down menu, choose Get Windows Password using the key pair you used when you launched the instance.

For more information, see Connect to your Windows Instance. You use the generated password for the RDP session to the domain controller instance later.

14. Use an RDP client from your desktop to connect the EC2 instance using the administrator user and password from previous step.
15. Create test users and groups in AD using the Windows command prompt by entering the following code (this walkthrough creates the user Bob, which you use again in Part 2):

dsadd user "cn=Bob,cn=Users,dc=adfsredshift,dc=com" -samid bob -upn bob@adfsredshift.com -email bob@adfsredshift.com -fn Bob -ln Smith -display "Bob Smith" -disabled no -pwd "YourOwnStrongPassword"

dsadd group "cn=AWS-Production, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

dsadd group "cn=AWS-Dev, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

dsadd user "cn=ADFSSVC,cn=Users,dc=adfsredshift,dc=com" -display "ADFS Service" -disabled no -pwd " YourOwnStrongPassword “

dsadd group "cn=RSDB-marketing, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

You can also use the Active Directory Users and Computers graphical user interface to create these users and groups.

After you run the preceding commands successfully, you can see the users and groups as in the following screenshot.

16. On the same Windows 2016 EC2 instance, install the AD FS 2016 role using PowerShell:

PS C:\Users\Administrator> Install-windowsfeature adfs-federation –IncludeManagementTools

For more information, see Install the AD FS Role Service.

Federation servers use a server authentication certificate, such as the Secure Sockets Layer (SSL) certificate in Internet Information Services (IIS). For more information, see Certificate Requirements for Federation Servers.

For testing purposes, you use a self-signed certificate, but as a recommended best practice, self-signed SSL certificates aren’t recommended for production environments.

17. To create the self-signed certificate, use PowerShell to install and configure the IIS:

PS C:\Users\Administrator> Install-WindowsFeature -name Web-Server -IncludeManagementTools

After IIS installation is successful, you use it to create a self-signed certificate.

18. In the Amazon EC2 Windows server, under Administrative Tools, open IIS.
19. Choose the IIS server.
20. Choose Server Certificates.
21. Under Actions, choose Create Self-Signed Certificate.

22. Enter a name, such as AD FSCertificate.
23. Choose Ok.

You’ve now created a certificate that’s ready to use.

Configuring federation in AD FS

To configure federation, complete the following steps:

1. On the Windows 2016 EC2 instance, open Server Manager.
2. Choose the flag icon.
3. Choose Configure the federation server on this server.

You can ignore the warning message to promote this server to domain controller. This doesn’t interfere with the setup because as the AD FS is working at this point and the server is already a domain controller.

4. Select Create the first federation server in a federation server farm.
5. Choose Next
6. On the SSL Certificate drop-down menu, choose the self-signed certificate you created during IIS setup.

The Federation Service Name gets auto-populated.

7. For Federation Service Display Name, enter ADFSRedshiftDemo.
8. Choose Next.
9. Confirm the names to ensure that it’s valid.
10. Choose Ok.

11. Enter the password for the service account – ADFSSVC that you created earlier, in previous section’s step 15.
12. Choose Next.

13. Choose Next two more times.
14. Review and run the prerequisite checks and choose Configure.

You can see the installation progress and AD FS installation starts.

You may receive a warning message, but you can ignore it for this walkthrough. You also need to restart the Windows 2016 EC2 instance.

15. After you RDP back into the Windows 2016 EC2 instance, download the federation metadata from AD FS.
    • To capture the hostname, under Windows, Administrative Tools, choose AD FS Manager.
    • Choose Edit Federation Service Properties.
    • Copy the federation service name, for example <<YourInstanceHostname.adfsredshift.com>>.
    • Install Google Chrome on the EC2 instance via RDP by entering the following code in the Windows PowerShell command prompt:

$LocalTempDir = $env:TEMP; $ChromeInstaller = "ChromeInstaller.exe"; (new-object System.Net.WebClient).DownloadFile('http://dl.google.com/chrome/install/375.126/chrome_installer.exe', "$LocalTempDir\$ChromeInstaller"); & "$LocalTempDir\$ChromeInstaller" /silent /install; $Process2Monitor =  "ChromeInstaller"; Do { $ProcessesFound = Get-Process | ?{$Process2Monitor -contains $_.Name} | Select-Object -ExpandProperty Name; If ($ProcessesFound) { "Still running: $($ProcessesFound -join ', ')" | Write-Host; Start-Sleep -Seconds 2 } else { rm "$LocalTempDir\$ChromeInstaller" -ErrorAction SilentlyContinue -Verbose } } Until (!$ProcessesFound)

Internet Explorer on the EC2 instance generated by the CloudFormation template is, by default, in Enhanced Security configuration, so may not be available to use.

You may choose to install any other browser or disable Enhanced Security for Internet Explorer.

1. • From Google Chrome, enter the following URL with the hostname from the previous step:

https://<<YourInstanceHostname.adfsredshift.com>>/FederationMetadata/2007-06/FederationMetadata.xml

If you receive an error message, choose the advance icon and then choose Proceed to… to download the file.

1. • Copy the FederationMetadata.xml file from the RDP download location and save it to your local desktop to use later.

Configuring AWS as the relying party with AD FS

To configure AWS as the relying party, you first need to create the SAML provider and federation role, and create and attach IAM policies.

Creating the SAML provider

To create the SAML provider, complete the following steps:

1. On the IAM console, choose Identity Provider.
2. Choose Create provider.
3. For Provider type, choose SAML.
4. For Provider name, enter ADFZ.
5. For Choose File, choose the FederationMetadata.xml you downloaded earlier.
6. Choose Create.

You should see the provider as in the following screenshot.

You should continue to use the name ADFZ for the SAML IdP because this name carries over in other parts of the post and is referred to in the claim rules.

Creating a SAML federation role

You can now create a SAML federation role.

1. On the IAM console, choose Roles
2. Choose Create role.
3. Choose SAML 2.0 Federation.
4. For SAML provider, enter ADFZ.
5. For Attribute, choose SAML:aud.
6. For Value, enter https://signin.thinkwithwp.com/saml.

7. Choose Next.
8. Choose any permissions you need for the role (for this walkthrough, choose AmazonRedshiftFullAccess).

In a production environment, it’s advised to use a role with restricted access as required.

9. Choose Next twice.
10. For Role name, choose ADFZ-Dev.

11. Repeat the previous steps to create another role called ADFZ-Production.

Creating an IAM policy

For the ADFZ-Production role, you now create an IAM policy.

1. On the IAM console, choose Policies.
2. Choose Create policy.

3. On the JSON tab, enter the following policy:
   • The GetClusterCredentials Redshift API with AWS credentials to get the temporary database credentials. This API creates the user and adds it to a specified DB group, so you also need to provide redshift:CreateClusterUser and redshift:JoinGroup permissions to the IAM role. See the following code:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift:ListSchemas",
                "redshift:DescribeQuery",
                "redshift:ListDatabases",
                "redshift:DescribeClusters",
                "redshift:ExecuteQuery",
                "redshift:GetClusterCredentials",
                "redshift:FetchResults",
                "redshift:DescribeTable",
                "redshift:CreateClusterUser",
                "redshift:ListTables",
                "redshift:CancelQuery",
                "tag:GetResources"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": "redshift:JoinGroup",
            "Resource": "*"
        }
    ]
}

4. Choose Review policy.Ideally, development and production IAM roles are in two different accounts. The Resource section here is set to be open with *, but in a production environment, you should specify the exact resource you want to control with the policy. Also, add a condition block to your policy as mentioned here. The Condition block enforces that the AWS user ID should match “unique-role-identifier:${redshift:DbUser}”, so that individual users can authenticate only as themselves.

5. Enter a policy name, for example redshift-marketing.
6. Choose Create policy.
7. Return to the ADFZ-Production role.
8. Choose Attach policies.
9. Choose the policy you created.

Configuring AWS as the relying party

To complete configuring AWS as the relying party, go back to RDP on Windows 2016 EC2 instance.

1. In the AD FS management application, choose Add Relying Party Trust.

2. Choose Claims aware.
3. Choose Start.
4. On the next page, for Data Source, enter the standard URL as https://signin.thinkwithwp.com/static/saml-metadata.xml for importing data about the relying party published online or on a local network option.
5. Choose Next.

6. Enter a display name, for example, Amazon Web Services.
7. Choose Next.
8. For Access Control Policy, choose Permit everyone.
9. Choose Next with default choices until you see the Finish page.
10. Choose Close.

Configuring claim rules

AD FS uses a claims-based identity model and issues a token that contains a set of claims as part of its function as federation services. Claim rules govern the decision with regards to claims that AD FS issues. Claim rules and all server configuration data are stored in the AD FS configuration database. AD FS makes issuance decisions that are based on claims and other contextual information by taking one set of claims as input, performing transformations, and returning a different set of claims as output.

For this walkthrough, you set up two standard claim rules and three custom claim rules.

Configuring standard claim rules

After you choose Close in the previous step, you are redirected to a window to define the claim rules. If this doesn’t happen, you can return to the AD FS management console on the Windows 2016 EC2 instance, choose Relying Party Trusts, and choose Edit Claim Issuance Policy. To add a rule for NameID, complete the following steps:

1. Choose Choose Rule Type.
2. For the claim rule template, choose Transform an Incoming Claim.
3. Choose Next.
4. For Claim rule name, enter NameId.
5. For Incoming claim type, choose Windows account name.
6. For Outgoing claim type, enter Name ID.
7. For Outgoing Name ID format, choose Persistent identifier.
8. Select Pass through all claim values.
9. Choose Finish.

You now add another rule for RoleSessionName.

1. Choose Choose Rule Type.
2. For Claim rule template, choose Send LDAP Attributes as Claims.
3. For Claim rule name, enter RoleSessionName.
4. For Attribute store, choose Active Directory.
5. For LDAP Attribute, choose E-Mail Addresses.
6. For Outgoing Claim Type, enter https://thinkwithwp.com/SAML/Attributes/RoleSessionName.
7. Choose Finish.

Configuring custom claim rules

By using a custom claim rule, you can create rules with more complex logic than a standard rule template. You use custom rules to send claims based on values that are extracted from a custom attribute store for AD groups, DB groups, and roles.

To add a custom rule for your AD group, complete the following steps:

1. Choose Choose Rule Type.
2. For Claim rule template, choose Send Claims Using a Custom Rule.
3. Choose Next.
4. For Claims rule name, enter Get AD Groups.
5. Add the following custom rule:

c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname", Issuer == "AD AUTHORITY"] => add(store = "Active Directory", types = ("http://temp/variable"), query = ";tokenGroups;{0}", param = c.Value);

6. Choose Finish.

To add a custom rule for roles, you replace the string with your account ID using the temporary variable you created earlier. This step takes all the groups from the temporary variable from previous step and replaces AWS- with an Amazon Resource Name (ARN) with your account ID and the prefix for the roles created in AWS. The list is then sent as the path in the following code.

7. Follow the previous steps to create the rule Roles.
8. Enter the following custom rule, replacing your own account ID and role prefix:

c:[Type == "http://temp/variable", Value =~ "(?i)^AWS-"] => issue(Type = "https://thinkwithwp.com/SAML/Attributes/Role", Value = RegExReplace(c.Value, "AWS-", "arn:aws:iam::<AWSAccountID>:saml-provider/ADFZ,arn:aws:iam::<AWSAccountID>:role/ADFZ-"));

You now add a third custom rule for the database group DBGroups mapping of an AD group RSDB-marketing to a database group marketing, which you create later in Amazon Redshift in Part2.

9. Follow the previous steps to create the rule DBGroups.
10. Enter the following custom rule:

c:[Type == "http://temp/variable", Value =~ "(?i)^RSDB-"]=> issue(Type = "https://redshift.amazon.com/SAML/Attributes/DbGroups", Value = RegExReplace(c.Value, "RSDB-", ""));

11. Choose Finish.

In this claim rule, you take an AD group starting with the name RSDB- and remove the prefix. The remaining string (marketing), matches the database group name. This allows the user after SAML authentication to join the database group with same name, which is only valid during the session window. This allows the user to access the resources allowed by the database group marketing, which is defined at the database level.

12. Confirm the five rules are correct and choose Ok.

Testing SAML federation using a web browser

Before starting your test, run the following code using PowerShell:

Set-AdfsProperties -EnableIdpInitiatedSignonPage $true

EnableIdpInitiatedSignonPage= False is set by default, and it has to be turned on for the test. You can validate EnableIdpInitiatedSignonPage is set to True by entering the following code:

Get-AdfsProperties

For more information, see AD FS Troubleshooting – Idp-Initiated Sign On.

To test your federation, complete the following steps:

1. Open Google Chrome on Windows Server 2016 through RDP.
2. Enter the following link: https://<<YourInstanceHostname.adfsredshift.com>>/adfs/ls/IdpInitiatedSignOn.aspx.
3. Sign in as bob@adfsredshift.com and use the password set for the AD user Bob.
4. For Select a role, select ADFZ-Dev.
5. Choose Sign in.

The Dev role has administrator access, so you can verify the authenticated user on the console.

Cleaning up

If you want to stop here (without continuing to Part 2) and avoid incurring future charges, delete the CloudFormation stack on the console. If you want to set up Amazon Redshift federation, wait to complete the cleanup steps in Part 2.

Conclusion

In this post, you learned to spin up a Windows 2016 Amazon EC2 instance to simulate an on-premises domain controller and AD FS setup process. The groups and users you created in Active Directory running on Windows 2016 were federated with SAML using AWS as the relying party. When you initiated the sign-in URL from your browser using the user ID you created in Active directory, the browser communicated with AD FS as IdP to authenticate the user ID. Then the web browser communicated to AWS Security Token Service (AWS STS), which returned temporary user credentials for console access. In the web browser, after you selected a federated role, the policy attached to the role allowed the user to assume the role and granted permissions on the console.

Special acknowledgment goes to AWS colleague Jose Eduardo Montilla Lugo for his valuable comments and suggestions.

About the Authors

Rajesh Francis is a Data Warehouse Specialist Solutions Architect at AWS. He specializes in Amazon Redshift and works with customers to build scalable Analytic solutions.

Vaibhav Agrawal is an Analytics Specialist Solutions Architect at AWS. Throughout his career, he has focused on helping customers design and build well-architected analytics and decision support platforms.

Bhanu Pittampally is an Analytics Specialist Solutions Architect based out of Dallas. He specializes in building analytical solutions. His background is in data and analytics for over 14 years. His LinkedIn profile can be found here.