Combine Amazon Redshift native IdP federation with Microsoft Azure AD and Energy BI

Amazon Redshift accelerates your time to insights with quick, straightforward, and safe cloud information warehousing at scale. Tens of 1000’s of shoppers depend on Amazon Redshift to research exabytes of information and run complicated analytical queries.

As enterprise clients look to construct their information warehouse on Amazon Redshift, they’ve many integration wants with the enterprise intelligence (BI) instruments they’re utilizing. For patrons who need to combine Amazon Redshift with their current id supplier (IdP) comparable to Microsoft Azure Energetic Listing (Azure AD) utilizing BI instruments and providers comparable to Energy BI Desktop and Energy BI service, we’ve got launched a local IdP for Amazon Redshift that will help you implement authentication and authorization for these instruments in a seamless approach.

Amazon Redshift native IdP simplifies the administration technique of managing identities and permission. This function offers native integration with Microsoft Azure AD, which you need to use for authentication and authorization with instruments like Energy BI. It makes use of your current IdP to simplify authentication and managing permissions. It does this by making it attainable to share id metadata to Amazon Redshift out of your IdP. On this strategy, an exterior IdP (comparable to Azure AD) points an entry token, which is handed to Amazon Redshift through a consumer, after which Amazon Redshift performs the token validation and declare extraction natively.

This put up exhibits a step-by-step implementation of the Amazon Redshift native IdP setup with Azure AD, which demonstrates methods to handle customers and teams with an organizational listing, and methods to federate into Amazon Redshift. You don’t have to create AWS Id and Entry Administration (IAM) roles, insurance policies, separate database customers, or teams in Amazon Redshift with this setup.

Answer overview

Utilizing an Amazon Redshift native IdP has the next advantages:

• You’ll be able to handle customers and teams from a centralized IdP
• Allows your customers to be robotically signed in to Amazon Redshift with their Azure AD accounts
• You’ll be able to robotically create Amazon Redshift roles with a namespace that represents exterior teams (comparable to Azure AD teams)
• Exterior consumer group membership is natively mirrored with Amazon Redshift roles and customers

The overall configuration steps of the Amazon Redshift native IdP strategy are as follows:

1. Register an utility in Azure AD and arrange teams.
2. Accumulate Azure AD info for the Amazon Redshift IdP.
3. Arrange the IdP on Amazon Redshift.
4. Arrange Amazon Redshift permissions to exterior identities.
5. Configure the consumer connection.

The next diagram illustrates the ensuing answer.

To get approved, the Energy BI consumer sends an authentication request to the Azure enterprise utility utilizing Azure AD credentials. After verification, Azure sends a JSON net token (OAuth token) to the Energy BI utility. The Energy BI utility forwards the connection string with the OAuth token to Amazon Redshift. Amazon Redshift parses and validates the token, and requests group info from Azure AD. Upon reception, Amazon Redshift robotically creates the consumer and roles, and does the respective mapping.

Stipulations

You want the next conditions to arrange this answer:

• A Microsoft Azure account that has an energetic subscription. It is advisable to an admin position to arrange the applying on Azure AD.
• Energy BI Desktop model 2.102.683.0 64-bit and above downloaded and put in. On this instance, we’ve got use a Home windows setting.
• The newest model of the Microsoft Enterprise/Customary Gateway put in.
• An AWS account with an Amazon Redshift cluster. On this put up, we join Energy BI Desktop and repair with a publicly accessible Amazon Redshift cluster.

Register an utility in Azure AD and arrange teams

To arrange the Azure utility and group permission, full the next steps:

1. Check in to the Azure portal together with your Microsoft account.
2. Navigate to the Azure Energetic Listing utility.
3. Beneath Handle, select App registrations and New registration.
4. For Identify, enter an utility identify (for instance, nativeoauthsetup).
5. Hold the default settings for the remainder of the fields.
6. Select Register to finish the preliminary utility registration.
   
7. On the newly created utility Overview web page, find the consumer ID and tenant ID and notice down these IDs as a way to register the IdP in Amazon Redshift later.
8. Beneath Handle within the navigation pane, select API permissions.
9. Select Add a permission.
   
