Executive Summary

TerraAlto enable authentication on AWS RedShift with internal Microsoft Active Directory using  ADFS Federation to facilitate the use of existing centralised management of authentication credentials for this global organisation’s users. Royal FrieslandCampina (RFC) is one of the world’s largest dairy companies.

About the Customer

Royal FrieslandCampina (RFC) is one of the world’s largest dairy companies with approximately $12.8 billion in annual revenue. The Dutch company produces and sells dairy-based beverages, cheeses, desserts, and infant and sports nutrition products across Europe, Asia, Africa and the Americas.

Customer Challenge

RFC wanted to integrate authentication for AWS Redshift with their internally hosted Microsoft Active Directory domain.

Why AWS

RFC as a company began their cloud journey on AWS in 2015 and it is has become their primary platform.

Why RFC Chose TerraAlto

RFC have been engaged with TerraAlto as their primary AWS partner since 2015 and work closely with on many AWS related projects included data platform initatives.

Partner (TerraAlto) Solution

The following document was used as a guide: https://aws.amazon.com/blogs/big-data/federate-database-user-authentication-easily-with-iam-and-amazon-redshift/ 

There were a number of steps involved:

  1. The first part of the process involves setting up trust between the AWS account and the Idp. The steps to do this are described here: https://aws.amazon.com/blogs/security/enabling-federation-to-aws-using-windows-active-directory-adfs-and-saml-2-0/ We had already set up ADFS, so we needed to set up the identity provider. To do this, we needed to download the SAML metadata document from the ADFS server at https://<yourservername>/FederationMetadata/2007-06/FederationMetadata.xml

  2. Once we had this document, we could create the identity provider. To do this log in to the AWS console and go to the IAM section. Under IAM, select Identity Providers:

  3. Next, select “Create Provider”:

  4. Select SAML as the Provider Type:

  5. Name the Provider and upload the SAML metadata document:

  6. Click Next and then Create

  7. This establishes the trust on the AWS side. The next step is to establish trust on the ADFS side. This is described here https://aws.amazon.com/blogs/security/enabling-federation-to-aws-using-windows-active-directory-adfs-and-saml-2-0/ under “Configuring AWS as a Trusted Relying Party”

  8. Next, we created the IAM role. To connect to Redshift using IAM credentials, this role needed permission to call the DescribeClusters and GetClusterCredentials for the target clusters. It also neeeded permissions to GetClusterCredentials and CreateClusterUser for the database user. CreateClusterUser is necessary if you want to auto-create database users. The role also needs JoinGroup permissions for the database group. The role policy looked like this:

  9. The Assume Role policy document for the role should allow the role to be assumed by the Provider that was created previously.

  10. Next the SAML assertions needed to be configured by the ADFS admins. To do this, we needed to provide them with the ARN’s of the IAM role and also the Identity Provider. These values needed to be returned in a SAML attribute called https://aws.amazon.com/SAML/Attributes/Role. The process for configuring SAML assertions is described here: https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml_assertions.html

  11. We were given a logon to test the federation, but we ran into some issues with this. When we would try to login and assume the role, we would get an error about an invalid arn. This was being caused by a zero-width character in the role arn. When this was removed, we ran into the error Not authorized to perform sts:AssumeRoleWithSAML Service: AWSSecurityTokenService; Status Code: 403. This was because the trust relationship in the policy had been set to https://signin.amazonaws.com/saml whereas it needed to be https://signin.aws.amazon.com/saml Once this had been corrected we were able to log into the console.

  12. The next step was to test connecting to Redshift. We set up an ODBC connection to do this. We needed to provide the cluster id, the region, the db group and adfs server. The db group can be configured in the SAML assertions, which is something we will do on rollout. We ran into issues with access to sts, where we needed to configure the proxy settings for ODBC. If you’re using Aginity, which doesn’t seem to have this option, you may have to consider using private endpoints.

Results and Benefits

TerraAlto has been able to provide:

  • working integration between AWS Redshift and internally hosted Microsoft Active Directory using ADFS Federation.
  • The authorisation component has been setup by TerraAlto to work based on mappings in a table in AWS Redshift, giving greater flexibility for authorisation control and reporting to the data platform team.