Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Setting up JDBC or ODBC single sign-on authentication You can leverage external identity providers (IdPs) to authenticate and authorize users accessing your Amazon Redshift cluster, simplifying user management and enhancing security. This enables centralized user management, role-based access control, and auditing capabilities across multiple services. Common use cases include streamlining authentication for diverse user groups, enforcing consistent access policies, and meeting regulatory requirements. The following pages guide you through configuring IdP integration with your Redshift cluster. For more information about configuring AWS as a service provider for the IdP, see [Configuring Your SAML 2.0 IdP with Relying Party Trust and Adding Claims](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml_relying-party.html#saml_relying-party) in the *IAM User Guide*. # AD FS This tutorial shows you how you can use AD FS as an identity provider (IdP) to access your Amazon Redshift cluster. ## Step 1: Set up AD FS and your AWS account to trust each other The following procedure describes how to set up a trust relationship. 1. Create or use an existing Amazon Redshift cluster for your AD FS users to connect to. To configure the connection, certain properties of this cluster are needed, such as the cluster identifier. For more information, see [Creating a Cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/create-cluster.html). 1. Set up AD FS to control Amazon Redshift access on the Microsoft Management Console: 1. Choose **ADFS 2.0**, and then choose **Add Relying Party Trust**. On the **Add Relying Party Trust Wizard** page, choose **Start**. 1. On the **Select Data Source** page, choose **Import data about the relying party published online or on a local network**. 1. For **Federation metadata address (host name or URL)**, enter **https://signin.aws.amazon.com/saml-metadata.xml**. The metadata XML file is a standard SAML metadata document that describes AWS as a relying party. 1. On the **Specify Display Name** page, enter a value for **Display name**. 1. On the **Choose Issuance Authorization Rules** page, choose an issuance authorization rule to either permit or deny all users to access this relying party. 1. On the **Ready to Add Trust** page, review your settings. 1. On the **Finish** page, choose **Open the Edit Claim Rules dialog for this relying party trust when the wizard closes**. 1. On the context (right-click) menu, choose **Relying Party Trusts**. 1. For your relying party, open the context (right-click) menu and choose **Edit Claim Rules**. On the **Edit Claim Rules** page, choose **Add Rule**. 1. For **Claim rule template**, choose **Transform an Incoming Claim**, and then on the **Edit Rule – NameId **page, do the following: + For **Claim rule name**, enter **NameId**. + For **Incoming claim name**, choose **Windows Account Name**. + For **Outgoing claim name**, choose **Name ID**. + For **Outgoing name ID format**, choose **Persistent Identifier**. + Choose **Pass through all claim values**. 1. On the **Edit Claim Rules** page, choose **Add Rule**. On the **Select Rule Template** page, for **Claim rule template**, choose **Send LDAP Attributes as Claims**. 1. On the **Configure Rule** page, do the following: + For **Claim rule name**, enter **RoleSessionName**. + For **Attribute store**, choose **Active Directory**. + For **LDAP Attribute**, choose **Email Addresses**. + For **Outgoing Claim Type**, choose **https://aws.amazon.com/SAML/Attributes/RoleSessionName**. 1. On the **Edit Claim Rules** page, choose **Add Rule**. On the **Select Rule Template** page, for **Claim rule template**, choose **Send Claims Using a Custom Rule**. 1. On the **Edit Rule – Get AD Groups** page, for **Claim rule name**, enter **Get AD Groups**. 1. For **Custom rule**, enter the following. ``` 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); ``` 1. On the **Edit Claim Rules** page, choose **Add Rule**. On the **Select Rule Template** page, for **Claim rule template**, choose **Send Claims Using a Custom Rule**. 1. On the **Edit Rule – Roles** page, for **Claim rule name**, type **Roles**. 1. For **Custom rule,** enter the following. ``` c:[Type == "http://temp/variable", Value =~ "(?i)^AWS-"] => issue(Type = "https://aws.amazon.com/SAML/Attributes/Role", Value = RegExReplace(c.Value, "AWS-", "arn:aws:iam::123456789012:saml-provider/ADFS,arn:aws:iam::123456789012:role/ADFS-")); ``` Note the ARNs of the SAML provider and role to assume. In this example, `arn:aws:iam:123456789012:saml-provider/ADFS` is the ARN of the SAML provider and `arn:aws:iam:123456789012:role/ADFS-` is the ARN of the role. 1. Make sure that you have downloaded the `federationmetadata.xml` file. Check that the document contents do not have invalid characters. This is the metadata file you use when configuring the trust relationship with AWS. 1. Create an IAM SAML identity provider on the IAM console. The metadata document. that you provide is the federation metadata XML file that you saved when you set up Azure Enterprise Application. For detailed steps, see [ Creating and Managing an IAM Identity Provider (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml.html#idp-manage-identityprovider-console) in the *IAM User Guide*. 1. Create an IAM role for SAML 2.0 federation on the IAM console. For detailed steps, see [Creating a Role for SAML](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-idp_saml.html#idp_saml_Create) in the *IAM User Guide*. 1. Create an IAM policy that you can attach to the IAM role that you created for SAML 2.0 federation on the IAM console. For detailed steps, see [Creating IAM Policies (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html#access_policies_create-start) in the *IAM User Guide*. For an Azure AD example, see [Setting up JDBC or ODBC single sign-on authentication](setup-azure-ad-identity-provider.md). ## Step 2: Set up JDBC or ODBC for authentication to AD FS ------ #### [ JDBC ] The following procedure describes how to set up a JDBC relationship to AD FS. + Configure your database client to connect to your cluster through JDBC using AD FS single sign-on. You can use any client that uses a JDBC driver to connect using AD FS single sign-on or use a language like Java to connect using a script. For installation and configuration information, see [Configuring a connection for JDBC driver version 2.x for Amazon Redshift](jdbc20-install.md). For example, you can use SQLWorkbench/J as the client. When you configure SQLWorkbench/J, the URL of your database uses the following format. ``` jdbc:redshift:iam://cluster-identifier:us-west-1/dev ``` If you use SQLWorkbench/J as the client, take the following steps: 1. Start SQL Workbench/J. In the **Select Connection Profile** page, add a **Profile Group**, for example **ADFS**. 1. For **Connection Profile**, enter your connection profile name, for example **ADFS**. 1. Choose **Manage Drivers**, and choose **Amazon Redshift**. Choose the **Open Folder** icon next to **Library**, then choose the appropriate JDBC .jar file. 1. On the **Select Connection Profile** page, add information to the connection profile as follows: + For **User**, enter your AD FS user name. This is the user name of the account that you are using for single sign-on that has permission to the cluster that you are trying to authenticate using. + For **Password**, enter your AD FS password. + For **Drivers**, choose **Amazon Redshift (com.amazon.redshift.jdbc.Driver)**. + For **URL**, enter **jdbc:redshift:iam://*your-cluster-identifier*:*your-cluster-region*/*your-database-name***. 1. Choose **Extended Properties**. For **plugin\$1name**, enter **com.amazon.redshift.plugin.AdfsCredentialsProvider**. This value specifies to the driver to use AD FS single sign-on as the authentication method. ------ #### [ ODBC ] **To set up ODBC for authentication to AD FS** + Configure your database client to connect to your cluster through ODBC using AD FS single sign-on. Amazon Redshift provides ODBC drivers for Linux, Windows, and macOS operating systems. Before you install an ODBC driver, determine whether your SQL client tool is 32-bit or 64-bit. Install the ODBC driver that matches the requirements of your SQL client tool. On Windows, in the **Amazon Redshift ODBC Driver DSN Setup** page, under **Connection Settings**, enter the following information: + For **Data Source Name**, enter ***your-DSN***. This specifies the data source name used as the ODBC profile name. + For **Auth type**, choose **Identity Provider: SAML**. This is the authentication method that the ODBC driver uses to authenticate using AD FS single sign-on. + For **Cluster ID**, enter ***your-cluster-identifier***. + For **Region**, enter ***your-cluster-region***. + For **Database**, enter ***your-database-name***. + For **User**, enter ***your-adfs-username***. This is the user name for the AD FS account that you are using for single sign-on that has permission to the cluster that you're trying to authenticate using. Use this only for **Auth type** is **Identity Provider: SAML**. + For **Password**, enter ***your-adfs-password***. Use this only for **Auth type** is **Identity Provider: SAML**. On macOS and Linux, edit the `odbc.ini` file as follows: **Note** All entries are case-insensitive. + For **clusterid**, enter ***your-cluster-identifier***. This is the name of the created Amazon Redshift cluster. + For **region**, enter ***your-cluster-region***. This is the AWS Region of the created Amazon Redshift cluster. + For **database**, enter ***your-database-name***. This is the name of the database that you're trying to access on the Amazon Redshift cluster. + For **locale**, enter **en-us**. This is the language that error messages display in. + For **iam**, enter **1**. This value specifies to the driver to authenticate using IAM credentials. + For **plugin\$1name**, do one of the following: + For AD FS single sign-on with MFA configuration, enter **BrowserSAML**. This is the authentication method that the ODBC driver uses to authenticate to AD FS. + For AD FS single sign-on configuration, enter **ADFS**. This is the authentication method that the ODBC driver uses to authenticate using Azure AD single sign-on. + For **uid**, enter ***your-adfs-username***. This is the user name of the Microsoft Azure account that you are using for single sign-on that has permission to the cluster you are trying to authenticate against. Use this only for **plugin\$1name** is **ADFS**. + For **pwd**, enter ***your-adfs-password***. Use this only for **plugin\$1name** is **ADFS**. On macOS and Linux, also edit the profile settings to add the following exports. ``` export ODBCINI=/opt/amazon/redshift/Setup/odbc.ini ``` ``` export ODBCINSTINI=/opt/amazon/redshift/Setup/odbcinst.ini ``` ------ # Azure You can use Microsoft Azure AD as an identity provider (IdP) to access your Amazon Redshift cluster. This tutorial shows you how you can use Azure as an identity provider (IdP) to access your Amazon Redshift cluster. To learn how to federate Amazon Redshift access with Microsoft Azure AD single sign-on, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/aXs9hEgJCss/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/aXs9hEgJCss) ## Step 1: Set up Azure and your AWS account to trust each other The following procedure describes how to set up a trust relationship. **To set up Azure AD and your AWS account to trust each other** 1. Create or use an existing Amazon Redshift cluster for your Azure AD users to connect to. To configure the connection, certain properties of this cluster are needed, such as the cluster identifier. For more information, see [Creating a Cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/create-cluster.html). 1. Set up an Azure Active Directory, groups, users used for AWS on the Microsoft Azure portal. 1. Add Amazon Redshift as an enterprise application on the Microsoft Azure portal to use for single sign-on to the AWS Console and federated login to Amazon Redshift. Choose **Enterprise application**. 1. Choose **\$1New application**. The Add an application page appears. 1. Search for **AWS** in the search field. 1. Choose **Amazon Web Services (AWS)** and choose **Add**. This creates the AWS application. 1. Under **Manage**, choose **Single sign-on**. 1. Choose **SAML**. The Amazon Web Services (AWS) \$1 SAML-based Sign-on page appears. 1. Choose **Yes** to proceed to the Set up Single Sign-On with SAML page. This page shows the list of pre-configured single sign-on related attributes. 1. For **Basic SAML Configuration**, choose the edit icon and choose **Save**. 1. When you are configuring for more than one application, provide an identifier value. For example, enter ***https://signin.aws.amazon.com/saml\$12***. Note that from the second application onwards, use this format with a \$1 sign to specify a unique SPN value. 1. In the **User Attributes and Claims** section, choose the edit icon. By default, the Unique User Identifier (UID), Role, RoleSessionName, and SessionDuration claims are pre-configured. 1. Choose **\$1 Add new claim** to add a claim for database users. For **Name**, enter **DbUser**. For **Namespace**, enter **https://redshift.amazon.com/SAML/Attributes**. For **Source**, choose **Attribute**. For **Source attribute**, choose **user.userprincipalname**. Then, choose **Save**. 1. Choose **\$1 Add new claim** to add a claim for AutoCreate. For **Name**, enter **AutoCreate**. For **Namespace**, enter **https://redshift.amazon.com/SAML/Attributes**. For **Source**, choose **Attribute**. For **Source attribute**, choose **"true"**. Then, choose **Save**. Here, `123456789012` is your AWS account, *`AzureSSO`* is an IAM role you created, and *`AzureADProvider`* is the IAM provider. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/setup-identity-provider-azure.html) 1. Under **App Registration > ***your-application-name*** > Authentication**, add **Mobile And Desktop Application**. Specify the URL as http://localhost/redshift/. 1. In the **SAML Signing Certificate** section, choose **Download** to download and save the federation metadata XML file for use when you create an IAM SAML identity provider. This file is used to create the single sign-on federated identity. 1. Create an IAM SAML identity provider on the IAM console. The metadata document that you provide is the federation metadata XML file that you saved when you set up Azure Enterprise Application. For detailed steps, see [Creating and Managing an IAM Identity Provider (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml.html#idp-manage-identityprovider-console) in the *IAM User Guide*. 1. Create an IAM role for SAML 2.0 federation on the IAM console. For detailed steps, see [ Creating a Role for SAML](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-idp_saml.html#idp_saml_Create) in the *IAM User Guide*. 1. Create an IAM policy that you can attach to the IAM role that you created for SAML 2.0 federation on the IAM console. For detailed steps, see [Creating IAM Policies (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html#access_policies_create-start) in the *IAM User Guide*. Modify the following policy (in JSON format) for your environment: + Substitute the AWS Region of your cluster for `us-west-1`. + Substitute your AWS account for *`123456789012`*. + Substitute your cluster identifier (or `*` for all clusters) for *`cluster-identifier`*. + Substitute your database (or `*` for all databases) for *`dev`*. + Substitute the unique identifier of your IAM role for *`AROAJ2UCCR6DPCEXAMPLE`*. + Substitute your tenant or company email domain for `example.com`. + Substitute the database group that you plan to assign the user to for *`my_dbgroup`*. ``` { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": "redshift:GetClusterCredentials", "Resource": [ "arn:aws:redshift:us-west-1:123456789012:dbname:cluster-identifier/dev", "arn:aws:redshift:us-west-1:123456789012:dbuser:cluster-identifier/${redshift:DbUser}", "arn:aws:redshift:us-west-1:123456789012:cluster:cluster-identifier" ], "Condition": { "StringEquals": { "aws:userid": "AROAJ2UCCR6DPCEXAMPLE:${redshift:DbUser}@example.com" } } }, { "Effect": "Allow", "Action": "redshift:CreateClusterUser", "Resource": "arn:aws:redshift:us-west-1:123456789012:dbuser:cluster-identifier/${redshift:DbUser}" }, { "Effect": "Allow", "Action": "redshift:JoinGroup", "Resource": "arn:aws:redshift:us-west-1:123456789012:dbgroup:cluster-identifier/my_dbgroup" }, { "Effect": "Allow", "Action": [ "redshift:DescribeClusters", "iam:ListRoles" ], "Resource": "*" } ] } ``` This policy grants permissions as follows: + The first section grants permission to the `GetClusterCredentials` API operation to get temporary credentials for the specified cluster. In this example, the resource is `cluster-identifier` with database *`dev`*, in account *`123456789012`*, and in AWS Region *`us-west-1`*. The `${redshift:DbUser}` clause allows only users that match the `DbUser` value specified in Azure AD to connect. + The condition clause enforces that only certain users get temporary credentials. These are users under the role specified by the role unique ID *`AROAJ2UCCR6DPCEXAMPLE`* in the IAM account identified by an email address in your company's email domain. For more information about unique IDs, see [Unique IDs](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_identifiers.html#identifiers-unique-ids) in the *IAM User Guide*. Your setup with your IdP (in this case, Azure AD) determines how the condition clause is written. If your employee's email is `johndoe@example.com`, first set `${redshift:DbUser}` to the super field that matches the employee's user name `johndoe`. Then, to make this condition work, set the AWS SAML `RoleSessionName` field to the super field that matches the employee’s email `johndoe@example.com`. When you take this approach, consider the following: + If you set `${redshift:DbUser}` to be the employee's email, then remove the `@example.com` in the example JSON to match the `RoleSessionName`. + If you set the `RoleSessionId` to be just the employee's user name, then remove the `@example.com` in the example to match the `RoleSessionName`. + In the example JSON, the `${redshift:DbUser}` and `RoleSessionName` are both set to the employee's email. This example JSON uses the Amazon Redshift database user name with `@example.com` to sign the user in to access the cluster. + The second section grants permission to create a `dbuser` name in the specified cluster. In this example JSON, it restricts creation to `${redshift:DbUser}`. + The third section grants permission to specify which `dbgroup` a user can join. In this example JSON, a user can join the `my_dbgroup` group in the specified cluster. + The fourth section grants permission to actions the user can do on all resources. In this example JSON, it allows users to call `redshift:DescribeClusters` to get cluster information such as the cluster endpoint, AWS Region, and port. It also allows users to call `iam:ListRoles` to check which roles a user can assume. ## Step 2: Set up JDBC or ODBC for authentication to Azure ------ #### [ JDBC ] **To set up JDBC for authentication to Microsoft Azure AD** + Configure your database client to connect to your cluster through JDBC using your Azure AD single sign-on. You can use any client that uses a JDBC driver to connect using Azure AD single sign-on or use a language like Java to connect using a script. For installation and configuration information, see [Configuring a connection for JDBC driver version 2.x for Amazon Redshift](jdbc20-install.md). For example, you can use SQLWorkbench/J as the client. When you configure SQLWorkbench/J, the URL of your database uses the following format. ``` jdbc:redshift:iam://cluster-identifier:us-west-1/dev ``` If you use SQLWorkbench/J as the client, take the following steps: 1. Start SQL Workbench/J. On the **Select Connection Profile** page, add a **Profile Group** called **AzureAuth**. 1. For **Connection Profile**, enter **Azure**. 1. Choose **Manage Drivers**, and choose **Amazon Redshift**. Choose the **Open Folder** icon next to **Library**, then choose the appropriate JDBC .jar file. 1. On the **Select Connection Profile** page, add information to the connection profile as follows: + For **User**, enter your Microsoft Azure user name. This is the user name of the Microsoft Azure account that you are using for single sign-on that has permission to the cluster that you are trying to authenticate using. + For **Password**, enter your Microsoft Azure password. + For **Drivers**, choose **Amazon Redshift (com.amazon.redshift.jdbc.Driver)**. + For **URL**, enter **jdbc:redshift:iam://*your-cluster-identifier*:*your-cluster-region*/*your-database-name***. 1. Choose **Extended Properties** to add additional information to the connection properties, as described following. For Azure AD single sign-on configuration, add additional information as follows: + For **plugin\$1name**, enter **com.amazon.redshift.plugin.AzureCredentialsProvider**. This value specifies to the driver to use Azure AD Single Sign-On as the authentication method. + For **idp\$1tenant**, enter ***your-idp-tenant***. Used only for Microsoft Azure AD. This is the tenant name of your company configured on Azure AD. This value can either be the tenant name or the tenant unique ID with hyphens. + For **client\$1secret**, enter ***your-azure-redshift-application-client-secret***. Used only for Microsoft Azure AD. This is your client secret of the Amazon Redshift application that you created when setting up your Azure Single Sign-On configuration. This is only applicable to the com.amazon.redshift.plugin.AzureCredentialsProvider plugin. + For **client\$1id**, enter ***your-azure-redshift-application-client-id***. Used only for Microsoft Azure AD. This is the client ID (with hyphens) of the Amazon Redshift application that you created when setting up your Azure Single Sign-On configuration. For Azure AD single sign-on with MFA configuration, add additional information to the connection properties as follows: + For **plugin\$1name**, enter **com.amazon.redshift.plugin.BrowserAzureCredentialsProvider**. This value specifies to the driver to use Azure AD single sign-on with MFA as the authentication method. + For **idp\$1tenant**, enter ***your-idp-tenant***. Used only for Microsoft Azure AD. This is the tenant name of your company configured on Azure AD. This value can either be the tenant name or the tenant unique ID with hyphens. + For **client\$1id**, enter ***your-azure-redshift-application-client-id***. This option is used only for Microsoft Azure AD. This is the client ID (with hyphens) of the Amazon Redshift application that you created when setting up your Azure AD single sign-on with MFA configuration. + For **listen\$1port**, enter ***your-listen-port***. This is the port that local server is listening to. The default is 7890. + For **idp\$1response\$1timeout**, enter ***the-number-of-seconds***. This is the number of seconds to wait before timing out when the IdP server sends back a response. The minimum number of seconds must be 10. If establishing the connection takes longer than this threshold, then the connection is aborted. ------ #### [ ODBC ] **To set up ODBC for authentication to Microsoft Azure AD** + Configure your database client to connect to your cluster through ODBC using your Azure AD single sign-on. Amazon Redshift provides ODBC drivers for Linux, Windows, and macOS operating systems. Before you install an ODBC driver, determine whether your SQL client tool is 32-bit or 64-bit. Install the ODBC driver that matches the requirements of your SQL client tool. On Windows, in the **Amazon Redshift ODBC Driver DSN Setup** page, under **Connection Settings**, enter the following information: + For **Data Source Name**, enter ***your-DSN***. This specifies the data source name used as the ODBC profile name. + For **Auth type** for Azure AD single sign-on configuration, choose **Identity Provider: Azure AD**. This is the authentication method that the ODBC driver uses to authenticate using Azure single sign-on. + For **Auth type** for Azure AD single sign-on with MFA configuration, choose **Identity Provider: Browser Azure AD**. This is the authentication method that the ODBC driver uses to authenticate using Azure single sign-on with MFA. + For **Cluster ID**, enter ***your-cluster-identifier***. + For **Region**, enter ***your-cluster-region***. + For **Database**, enter ***your-database-name***. + For **User**, enter ***your-azure-username***. This is the user name for the Microsoft Azure account that you are using for single sign-on that has permission to the cluster that you're trying to authenticate using. Use this only for **Auth Type** is **Identity Provider: Azure AD**. + For **Password**, enter ***your-azure-password***. Use this only for **Auth Type** is **Identity Provider: Azure AD**. + For **IdP Tenant**, enter ***your-idp-tenant***. This is the tenant name of your company configured on your IdP (Azure). This value can either be the tenant name or the tenant unique ID with hyphens. + For **Azure Client Secret**, enter ***your-azure-redshift-application-client-secret***. This is the client secret of the Amazon Redshift application that you created when setting up your Azure single sign-on configuration. + For **Azure Client ID**, enter ***your-azure-redshift-application-client-id***. This is the client ID (with hyphens) of the Amazon Redshift application that you created when setting up your Azure single sign-on configuration. + For **Listen Port**, enter ***your-listen-port***. This is the default listen port that local server is listening to. The default is 7890. This applies only to the Browser Azure AD plugin. + For **Response Timeout**, enter ***the-number-of-seconds***. This is the number of seconds to wait before timing out when the IdP server sends back a response. The minimum number of seconds must be 10. If establishing the connection takes longer than this threshold, then the connection is aborted. This option applies only to the Browser Azure AD plugin. On macOS and Linux, edit the `odbc.ini` file as follows: **Note** All entries are case-insensitive. + For **clusterid**, enter ***your-cluster-identifier***. This is the name of the created Amazon Redshift cluster. + For **region**, enter ***your-cluster-region***. This is the AWS Region of the created Amazon Redshift cluster. + For **database**, enter ***your-database-name***. This is the name of the database that you're trying to access on the Amazon Redshift cluster. + For **locale**, enter **en-us**. This is the language that error messages display in. + For **iam**, enter **1**. This value specifies to the driver to authenticate using IAM credentials. + For **plugin\$1name** for Azure AD single sign-on configuration, enter **AzureAD**. This specifies to the driver to use Azure Single Sign-On as the authentication method. + For **plugin\$1name** for Azure AD single sign-on with MFA configuration, enter **BrowserAzureAD**. This specifies to the driver to use Azure Single Sign-On with MFA as the authentication method. + For **uid**, enter ***your-azure-username***. This is the user name of the Microsoft Azure account you are using for single sign-on that has permission to the cluster you are trying to authenticate against. Use this only for **plugin\$1name** is **AzureAD**. + For **pwd**, enter ***your-azure-password***. Use this only for **plugin\$1name** is **AzureAD**. + For **idp\$1tenant**, enter ***your-idp-tenant***. This is the tenant name of your company configured on your IdP (Azure). This value can either be the tenant name or the tenant unique ID with hyphens. + For **client\$1secret**, enter ***your-azure-redshift-application-client-secret***. This is the client secret of the Amazon Redshift application that you created when setting up your Azure single sign-on configuration. + For **client\$1id**, enter ***your-azure-redshift-application-client-id***. This is the client ID (with hyphens) of the Amazon Redshift application that you created when setting up your Azure single sign-on configuration. + For **listen\$1port**, enter ***your-listen-port***. This is the port that local server is listening to. The default is 7890. This applies to the Browser Azure AD plugin. + For **idp\$1response\$1timeout**, enter ***the-number-of-seconds***. This is the specified period of time in seconds to wait for response from Azure. This option applies to the Browser Azure AD plugin. On macOS and Linux, also edit the profile settings to add the following exports. ``` export ODBCINI=/opt/amazon/redshift/Setup/odbc.ini ``` ``` export ODBCINSTINI=/opt/amazon/redshift/Setup/odbcinst.ini ``` ------ ## Troubleshooting To troubleshoot issues with the Browser Azure AD plugin, consider the following. + To use the Browser Azure AD plugin, you must set the reply URL specified in the request to match the reply URL configured for your application. Navigate to the **Set up Single Sign-On with SAML** page on the Microsoft Azure portal. Then check the **Reply URL** is set to http://localhost/redshift/. + If you get an IdP tenant error, verify that the **IdP Tenant** name matches the domain name you initially used to set up the Active Directory in Microsoft Azure. On Windows, navigate to the **Connection Settings** section of the **Amazon Redshift ODBC DSN Setup** page. Then check the tenant name of your company configured on your IdP (Azure) matches the domain name you initially used to set up the Active Directory in Microsoft Azure. On macOS and Linux, find the *odbc.ini* file. Then check the tenant name of your company configured on your IdP (Azure) matches the domain name you initially used to set up the Active Directory in Microsoft Azure. + If you get an error that the reply URL specified in the request does not match the reply URLs configured for your application, verify that the **Redirect URIs** is the same as the reply URL. Navigate to the **App registration** page of your application on the Microsoft Azure portal. Then check the Redirect URIs matches the reply URL. + If you get the unexpected response: unauthorized error, verify that you completed the **Mobile and desktop applications** configuration. Navigate to the ** App registration** page of your application on the Microsoft Azure portal. Then navigate to **Authentication** and check that you configured **Mobile and desktop applications** to use http://localhost/redshift/ as the redirect URIs. # Ping Identity You can use Ping Identity as an identity provider (IdP) to access your Amazon Redshift cluster. This tutorial shows you how you can use Ping Identity as an identity provider (IdP) to access your Amazon Redshift cluster. ## Step 1: Set up Ping Identity and your AWS account to trust each other The following procedure describes how to set up a trust relationship using the PingOne portal. **To set up Ping Identity and your AWS account to trust each other** 1. Create or use an existing Amazon Redshift cluster for your Ping Identity users to connect to. To configure the connection, certain properties of this cluster are needed, such as the cluster identifier. For more information, see [Creating a Cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/create-cluster.html). 1. Add Amazon Redshift as a new SAML application on the PingOne portal. For detailed steps, see the [Ping Identity documentation](https://docs.pingidentity.com/). 1. Go to **My Applications**. 1. Under **Add Application**, choose **New SAML Application**. 1. For **Application Name**, enter **Amazon Redshift**. 1. For **Protocol Version**, choose **SAML v2.0**. 1. For **Category**, choose ***your-application-category***. 1. For **Assertion Consumer Service (ACS)**, type ***your-redshift-local-host-url***. This is the local host and port that the SAML assertion redirects to. 1. For **Entity ID**, enter `urn:amazon:webservices`. 1. For **Signing**, choose **Sign Assertion**. 1. In the **SSO Attribute Mapping** section, create the claims as shown in the following table. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/setup-identity-provider-ping.html) 1. For **Group Access**, set up the following group access, if needed: + **https://aws.amazon.com/SAML/Attributes/Role** + **https://aws.amazon.com/SAML/Attributes/RoleSessionName** + **https://redshift.amazon.com/SAML/Attributes/AutoCreate** + **https://redshift.amazon.com/SAML/Attributes/DbUser** 1. Review your setup and make changes, if necessary. 1. Use the **Initiate Single Sign-On (SSO) URL** as the login URL for the Browser SAML plugin. 1. Create an IAM SAML identity provider on the IAM console. The metadata document that you provide is the federation metadata XML file that you saved when you set up Ping Identity. For detailed steps, see [ Creating and Managing an IAM Identity Provider (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml.html#idp-manage-identityprovider-console) in the *IAM User Guide*. 1. Create an IAM role for SAML 2.0 federation on the IAM console. For detailed steps, see [ Creating a Role for SAML](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-idp_saml.html#idp_saml_Create) in the *IAM User Guide*. 1. Create an IAM policy that you can attach to the IAM role that you created for SAML 2.0 federation on the IAM console. For detailed steps, see [Creating IAM Policies (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html#access_policies_create-start) in the *IAM User Guide*. For an Azure AD example, see [Setting up JDBC or ODBC single sign-on authentication](setup-azure-ad-identity-provider.md). ## Step 2: Set up JDBC or ODBC for authentication to Ping Identity ------ #### [ JDBC ] **To set up JDBC for authentication to Ping Identity** + Configure your database client to connect to your cluster through JDBC using Ping Identity single sign-on. You can use any client that uses a JDBC driver to connect using Ping Identity single sign-on or use a language like Java to connect using a script. For installation and configuration information, see [Configuring a connection for JDBC driver version 2.x for Amazon Redshift](jdbc20-install.md). For example, you can use SQLWorkbench/J as the client. When you configure SQLWorkbench/J, the URL of your database uses the following format. ``` jdbc:redshift:iam://cluster-identifier:us-west-1/dev ``` If you use SQLWorkbench/J as the client, take the following steps: 1. Start SQL Workbench/J. In the **Select Connection Profile** page, add a **Profile Group**, for example **Ping**. 1. For **Connection Profile**, enter ***your-connection-profile-name***, for example **Ping**. 1. Choose **Manage Drivers**, and choose **Amazon Redshift**. Choose the **Open Folder** icon next to **Library**, then choose the appropriate JDBC .jar file. 1. On the **Select Connection Profile** page, add information to the connection profile as follows: + For **User**, enter your PingOne user name. This is the user name of the PingOne account that you are using for single sign-on that has permission to the cluster that you are trying to authenticate using. + For **Password**, enter your PingOne password. + For **Drivers**, choose **Amazon Redshift (com.amazon.redshift.jdbc.Driver)**. + For **URL**, enter **jdbc:redshift:iam://*your-cluster-identifier*:*your-cluster-region*/*your-database-name***. 1. Choose **Extended Properties** and do one of the following: + For **login\$1url**, enter ***your-ping-sso-login-url***. This value specifies to the URL to use single sign-on as the authentication to log in. + For Ping Identity, for **plugin\$1name**, enter **com.amazon.redshift.plugin.PingCredentialsProvider**. This value specifies to the driver to use Ping Identity single sign-on as the authentication method. + For Ping Identity with single sign-on, for **plugin\$1name**, enter **com.amazon.redshift.plugin.BrowserSamlCredentialsProvider**. This value specifies to the driver to use Ping Identity PingOne with single sign-on as the authentication method. ------ #### [ ODBC ] **To set up ODBC for authentication to Ping Identity** + Configure your database client to connect to your cluster through ODBC using Ping Identity PingOne single sign-on. Amazon Redshift provides ODBC drivers for Linux, Windows, and macOS operating systems. Before you install an ODBC driver, determine whether your SQL client tool is 32-bit or 64-bit. Install the ODBC driver that matches the requirements of your SQL client tool. On Windows, in the **Amazon Redshift ODBC Driver DSN Setup** page, under **Connection Settings**, enter the following information: + For **Data Source Name**, enter ***your-DSN***. This specifies the data source name used as the ODBC profile name. + For **Auth type**, do one of the following: + For Ping Identity configuration, choose **Identity Provider: Ping Federate**. This is the authentication method that the ODBC driver uses to authenticate using Ping Identity single sign-on. + For Ping Identity with single sign-on configuration, choose **Identity Provider: Browser SAML**. This is the authentication method that the ODBC driver uses to authenticate using Ping Identity with single sign-on. + For **Cluster ID**, enter ***your-cluster-identifier***. + For **Region**, enter ***your-cluster-region***. + For **Database**, enter ***your-database-name***. + For **User**, enter ***your-ping-username***. This is the user name for the PingOne account that you are using for single sign-on that has permission to the cluster that you're trying to authenticate using. Use this only for **Auth type** is **Identity Provider: PingFederate**. + For **Password**, enter ***your-ping-password***. Use this only for **Auth type** is **Identity Provider: PingFederate**. + For **Listen Port**, enter ***your-listen-port***. This is the port that local server is listening to. The default is 7890. This applies only to the Browser SAML plugin. + For **Response Timeout**, enter ***the-number-of-seconds***. This is the number of seconds to wait before timing out when the IdP server sends back a response. The minimum number of seconds must be 10. If establishing the connection takes longer than this threshold, then the connection is aborted. This applies only to the Browser SAML plugin. + For **Login URL**, enter ***your-login-url***. This applies only to the Browser SAML plugin. On macOS and Linux, edit the `odbc.ini` file as follows: **Note** All entries are case-insensitive. + For **clusterid**, enter ***your-cluster-identifier***. This is the name of the created Amazon Redshift cluster. + For **region**, enter ***your-cluster-region***. This is the AWS Region of the created Amazon Redshift cluster. + For **database**, enter ***your-database-name***. This is the name of the database that you're trying to access on the Amazon Redshift cluster. + For **locale**, enter **en-us**. This is the language that error messages display in. + For **iam**, enter **1**. This value specifies to the driver to authenticate using IAM credentials. + For **plugin\$1name**, do one of the following: + For Ping Identity configuration, enter **BrowserSAML**. This is the authentication method that the ODBC driver uses to authenticate to Ping Identity. + For Ping Identity with single sign-on configuration, enter **Ping**. This is the authentication method that the ODBC driver uses to authenticate using Ping Identity with single sign-on. + For **uid**, enter ***your-ping-username***. This is the user name of the Microsoft Azure account you are using for single sign-on that has permission to the cluster you are trying to authenticate against. Use this only for **plugin\$1name** is **Ping**. + For **pwd**, enter ***your-ping-password***. Use this only for **plugin\$1name** is **Ping**. + For **login\$1url**, enter ***your-login-url***. This is the Initiate single sign-on URL that returns the SAML Response. This applies only to the Browser SAML plugin. + For **idp\$1response\$1timeout**, enter ***the-number-of-seconds***. This is the specified period of time in seconds to wait for response from PingOne Identity. This applies only to the Browser SAML plugin. + For **listen\$1port**, enter ***your-listen-port***. This is the port that local server is listening to. The default is 7890. This applies only to the Browser SAML plugin. On macOS and Linux, also edit the profile settings to add the following exports. ``` export ODBCINI=/opt/amazon/redshift/Setup/odbc.ini ``` ``` export ODBCINSTINI=/opt/amazon/redshift/Setup/odbcinst.ini ``` ------ # Okta You can use Okta as an identity provider (IdP) to access your Amazon Redshift cluster. This tutorial shows you how you can use Okta as an identity provider (IdP) to access your Amazon Redshift cluster. ## Step 1: Set up Okta and your AWS account to trust each other The following procedure describes how to set up a trust relationship. **To set up Okta and your AWS account to trust each other** 1. Create or use an existing Amazon Redshift cluster for your Okta users to connect to. To configure the connection, certain properties of this cluster are needed, such as the cluster identifier. For more information, see [Creating a Cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/create-cluster.html). 1. Add Amazon Redshift as a new application on the Okta portal. For detailed steps, see the [Okta documentation](https://developer.okta.com/docs/). + Choose **Add Application**. + Under **Add Application**, choose **Create New App**. + On the **Create a New Add Application Integration** page, for **Platform**, choose **Web**. + For **Sign on method**, choose **SAML v2.0**. + On the **General Settings** page, for **App name**, enter ***your-redshift-saml-sso-name***. This is the name of your application. + On the **SAML Settings** page, for **Single sign on URL**, enter ***your-redshift-local-host-url***. This is the local host and port that the SAML assertion redirects to, for example `http://localhost:7890/redshift/`. 1. Use the **Single sign on URL** value as the **Recipient URL** and **Destination URL**. 1. For **Signing**, choose **Sign Assertion**. 1. For **Audience URI (SP Entity ID)**, enter **urn:amazon:webservices** for the claims, as shown in the following table. 1. In the **Advanced Settings** section, for **SAML Issuer ID**, enter ***your-Identity-Provider-Issuer-ID***, which you can find in the **View Setup Instructions** section. 1. In the **Attribute Statements** section, create the claims as shown in the following table. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/mgmt/setup-identity-provider-okta.html) 1. In the **App Embed Link** section, find the URL that you can use as the login URL for the Browser SAML plugin. 1. Create an IAM SAML identity provider on the IAM console. The metadata document that you provide is the federation metadata XML file that you saved when you set up Okta. For detailed steps, see [ Creating and Managing an IAM Identity Provider (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml.html#idp-manage-identityprovider-console) in the *IAM User Guide*. 1. Create an IAM role for SAML 2.0 federation on the IAM console. For detailed steps, see [ Creating a Role for SAML](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-idp_saml.html#idp_saml_Create) in the *IAM User Guide*. 1. Create an IAM policy that you can attach to the IAM role that you created for SAML 2.0 federation on the IAM console. For detailed steps, see [Creating IAM Policies (Console)](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html#access_policies_create-start) in the *IAM User Guide*. For an Azure AD example, see [Setting up JDBC or ODBC single sign-on authentication](setup-azure-ad-identity-provider.md). ## Step 2: Set up JDBC or ODBC for authentication to Okta ------ #### [ JDBC ] **To set up JDBC for authentication to Okta** + Configure your database client to connect to your cluster through JDBC using Okta single sign-on. You can use any client that uses a JDBC driver to connect using Okta single sign-on or use a language like Java to connect using a script. For installation and configuration information, see [Configuring a connection for JDBC driver version 2.x for Amazon Redshift](jdbc20-install.md). For example, you can use SQLWorkbench/J as the client. When you configure SQLWorkbench/J, the URL of your database uses the following format. ``` jdbc:redshift:iam://cluster-identifier:us-west-1/dev ``` If you use SQLWorkbench/J as the client, take the following steps: 1. Start SQL Workbench/J. In the **Select Connection Profile** page, add a **Profile Group**, for example **Okta**. 1. For **Connection Profile**, enter ***your-connection-profile-name***, for example **Okta**. 1. Choose **Manage Drivers**, and choose **Amazon Redshift**. Choose the **Open Folder** icon next to **Library**, then choose the appropriate JDBC .jar file. 1. On the **Select Connection Profile** page, add information to the connection profile as follows: + For **User**, enter your Okta user name. This is the user name of the Okta account that you are using for single sign-on that has permission to the cluster that you are trying to authenticate using. + For **Password**, enter your Okta password. + For **Drivers**, choose **Amazon Redshift (com.amazon.redshift.jdbc.Driver)**. + For **URL**, enter **jdbc:redshift:iam://*your-cluster-identifier*:*your-cluster-region*/*your-database-name***. 1. Choose **Extended Properties** and do one of the following: + For **login\$1url**, enter ***your-okta-sso-login-url***. This value specifies to the URL to use single sign-on as the authentication to log in to Okta. + For Okta single sign-on, for **plugin\$1name**, enter **com.amazon.redshift.plugin.OktaCredentialsProvider**. This value specifies to the driver to use Okta single sign-on as the authentication method. + For Okta single sign-on with MFA, for **plugin\$1name**, enter **com.amazon.redshift.plugin.BrowserSamlCredentialsProvider**. This value specifies to the driver to use Okta single sign-on with MFA as the authentication method. ------ #### [ ODBC ] **To set up ODBC for authentication to Okta** + Configure your database client to connect to your cluster through ODBC using Okta single sign-on. Amazon Redshift provides ODBC drivers for Linux, Windows, and macOS operating systems. Before you install an ODBC driver, determine whether your SQL client tool is 32-bit or 64-bit. Install the ODBC driver that matches the requirements of your SQL client tool. On Windows, in the **Amazon Redshift ODBC Driver DSN Setup** page, under **Connection Settings**, enter the following information: + For **Data Source Name**, enter ***your-DSN***. This specifies the data source name used as the ODBC profile name. + For **Auth type**, do one of the following: + For Okta single sign-on configuration, choose **Identity Provider: Okta**. This is the authentication method that the ODBC driver uses to authenticate using Okta single sign-on. + For Okta single sign-on with MFA configuration, choose **Identity Provider: Browser SAML**. This is the authentication method that the ODBC driver uses to authenticate using Okta single sign-on with MFA. + For **Cluster ID**, enter ***your-cluster-identifier***. + For **Region**, enter ***your-cluster-region***. + For **Database**, enter ***your-database-name***. + For **User**, enter ***your-okta-username***. This is the user name for the Okta account that you are using for single sign-on that has permission to the cluster that you're trying to authenticate using. Use this only for **Auth type** is **Identity Provider: Okta**. + For **Password**, enter ***your-okta-password***. Use this only for **Auth type** is **Identity Provider: Okta**. On macOS and Linux, edit the `odbc.ini` file as follows: **Note** All entries are case-insensitive. + For **clusterid**, enter ***your-cluster-identifier***. This is the name of the created Amazon Redshift cluster. + For **region**, enter ***your-cluster-region***. This is the AWS Region of the created Amazon Redshift cluster. + For **database**, enter ***your-database-name***. This is the name of the database that you're trying to access on the Amazon Redshift cluster. + For **locale**, enter **en-us**. This is the language that error messages display in. + For **iam**, enter **1**. This value specifies to the driver to authenticate using IAM credentials. + For **plugin\$1name**, do one of the following: + For Okta single sign-on with MFA configuration, enter **BrowserSAML**. This is the authentication method that the ODBC driver uses to authenticate to Okta single sign-on with MFA. + For Okta single sign-on configuration, enter **Okta**. This is the authentication method that the ODBC driver uses to authenticate using Okta single sign-on. + For **uid**, enter ***your-okta-username***. This is the user name of the Okta account you are using for single sign-on that has permission to the cluster you are trying to authenticate against. Use this only for **plugin\$1name** is **Okta**. + For **pwd**, enter ***your-okta-password***. Use this only for **plugin\$1name** is **Okta**. + For **login\$1url**, enter ***your-login-url***. This is the Initiate single sign-on URL that returns the SAML Response. This applies only to the Browser SAML plugin. + For **idp\$1response\$1timeout**, enter ***the-number-of-seconds***. This is the specified period of time in seconds to wait for response from PingOne. This applies only to the Browser SAML plugin. + For **listen\$1port**, enter ***your-listen-port***. This is the port that local server is listening to. The default is 7890. This applies only to the Browser SAML plugin. On macOS and Linux, also edit the profile settings to add the following exports. ``` export ODBCINI=/opt/amazon/redshift/Setup/odbc.ini ``` ``` export ODBCINSTINI=/opt/amazon/redshift/Setup/odbcinst.ini ``` ------