10. Select Microsoft Graph after which select Software permissions.
    
11. Seek for listing and choose the Listing.Learn.All permission.
12. Select Add permissions.
    
13. Select Grant admin consent.
    
14. Within the popup field, select Sure to grant the admin consent.

The standing of the permission exhibits Granted for with a inexperienced verify mark.

15. Beneath Handle within the navigation pane, select Certificates & secrets and techniques.
16. Select Consumer secrets and techniques and select New consumer secret.
17. Enter a Description, choose an expiration for the key or specify a customized lifetime. We’re conserving Microsoft really useful default expiration worth of 6 months. Select Add.
    
18. Copy the key worth.

It could solely be current one time and after that you simply can not learn it.

19. On the Azure AD dwelling web page, underneath Handle within the navigation pane, select Teams.
20. Select New group.
    
21. Within the New Group part, present the required info.
    
22. Select No members chosen after which seek for the members.
23. Choose your members and select Choose. For this instance, you’ll be able to search your username and click on choose.
    

You’ll be able to see the variety of members within the Members part.

24. Select Create.
    

Accumulate Azure AD Info for Amazon Redshift IdP

Earlier than we accumulate the Azure AD info, we have to establish the entry token model from the applying which you’ve gotten created earlier. Within the navigation pane, underneath Handle, select Manifest part, then view the accessTokenAcceptedVersion parameter: null and 1 point out v1.0 tokens, and 2 signifies v2.0 tokens.

To configure your IdP in Amazon Redshift, accumulate the next parameters from Azure AD. If don’t have these parameters, contact your Azure admin.

• issuer – This is called <Microsoft_Azure_issuer_value>. When you’re utilizing the v1.0 token, use https://sts.home windows.internet/<Microsoft_Azure_tenantid_value>/. At present, Energy BI solely makes use of v1.0 token. When you’re utilizing the v2.0 token, use https://login.microsoftonline.com/<Microsoft_Azure_tenantid_value>/v2.0. To search out your Microsoft Azure tenant ID, full the next steps:
  • Check in to the Azure portal together with your Microsoft account.
  • Beneath Handle, select App registrations.
  • Select the Amazon Redshift utility you created earlier.
  • Click on on the Overview (left panel) web page and underneath Necessities, notice down the values.

• client_id – This is called <Microsoft_Azure_clientid_value> within the following sections. An instance of a consumer ID is 5ab12345-1234-1a12-123a-11abc1a12ab1). To get your consumer ID worth, find the Amazon Redshift utility you created earlier on the Azure portal; it’s listed within the Necessities part.
• client_secret – This is called <Microsoft_Azure_client_secret_value> within the following sections. An instance of a consumer secret worth is KiG7Q~FEDnE.VsWS1IIl7LV1R2BtA4qVv2ixB). To create your consumer secret worth, consult with the steps within the earlier part.
• viewers – This is called <Microsoft_Azure_token_audience_value> within the following sections. With Energy BI Desktop, it’s worthwhile to set the viewers worth as https://evaluation.home windows.internet/powerbi/connector/AmazonRedshift.

Arrange the IdP on Amazon Redshift

To arrange the IdP on Amazon Redshift, full the next steps:

1. Log in to Amazon Redshift with a superuser consumer identify and password utilizing question editor v2 or any SQL consumer.
2. Run the next SQL:

   CREATE IDENTITY PROVIDER <idp_name> TYPE azure 
   NAMESPACE '<namespace_name>' 
   PARAMETERS '{ 
   "issuer":"<Microsoft_Azure_issuer_value>", 
   "viewers":["<Microsoft_Azure_token_audience_value>"],
   "client_id":"<Microsoft_Azure_clientid_value>", 
   "client_secret":"<Microsoft_Azure_client_secret_value>"
   }';

In our instance, we use the v1.0 token issuer as a result of as of this writing, Energy BI solely makes use of the v1.0 token:

CREATE IDENTITY PROVIDER oauth_standard TYPE azure
NAMESPACE 'aad'
PARAMETERS '{
"issuer":"https://sts.home windows.internet/e12b1bb1-1234-12ab-abc1-1ab012345a12/",
"viewers":["https://analysis.windows.net/powerbi/connector/AmazonRedshift"],
"client_id":"5ab12345-1234-1a12-123a-11abc1a12ab1",
"client_secret":"KiG7Q~FEDnE.VsWS1IIl7LV1R2BtA4qVv2ixB"
}'

