Combine Amazon Redshift native IdP federation with Microsoft Azure AD utilizing a SQL consumer

Amazon Redshift accelerates your time to insights with quick, straightforward, and safe cloud knowledge warehousing at scale. Tens of hundreds of consumers depend on Amazon Redshift to research exabytes of knowledge and run complicated analytical queries.

The brand new Amazon Redshift native id supplier authentication simplifies administration by sharing id and group membership data to Amazon Redshift from a third-party id supplier (IdP) service, comparable to Microsoft Azure Energetic Listing (Azure AD), and enabling Amazon Redshift to natively course of third-party tokens, identities, and group permissions. This course of could be very straightforward to arrange, gives a safe and smoother buyer expertise for managing identities and teams at a centralized exterior IdP, and integrates natively with Amazon Redshift.

On this publish, we concentrate on Microsoft Azure AD because the IdP and supply step-by-step steerage to attach SQL shoppers like SQL Workbench/J and DBeaver with Amazon Redshift utilizing a local IdP course of. Azure AD manages the customers and gives federated entry to Amazon Redshift. You don’t have to create separate Amazon Redshift database customers, AWS Id and Entry Administration (IAM) roles, or IAM insurance policies with this setup.

Answer overview

Utilizing an Amazon Redshift native IdP has the next advantages:

• Permits your customers to be routinely signed in to Amazon Redshift with their Azure AD accounts
• You may handle customers and teams from a centralized IdP
• Exterior customers can securely entry Amazon Redshift with out manually creating new consumer names or roles utilizing their current company listing credentials
• Exterior consumer group memberships are natively mirrored with Amazon Redshift roles and customers

The next diagram illustrates the structure of a local IdP for Amazon Redshift:

The workflow accommodates the next steps:

1. You configure a JDBC or ODBC driver in your SQL consumer to make use of Azure AD federation and use Azure AD login credentials to sign up.
2. Upon a profitable authentication, Azure AD points an authentication token (OAuth token) again to the Amazon Redshift driver.
3. The motive force forwards the authentication token to the Amazon Redshift cluster to provoke a brand new database session.
4. Amazon Redshift verifies and validates the authentication token.
5. Amazon Redshift calls the Azure Graph API to acquire the consumer’s group membership.
6. Amazon Redshift maps the logged-in Azure AD consumer to the Amazon Redshift consumer and maps the Azure AD teams to Amazon Redshift roles. If the consumer and teams don’t exist, Amazon Redshift routinely creates these identities inside the IdP namespace.

To implement the answer, you full the next high-level steps:

1. Arrange your Azure software.
   1. Create OAuth Utility
   2. Create Redshift Consumer software
   3. Create Azure AD Group
2. Acquire Azure AD data for the Amazon Redshift IdP.
3. Arrange the IdP on Amazon Redshift.
4. Arrange Amazon Redshift permissions to exterior identities.
5. Configure the SQL consumer (for this publish, we use SQL Workbench/J and DBeaver).

Conditions

You want the next stipulations to arrange this answer:

Arrange your Azure software

For integrating with any SQL consumer/BI software besides Microsoft Energy BI, we’d be creating two purposes. First software will likely be used to authenticate the consumer and supply a login token.  Second software will likely be utilized by Redshift to retrieve consumer and group data.

Step 1: Create OAuth Utility

1. Check in to the Azure portal together with your Microsoft account.
2. Navigate to the Azure Energetic Listing software.
3. Beneath Handle within the navigation pane, select App registrations after which select New registration.
   
4. For Title, enter a reputation (for instance, oauth_application).
5. For Redirect URI, select Public consumer/native (cell and desktop) and enter the redirect URL http://localhost:7890/redshift/. For this publish, we’re protecting the default settings for the remainder of the fields.
6. Select Register.
   
7. Within the navigation pane, underneath Handle, select Expose an API.

In case you’re establishing for the primary time, you may see Set to the appropriate of Utility ID URI.

8. Select Set after which select Save.
   
