Configure single sign-on authentication for Amazon Athena with Azure AD built-in to on-premises AD

Amazon Athena is an interactive question service that makes it simpler to research knowledge immediately in Amazon Easy Storage Service (Amazon S3) utilizing customary SQL. Cloud operation groups can use AWS Identification and Entry Administration (IAM) federation to centrally handle entry to Athena. This simplifies administration by permitting a governing workforce to regulate consumer entry to Athena workgroups from a centrally managed Azure AD linked to an on-premise Energetic Listing. This setup reduces the overhead expertise by cloud operation groups when managing IAM customers. Athena helps federation with Energetic Listing Federation Service (ADFS), PingFederate, Okta, and Microsoft Azure Energetic Listing (Azure AD) federation.

This weblog publish illustrates the way to arrange AWS IAM federation with Azure AD linked to on-premises AD and configure Athena workgroup- stage entry for various customers. We’re going to cowl two eventualities:

1. Azure AD managed customers and teams, and on-premises AD.
2. On-prem Energetic listing managed customers and teams synchronized to Azure AD.

We don’t cowl the way to setup synchronization between on-premises AD and Azure AD with the assistance of Azure AD join. For extra info on the way to combine Azure AD with an AWS Managed AD , see Allow Workplace 365 with AWS Managed Microsoft AD with out consumer password synchronization and the way to combine Azure AD with an on-premises AD , see Microsoft article Customized set up of Azure Energetic Listing Join.

Resolution overview

This resolution helps you configure IAM federation with Azure AD linked to on-premises AD and configure Athena workgroup-level entry for customers. You possibly can management entry to the workgroup by both an on-premises AD group or Azure AD group. The answer consists of 4 sections:

1. Arrange Azure AD as your id supplier (IdP):
   1. Arrange Azure AD as your SAML IdP for an AWS single-account app.
   2. Configure the Azure AD app with delegated permissions.
2. Arrange your IAM IdP and roles:
   1. Arrange an IdP trusting Azure AD.
   2. Arrange an IAM consumer with learn position permission.
   3. Arrange an IAM position and insurance policies for every Athena workgroup.
3. Arrange consumer entry in Azure AD:
   1. Arrange computerized IAM position provisioning.
   2. Arrange consumer entry to the Athena workgroup position.
4. Entry Athena:
   1. Entry Athena utilizing the web-based Microsoft My Apps portal.
   2. Entry Athena utilizing SQL Workbench/J a free, DBMS-independent, cross-platform SQL question software.

The next diagram illustrates the structure of the answer.

The answer workflow contains the next steps:

1. The developer workstation connects to Azure AD by way of a SQL Workbench/j JDBC Athena driver to request a SAML token (two-step OAuth course of).
2. Azure AD sends authentication site visitors again to on-premises by way of an Azure AD pass-through agent or ADFS.
3. The Azure AD pass-through agent or ADFS connects to on-premises DC and authenticates the consumer.
4. The pass-through agent or ADFS sends a hit token to Azure AD.
5. Azure AD constructs a SAML token containing the assigned IAM position and sends it to the consumer.
6. The consumer connects to AWS Safety Token Service (AWS STS) and presents the SAML token to imagine the Athena position and generates short-term credentials.
7. AWS STS sends short-term credentials to the consumer.
8. The consumer makes use of the short-term credentials to hook up with Athena.

Stipulations

You could meet the next necessities previous to configuring the answer:

• On the Azure AD facet, full the next:
  • Arrange the Azure AD Join server and sync with on-premises AD
  • Arrange the Azure AD pass-through or Microsoft ADFS federation between Azure AD and on-premises AD
  • Create three customers (user1, user2, user3) and three teams (athena-admin-adgroup, athena-datascience-adgroup, athena-developer-adgroup) for 3 respective Athena workgroups
• On the Athena facet, create three Athena workgroups: athena-admin-workgroup, athena-datascience-workgroup, athena-developer-workgroup

For extra info on utilizing pattern Athena workgroups, see A public knowledge lake for evaluation of COVID-19 knowledge.

Arrange Azure AD

On this part we are going to cowl Azure AD configuration particulars for Athena in Microsoft Azure subscription. Primarily we are going to register an app, configure federation, delegate app permission and generate App secret.

Set Azure AD as SAML IdP for an AWS single-account app