3. To change the IdP, use the next command (this new set of parameter values utterly replaces the present values):

   ALTER IDENTITY PROVIDER <idp_name> PARAMETERS 
   '{
   "issuer":"<Microsoft_Azure_issuer_value>",
   "viewers":["<Microsoft_Azure_token_audience_value>"], 
   "client_id":"<Microsoft_Azure_clientid_value>", 
   "client_secret":"<Microsoft_Azure_client_secret_value>"
   }';

4. To view a single registered IdP within the cluster, use the next code:

   DESC IDENTITY PROVIDER <idp_name>;

5. To view all registered IdPs within the cluster, use the next code:

   choose * from svv_identity_providers;

6. To drop the IdP, use the next command:

   DROP IDENTITY PROVIDER <idp_name> [CASCADE];

Arrange Amazon Redshift permissions to exterior identities

The customers, roles, and position assignments are robotically created in your Amazon Redshift cluster throughout the first login utilizing your native IdP except they have been manually created earlier.

Create and assign permission to Amazon Redshift roles

On this step, we create a task within the Amazon Redshift cluster primarily based on the teams that you simply created on the Azure AD portal.

The position identify within the Amazon Redshift cluster appears like <namespace>:<azure_ad_group_name>, the place the namespace is the one we offered within the IdP creation command and the group identify is the one we specified once we have been organising the Azure utility. In our instance, it’s aad:rsgroup.

Run the next command within the Amazon Redshift cluster:

create position "<namespace_name>:<Azure AD groupname>";

For instance:

create position "aad:rsgroup";

To grant permission to the Amazon Redshift position, enter the next command:

GRANT  ALL [ PRIVILEGES ] 
ON  ALL TABLES IN SCHEMA schema_name [, ...] 
TO position "<namespace_name>:<Azure AD groupname>";

Then grant related permission to the position as per your requirement. For instance:

grant choose on all tables in schema public to position "aad:rsgroup";

Create and assign permission to an Amazon Redshift consumer

This step is barely required if you wish to grant permission to an Amazon Redshift consumer as a substitute of roles. We create an Amazon Redshift consumer that maps to a Azure AD consumer after which grant permission to it. When you don’t need to explicitly assign permission to an Amazon Redshift consumer, you’ll be able to skip this step.

To create the consumer, use the next syntax:

CREATE USER "<namespace_name>:<Azure AD username>" PASSWORD DISABLE;

For instance:

CREATE USER "aad:john@azent.org" PASSWORD DISABLE;

We use the next syntax to grant permission to the Amazon Redshift consumer:

GRANT  ALL [ PRIVILEGES ] 
ON  ALL TABLES IN SCHEMA schema_name [, ...] 
TO "<namespace_name>:<Azure AD username>";

For instance:

grant choose on all tables in schema public to "aad:john@azent.org"

Configure your consumer connection utilizing an Amazon Redshift native IdP

On this part, we offer directions to arrange your consumer connection for both Energy BI Desktop or the Energy BI service.

Join Energy BI Desktop

On this instance, we use Energy BI Desktop to attach with Amazon Redshift utilizing a local IdP. Use Energy BI Desktop model: 2.102.683.0 64-bit and above.

1. In your Energy BI Desktop, select Get information.
   
2. Seek for the Amazon Redshift connector, then select it and select Join.
   
3. For Server, enter your Amazon Redshift cluster’s endpoint. For instance, test-cluster.ct4abcufthff.us-east-1.redshift.amazonaws.com.
4. For Database, enter your database identify. On this instance, we use dev.
5. Select OK.
   
6. Select Microsoft Account.
   
7. Select Check in.
   
8. Enter your Microsoft Account credentials.
   

Whenever you’re linked, you’ll be able to see the message You’re presently signed in.

9. Select Join.
   

Congratulations! You’re signed in utilizing the Amazon Redshift native IdP with Energy BI Desktop. Now you’ll be able to browse your information.