9. After the applying ID URI is ready up, select Add a scope.
10. For Scope identify, enter a reputation (for instance, jdbc_login).
11. For Admin consent show identify, enter a show identify (for instance, JDBC login).
12. For Admin consent description, enter an outline of the scope.
13. Select Add scope.
    
    
14. After the scope is added, be aware down the applying ID URI (for instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p) and API scope (api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login) with a view to register the IdP in Amazon Redshift later.

The appliance ID URI is called <Microsoft_Azure_Application_ID_URI> within the following part.

The API scope is called <Microsoft_Azure_API_scope_value> when establishing the SQL consumer comparable to DBeaver and SQL Workbench/J.

Step 2. Create Redshift Consumer Utility

1. Navigate to the Azure Energetic Listing software.
2. Beneath Handle within the navigation pane, select App registrations after which select New registration.
3. For Title, enter a reputation (for instance, redshift_client). For this publish, we’re protecting the default settings for the remainder of the fields.
4. Select Register.
   
5. On the newly created software Overview web page, find the consumer ID and tenant ID and be aware down these IDs with a view to register the IdP in Amazon Redshift later.
6. Within the navigation pane, select Certificates & secrets and techniques.
7. Select New consumer secret.
8. Enter a Description, choose an expiration for the key or specify a customized lifetime. We’re protecting Microsoft really useful default expiration worth of 6 months. Select Add.
9. Copy the key worth.

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

10. Within the navigation pane, select API permissions.
11. Select Add a permission and select Microsoft Graph.
    
12. Select Utility permissions.
13. Search the listing and choose the Listing.Learn.All permission.
14. Select Add permissions.
    
15. After the permission is created, select Grant admin consent.
    
16. Within the pop-up field, select Sure to grant the admin consent.

The standing for the permission reveals as Granted for with a inexperienced verify mark.

Step 3. Create Azure AD Group

1. On the Azure AD residence web page, underneath Handle, select Teams.
   
2. Select New group.
   
3. Within the New Group part, present the required data.
   
4. Select No members chosen after which seek for the members.
5. Choose the members and select Choose. For this instance, you may search your username and click on choose.
   

You may see the variety of members within the Members part.

6. Select Create.
   

Acquire Azure AD data

Earlier than we acquire the Azure AD data, we have to determine the entry token model from the applying which you may have created earlier on the Azure portal underneath Step 1. Create OAuth Utility. 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, acquire the next parameters from Azure AD. In case you don’t have these parameters, contact your Azure admin.

1. issuer – This is called <Microsoft_Azure_issuer_value> within the following sections. In case you’re utilizing the v1.0 token, use https://sts.home windows.internet/<Microsoft_Azure_tenantid_value>/. In case you’re utilizing the v2.0 token, use https://login.microsoftonline.com/<Microsoft_Azure_tenantid_value>/v2.0. To seek out your Azure tenant ID, full the next steps:
   • Check in to the Azure portal together with your Microsoft account.
   • Beneath Handle, select App registrations.
   • Select any software which you may have created in earlier sections.
   • Click on on the Overview (left panel) web page and it’s listed within the Necessities part as Listing (tenant) ID.
     
     
2. client_id – This is called <Microsoft_Azure_clientid_value> within the following sections. An instance of a consumer ID is 123ab555-a321-666d-7890-11a123a44890). To get your consumer ID worth, find the applying you created earlier on the Azure portal underneath Step 2. Create Redshift Consumer Utility. Click on on the Overview (left panel) web page and it’s listed within the Necessities part.
   
3. 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 part underneath Step 2. Create Redshift Consumer Utility.
4. viewers – This is called <Microsoft_Azure_token_audience_value> within the following sections. In case you’re utilizing a v1.0 token, the viewers worth is the applying ID URI (for instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p). In case you’re utilizing a v2.0 token, the viewers worth is the consumer ID worth (for instance, 991abc78-78ab-4ad8-a123-zf123ab03612p). To get these values, please consult with the applying which you may have created in Step 1: Create OAuth Utility. Click on on the Overview (left panel) web page and it’s listed within the Necessities part.
   

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>"
   }';

For instance, the next code makes use of a v1.0 entry token:

CREATE IDENTITY PROVIDER test_idp TYPE 
azure NAMESPACE 'oauth_aad' 
PARAMETERS '{
"issuer":"https://sts.home windows.internet/87f4aa26-78b7-410e-bf29-57b39929ef9a/", 
"viewers":["api://991abc78-78ab-4ad8-a123-zf123ab03612p"],
"client_id":"123ab555-a321-666d-7890-11a123a44890", 
"client_secret":"KiG7Q~FEDnE.VsWS1IIl7LV1R2BtA4qVv2ixB"
}';

The next code makes use of a v2.0 entry token:

CREATE IDENTITY PROVIDER test_idp TYPE 
azure NAMESPACE 'oauth_aad' 
PARAMETERS '{
"issuer":
"https://login.microsoftonline.com/87f4aa26-78b7-410e-bf29-57b39929ef9a/v2.0",
"viewers":["991abc78-78ab-4ad8-a123-zf123ab03612p"], 
"client_id":"123ab555-a321-666d-7890-11a123a44890", 
"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 function assignments are routinely created in your Amazon Redshift cluster through the first login utilizing your native IdP until they had 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. This helps us keep away from creating a number of consumer names manually on the Amazon Redshift facet and assign permissions for a number of customers individually.

The function identify within the Amazon Redshift cluster seems 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 after we had been establishing the Azure software. In our instance, it’s oauth_aad:rsgroup.

Run the next command within the Amazon Redshift cluster to create a task:

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

For instance:

create function "oauth_aad:rsgroup";

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

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

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

grant choose on all tables in schema public to function "oauth_aad:rsgroup";

Create and assign permission to an Amazon Redshift consumer

This step is just 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. In case you don’t need to explicitly assign permission to an Amazon Redshift consumer, you may skip this step.

To create the consumer, use the next syntax:

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

For instance:

CREATE USER "oauth_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 "oauth_aad:john@azent.org";

Configure the SQL consumer

On this part, we offer directions to arrange a SQL consumer utilizing both DBeaver or SQL Workbench/J.

Arrange DBeaver

To arrange DBeaver, full the next steps:

1. Go to Database and select Driver Supervisor.
2. Seek for Redshift, then select it and select Copy.
   
3. On the Settings tab, for Driver identify, enter a reputation, comparable to Redshift Native IDP.
4. Replace the URL template to jdbc:redshift://{host}:{port}/{database}?plugin_name=com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider
   Be aware: On this URL template, don’t change the template parameters with the precise values. Please hold the worth as proven in screenshot beneath.
5. On the Libraries tab, select Add information. Preserve just one set of the most recent driver model (2.1.0.4 and upwards) and in case you see any older variations, delete these information.
6. Add all of the information from the downloaded AWS JDBC driver pack .zip file and select OK (keep in mind to unzip the .zip file).

Be aware: Use the Amazon Redshift driver 2.1.0.4 onwards, as a result of all earlier Amazon Redshift driver variations don’t help the Amazon Redshift native IdP function.

7. Shut the Driver Supervisor.
8. Go to Database and select New Database Connection.
9. Seek for Redshift Native IDP, then select it and select Subsequent.
   
10. For Host/Occasion, enter your Amazon Redshift endpoint. For e.g. test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com.
11. For Database, enter the database identify (for this publish, we use dev).
12. For Port, enter 5439.
    
13. Please get the beneath parameter values (scope, client_id and idp_tenant) from the applying which you may have created in Step 1: Create OAuth Utility. On the Driver properties tab, add the next properties:
    1. listen_port – 7890
    2. idp_response_timeout – 50
    3. scope – Enter the worth for <Microsoft_Azure_API_scope_value>.
       • In case you’re utilizing a v1.0 token, then use the scope worth (for instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login).
         
       • In case you’re utilizing a v2.0 token, the scope worth is the consumer ID worth (for instance, 991abc78-78ab-4ad8-a123-zf123ab03612p).
         
    4. client_id – Enter the worth for <Microsoft_Azure_clientid_value>. For instance, 991abc78-78ab-4ad8-a123-zf123ab03612p.
    5. idp_tenant – Enter the worth for <Microsoft_Azure_tenantid_value>. For instance, 87f4aa26-78b7-410e-bf29-57b39929ef9a.
       