To arrange Azure AD as your SAML IdP, full the next steps:

1. Check in to the Azure Portal with Azure AD world admin credentials.
2. Select Azure Energetic Listing.
3. Select Enterprise functions.
4. Select New utility.
   
5. Seek for Amazon within the search bar.
6. Select AWS Single-Account Entry.
7. For Title, enter Athena-App.
   
8. Select Create.
9. Within the Getting Began part, underneath Arrange single signal on, select Get began.
   
10. For Choose a single sign-on technique, select SAML.
    
11. For Fundamental SAML Configuration, select Edit.
    
    
12. For Identifier (Entity ID), enter https://signin.aws.amazon.com/saml#1.
13. Select Save.
    
14. Below SAML Signing Certificates, for Federation Metadata XML, select Obtain.

This file is required to configure your IAM IdP within the subsequent part. Save this file in your native machine to make use of later when configuring IAM on AWS.

Configure your Azure AD app with delegated permissions

To configure your Azure AD app, full the next steps:

1. Select Azure Energetic Listing.
2. Select App registrations and All Purposes.
3. Seek for and select Athena-App.
4. Notice the values for Utility (consumer) ID and Listing (tenant) ID.

You want these values within the JDBC connection while you connect with Athena.

5. Below API Permissions, select Add a permission.
6. Select Microsoft Graph and Delegated permissions.
7. For Choose permissions, seek for consumer.learn.
8. For Consumer, select Consumer.Learn.
9. Select Add permission.
   
10. Select Grant admin consent and Sure.
    
11. Select Authentication and Add a platform.
12. Select Cell and Desktop functions.
    
13. Below Customized redirect URIs, enter http://localhost/athena.
14. Select Configure.
15. Select Certificates & secrets and techniques and New consumer secret.
16. Enter an outline.
17. For Expires, select 24 months.
    
18. Copy the consumer secret worth to make use of when configuring the JDBC connection.

Arrange the IAM IdP and roles

On this part we are going to cowl IAM configuration in AWS account. Primarily we are going to create an IAM consumer, Roles and insurance policies.

Arrange an IdP trusting Azure AD

To arrange your IdP trusting Azure AD, full the next steps:

1. On the IAM console, select Identification suppliers within the navigation pane.
2. Select Add supplier.
3. For Supplier Kind, select SAML.
4. For Supplier Title, enter AzureADAthenaProvider.
5. For Metadata Doc, add the file downloaded from Azure Portal.
6. Select Add supplier.

Arrange an IAM consumer with learn position permission

To arrange your IAM consumer, full the next steps:

1. On the IAM console, select Customers within the navigation pane.
2. Select Add consumer.
3. For Consumer title, enter ReadRoleUser.
4. For Entry kind, choose Programmatic entry.
5. Select Subsequent: Permissions.
6. For Set permissions, select Connect present insurance policies immediately.
7. Select Create coverage.
8. Choose JSON and enter the next coverage, which supplies learn entry to enumerate roles in IAM:

   {
       "Model": "2012-10-17",
       "Assertion": [
           {
               "Effect": "Allow",
               "Action": [
                   "iam:ListRoles"
               ],
               "Useful resource": "*"
           }
       ]
   }
   

9. Select Subsequent: Tags.
10. Select Subsequent: Evaluation.
11. For Title, enter readrolepolicy.
12. Select Create coverage.
13. On the Add Consumer tab, seek for and select the position readrole.
14. Select Subsequent: tags.
15. Select Subsequent: Evaluation.
16. Select Create consumer.
17. Obtain the .csv file containing the entry key ID and secret entry key.

We use these when configuring Azure AD computerized provisioning.

Arrange an IAM position and insurance policies for every Athena workgroup

To arrange IAM roles and insurance policies to your Athena workgroups, full the next steps:

1. On the IAM console, select Roles within the navigation pane.
2. Select Create position.
3. For Choose kind of trusted entity, select SAML 2.0 federation.
4. For SAML supplier, select AzureADAthenaProvider.
5. Select Enable programmatic and AWS Administration Console entry.
6. Below Situation, select Key.
7. Choose SAML:aud.
8. For Situation, choose StringEquals.
9. For Worth, enter http://localhost/athena.
10. Select Subsequent: Permissions.
11. Select Create coverage.
12. Select JSON and enter the next coverage (present the ARN of your workgroup):

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Action": [
                "athena:ListEngineVersions",
                "athena:ListWorkGroups",
                "athena:ListDataCatalogs",
                "athena:ListDatabases",
                "athena:GetDatabase",
                "athena:ListTableMetadata",
                "athena:GetTableMetadata"
            ],
            "Useful resource": "*"
        },
        {
            "Impact": "Enable",
            "Motion": [
                "athena:BatchGetQueryExecution",
                "athena:GetQueryExecution",
                "athena:ListQueryExecutions",
                "athena:StartQueryExecution",
                "athena:StopQueryExecution",
                "athena:GetQueryResults",
                "athena:GetQueryResultsStream",
                "athena:CreateNamedQuery",
                "athena:GetNamedQuery",
                "athena:BatchGetNamedQuery",
                "athena:ListNamedQueries",
                "athena:DeleteNamedQuery",
                "athena:CreatePreparedStatement",
                "athena:GetPreparedStatement",
                "athena:ListPreparedStatements",
                "athena:UpdatePreparedStatement",
                "athena:DeletePreparedStatement"
            ],
            "Useful resource": [
                "arn:aws:athena:xxxx:xxxxxx:xxx/xxxx"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "athena:DeleteWorkGroup",
                "athena:UpdateWorkGroup",
                "athena:GetWorkGroup",
                "athena:CreateWorkGroup"
            ],
            "Useful resource": [
                "arn:aws:athena:xxxx:xxxxxx:xxx/xxxx"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "glue:CreateDatabase",
                "glue:DeleteDatabase",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:UpdateDatabase",
                "glue:CreateTable",
                "glue:DeleteTable",
                "glue:BatchDeleteTable",
                "glue:UpdateTable",
                "glue:GetTable",
                "glue:GetTables",
                "glue:BatchCreatePartition",
                "glue:CreatePartition",
                "glue:DeletePartition",
                "glue:BatchDeletePartition",
                "glue:UpdatePartition",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:BatchGetPartition"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:CreateBucket",
                "s3:PutObject",
                "s3:PutBucketPublicAccessBlock"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "s3:ListBucket",
                "s3:GetBucketLocation",
                "s3:ListAllMyBuckets"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "sns:ListTopics",
                "sns:GetTopicAttributes"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "cloudwatch:PutMetricAlarm",
                "cloudwatch:DescribeAlarms",
                "cloudwatch:DeleteAlarms"
            ],
            "Useful resource": [
                "*"
            ]
        },
        {
            "Impact": "Enable",
            "Motion": [
                "lakeformation:GetDataAccess"
            ],
            "Useful resource": [
                "*"
            ]
        }
    ]
}

The coverage grants full entry to Athena workgroup. It’s based mostly on the AWS managed coverage AmazonAthenaFullAccess and workgroup instance insurance policies.

13. Select Subsequent: Tags.
14. Select Subsequent: Evaluation.
15. For Title, enter athenaworkgroup1policy.
16. Select Create coverage.
17. On the Create position tab, seek for athenaworkgroup1policy and choose the coverage.
18. Select Subsequent: Tags.
19. Select Subsequent: Evaluation.
20. Select Create position.
21. For Title, enter athenaworkgroup1role.
22. Select Create position.

Arrange consumer entry in Azure AD

On this part we are going to setup Automated provisioning and assign customers to app from Microsoft Azure portal.

Arrange computerized IAM position provisioning

To arrange computerized IAM position provisioning, full the next steps:

1. Check in to the Azure Portal with Azure AD world admin credentials.
2. Select Azure Energetic Listing.
3. Select Enterprise Purposes and select Athena-App.
4. Select Provision Consumer Accounts.
   
5. Within the Provisioning part, select Get began.
6. For Provisioning Mode, select Automated.
   
7. Broaden Admin credentials and populate clientsecret and Secret Token with the entry key ID and secret entry key of ReadRoleUser, respectively.
8. Select Check Connection and Save.
9. Select Begin provisioning.

The preliminary cycle can take a while to finish, after which the IAM roles are populated in Azure AD.

Arrange consumer entry to the Athena workgroup position

To arrange consumer entry to the workgroup position, full the next steps:

1. Check in to Azure Portal with Azure AD world admin credentials.
2. Select Azure Energetic Listing.
3. Select Enterprise Purposes and select Athena-App.
4. Select Assign customers and teams and Add consumer/group.
5. Below Customers and teams, choose the group that you just need to assign Athena permission to. For this publish, we use athena-admin-adgroup; alternatively, you’ll be able to choose user1.
6. Select Choose.
7. For Choose a task, choose the position athenaworkgroup1role.
8. Select Choose.
9. Select Assign.

Entry Athena

On this part we are going to display the way to entry Athena from AWS console and developer software SQL Workbench/J

Entry Athena utilizing the web-based Microsoft My Apps portal

To make use of the Microsoft My Apps portal to entry Athena, full the next steps:

1. Check in to Azure Portal with Azure AD world admin credentials.
2. Select Azure Energetic Listing
3. Select Enterprise Purposes and select Athena-App.
4. Select
5. Properties.
6. Copy the worth for Consumer entry URL.
7. Open an online browser and enter the URL.

The hyperlink redirects you to an Azure login web page.

7. Log in with the on-premises consumer credentials.

You’re redirected to the AWS Administration Console.

Entry Athena utilizing SQL Workbench/J

In extremely regulated organizations, inside customers aren’t allowed to make use of the console to entry Athena. In such instances, you need to use SQL Workbench/J, an open-source software that allows connectivity to Athena utilizing a JDBC driver.

1. Obtain the newest Athena JDBC driver (select the suitable driver based mostly in your Java model).
2. Obtain and set up SQL Workbench/J.
3. Open SQL Workbench/J.
4. On the File menu, select Join Window.
5. Select Handle Drivers.
6. For Title, enter a reputation to your driver.
7. Browse to the folder location the place you downloaded and unzipped the driving force.
8. Select OK.
   

Now that we configured the Athena driver, it’s time to hook up with Athena. You must fill out the connection URL, consumer title, and password.

Use the next connection string to hook up with Athena with a consumer account with out MFA (present the values collected earlier within the publish):

jdbc:awsathena://AwsRegion=xxxx;AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.AzureCredentialsProvider;tenant_id=xxxx;client_id=xxxx;Workgroup=xxxx;client_secret=xxxx

To attach utilizing a consumer account with MFA enabled, use the browser Azure AD Credentials Supplier. You must assemble the connection URL and fill out the consumer title Username and password

Use the next connection string to hook up with Athena with a consumer account that has MFA enabled (present the values you collected earlier):

jdbc:awsathena://AwsRegion=xxxx;AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.BrowserAzureCredentialsProvider;tenant_id=xxxx;client_id=xxxx;Workgroup=xxxx;

Substitute textual content in crimson with particulars collected earlier within the article.

When the connection is established, you’ll be able to run queries in opposition to Athena.

Proxy configuration

In case you’re connecting to Athena by way of a proxy server, guarantee that the proxy server permits port 444. The consequence set streaming API makes use of port 444 on the Athena server for outbound communications. Set the ProxyHost property to the IP handle or host title of your proxy server. Set the ProxyPort property to the variety of the TCP port that the proxy server makes use of to pay attention for consumer connections. See the next code:

jdbc:awsathena://AwsRegion=xxxx;AwsCredentialsProviderClass=com.simba.athena.iamsupport.plugin.BrowserAzureCredentialsProvider;tenant_id=xxxx;client_id=xxxx;Workgroup=xxxx;ProxyHost=xxxx;ProxyPort=xxxx

Abstract

On this publish, we configured IAM federation with Azure AD linked to on-premises AD and arrange granular entry to an Athena workgroup. We additionally checked out the way to entry Athena by way of the console utilizing the Microsoft My Apps net portal and SQL Workbench/J software. We additionally mentioned how the connection works over a proxy. The identical federation infrastructure can be leveraged for ODBC driver configuration. It’s also possible to use the directions on this publish to arrange SAML-based Azure IdP to allow federated entry to Athena Workgroups.

In regards to the Creator

Niraj Kumar is a Principal Technical Account Supervisor for monetary companies at AWS, the place he helps clients design, architect, construct, function, and assist workloads on AWS in a safe and strong method. He has over 20 years of numerous IT expertise within the fields of enterprise structure, cloud and virtualization, safety, IAM, resolution structure, and data techniques and applied sciences. In his free time, he enjoys mentoring, teaching, trekking, watching documentaries along with his son, and studying one thing completely different day-after-day.