After that, you’ll be able to create your personal Energy BI report on the desktop model and publish it to your Microsoft account. For this instance, we created and printed a report named RedshiftOAuthReport, which I consult with later on this put up.

Join Energy BI service

Now, let’s join a Energy BI gateway with Amazon Redshift utilizing a local IdP. Earlier than continuing with under setup, please be sure to have downloaded and put in the newest model of the Microsoft Enterprise/Customary Gateway.

1. Open the Energy BI net utility and check in if crucial.

You’ll be able to see the RedshiftOAuthReport report that we created earlier.

2. Within the navigation pane, underneath Datasets, select the menu icon (three dots) subsequent to the report identify after which select Settings.
   
3. Allow Gateway connection on the settings web page.
   
4. Click on on the arrow on proper aspect and choose Manually add to gateway.
   
   
5. Within the Knowledge Supply Settings part, enter the suitable values:
   1. For Knowledge Supply Identify, enter a reputation.
   2. For Knowledge Supply Sort, select Amazon Redshift.
   3. For Server, enter your Amazon Redshift cluster’s endpoint.
   4. For Database, enter your database identify (for this put up, we use dev).
   5. For Authentication Technique, select OAuth2.
6. Select Edit credentials.
   
7. Within the pop-up field, select Check in.
   
8. Enter your Microsoft account credentials and observe the authentication course of.
9. After the authentication, select Add on the Knowledge Supply Settings web page.
10. Be sure that Gateway connection is enabled. If not, allow it.
11. Choose your gateway from the gateway listing.
12. On the Maps to menu, select your information supply.
13. Select Apply.
    

Congratulations! You may have accomplished the Amazon Redshift native IdP setup with Energy BI net service.

Finest Practices with Redshift native IdP:

• Pre-create the Amazon Redshift roles primarily based upon the teams which you’ve gotten created on the Azure AD portal.
• Assign permissions to Redshift roles as a substitute of assigning to every particular person exterior consumer. This may present smoother finish consumer expertise as consumer can have all of the required permission after they login utilizing native IdP.

Troubleshooting

In case your connection didn’t work, think about the next:

• Allow logging within the driver. For directions, see Configure logging.
• Be certain that to make use of the most recent Amazon Redshift JDBC driver model 2.1.0.4 onwards, which helps Amazon Redshift native IdP authentication.
• When you’re getting errors whereas organising the applying on Azure AD, be sure to have admin entry.
• When you can authenticate through the SQL consumer however get a permission subject or can’t see objects, grant the related permission to the position, as detailed earlier on this put up.
• When you get the error “declare worth doesn’t match anticipated worth,” be sure to offered the proper parameters throughout Amazon Redshift IdP registration.
• Verify stl_error or stl_connection_log views on the Amazon Redshift cluster for authentication failures.

Abstract

On this put up, we coated the step-by-step technique of integrating Amazon Redshift with Azure AD and Energy BI Desktop and net service utilizing Amazon Redshift native IdP federation. The method consisted of registering a Azure utility, creating Azure AD teams, organising the Amazon Redshift IdP, creating and assigning permission to Amazon Redshift roles, and at last configuring consumer connections.

For extra details about Amazon Redshift native IdP federation, see:

You probably have questions or options, please go away a remark.

Concerning the Authors

Maneesh Sharma is a Senior Database Engineer at AWS with greater than a decade of expertise designing and implementing large-scale information warehouse and analytics options. He collaborates with varied Amazon Redshift Companions and clients to drive higher integration.

Ilesh Garish is a Software program Improvement Engineer at AWS. His position is to develop connectors for Amazon Redshift. Previous to AWS, he constructed database drivers for the Oracle RDBMS, TigerLogic XDMS, and OpenAccess SDK. He additionally labored within the database inside applied sciences at San Francisco Bay Space startups.

Debu Panda is a Senior Supervisor, Product Administration at AWS. He’s an business chief in analytics, utility platform, and database applied sciences, and has greater than 25 years of expertise within the IT world.

Sergey Konoplev is a Senior Database Engineer on the amazon Redshift Workforce at AWS. Sergey has been specializing in Automation and enchancment of database and information operations for greater than a decade.