How to connect an ODBC data source to Redshift through an Azure SSO identity provider?

Question

We've setup an Azure identity provider to allow our users to access a Redshift data warehouse using their Entra identities using SSO. We've been able to configure a JDBC driver so that SQL administration programs like DBeaver can access the data warehouse the same way.

However, I'm having trouble setting up an ODBC data source along the same lines. I keep getting a `Connection Error : No role found in SAML assertion. I assume this means that I'm missing something in the Azure hop of the communications but I'm not entirely sure.

I've set the IdP tenant, client and scope. I've also provided the endpoint for the Redshift cluster and the database name. Additionally, the role we're using is associated with a group called Redshift_Access on AAD, which maps to azure_aad.Redshift_Access inside Redshift.

Does anyone have a potential solution for this? I know it's about four separate technologies trying to talk to each other but the fact that our JDBC driver "just works" gives me some hope here.

Answer 1

Use ODBC version 2 driver (Version 1 has connection issues). Set the drop down to browserazureoauth2 and auth is none. Then set the properties just like you would the jdbc connection in datagrip.

jdbc:redshift://HOSTNAME:5439/DBNAME?plugin_name=com.amazon.redshift.plugin.BrowserAzureOAuth2CredentialsProvider&listen_port=7890&idp_response_timeout=50&scope=api://AZURECLIENTID/jdbc_login&client_id= AZURECLIENTID&idp_tenant=AZURETENANTID

Do not attempt “Browser IdC Auth Plugin” with identity provider type AWSIDC, even if u have azure auto provisioning to iam identity center, you still have to jump through so many hoops just to get it to work, and once you do every new browser tab popup asks to approve permissions instead of just letting you in… Half baked not ready yet.

NOTE: Check out “Alter Identity Provider,” they added filtering so you can limit it to auto create roles for just the Azure groups you want ported (only one filter currently supported). No more hundreds of garbage roles!