14. You may confirm the connection by selecting Take a look at Connection.

You’re redirected to the browser to sign up together with your Azure AD credentials. In case, you get SSL associated error, then go to SSL tab and choose Use SSL

15. Log in to be redirected to a web page displaying the connection as profitable.
    
16. Select Okay.

Congratulations! You’ve gotten accomplished the Amazon Redshift native IdP setup with DBeaver.

Arrange SQL Workbench/J

To arrange SQL Workbench/J, full the next steps:

1. Create a brand new connection in SQL Workbench/J and select Amazon Redshift as the driving force.
2. Select Handle drivers and add all of the information from the downloaded AWS JDBC driver pack .zip file (keep in mind to unzip the .zip file).

Use the Amazon Redshift driver 2.1.0.4 onwards, as a result of all earlier Amazon Redshift driver variations don’t help the Amazon Redshift native IdP function.

3. For URL, enter jdbc:redshift://<cluster endpoint>:<port>:<databasename>. For e.g., jdbc:redshift://test-cluster.ab6yejheyhgf.us-east-1.redshift.amazonaws.com:5439/dev.
   
4. Please get the beneath parameter values (scope, client_id and idp_tenant) from the applying which you may have created in Step 1: Create OAuth Utility. On the Driver properties tab, add the next properties:
   1. plugin_name – com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider
   2. listen_port – 7890
   3. idp_response_timeout – 50
   4. scope – Enter the worth for <Microsoft_Azure_API_scope_value>.
      • In case you’re utilizing a v1.0 token, then use the scope worth (for instance, api://991abc78-78ab-4ad8-a123-zf123ab03612p/jdbc_login).
        
      • In case you’re utilizing a v2.0 token, the scope worth is the consumer ID worth (for instance, 991abc78-78ab-4ad8-a123-zf123ab03612p).
        
   5. client_id – Enter the worth for <Microsoft_Azure_clientid_value>. For instance, 991abc78-78ab-4ad8-a123-zf123ab03612p.
   6. idp_tenant – Enter the worth for <Microsoft_Azure_tenantid_value>. For instance, 87f4aa26-78b7-410e-bf29-57b39929ef9a.
5. Select OK.
   
6. Select Take a look at from SQL Workbench/J.

You’re redirected to the browser to sign up together with your Azure AD credentials.

7. Log in to be redirected to a web page displaying the connection as profitable.
8. Select End.
   
   
9. With this connection profile, run the next question to check Amazon Redshift native IdP authentication.
   

Congratulations! You’ve gotten accomplished the Amazon Redshift native IdP setup with SQL Workbench/J.

Finest Practices with Redshift native IdP:

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

Troubleshooting

In case your connection didn’t work, take into account the next:

• Allow logging within the driver. For directions, see Configure logging.
• Ensure 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.
• In case you’re getting errors whereas establishing the applying on Azure AD, be sure to have admin entry.
• In case you can authenticate by way of the SQL consumer however get a permission concern or can’t see objects, grant the related permission to the function, as detailed earlier on this publish.
• In case you get the error “declare worth doesn’t match anticipated worth,” be sure to offered the proper parameters throughout Amazon Redshift IdP registration.
• Test stl_error or stl_connection_log views on the Amazon Redshift cluster for authentication failures.

Conclusion

On this publish, we offered step-by-step directions to combine Amazon Redshift with Azure AD and SQL shoppers (SQL Workbench/J and DBeaver) utilizing Amazon Redshift native IdP authentication. We additionally confirmed how Azure group membership is mapped routinely with Amazon Redshift roles and arrange Amazon Redshift permissions.

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

In regards to the Authors

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

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

Ilesh Garish is a Software program Growth Engineer at AWS. His function 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 inner applied sciences at San Francisco Bay Space startups.

Dengfeng(Davis) Li is a Software program Growth Engineer at AWS. His ardour is creating ease-of-use, safe and scalable purposes. Prior to now few years, he labored on Redshift safety, knowledge sharing and catalog optimization.