External Client connecting to Internal Redshift Serverless Data Share with PowerBI

[icassiem]

Good day,

We are trying to implement data share moving from API OAuth2 to Redshift Serverless Data Sharing

1. Is this possible for a external user/client to access aws redshift serverless from powerbi?

2. Would the external client require a gateway?

3. Would the external + internal client require an ODBC installation to connect to Redshift Serverless?

Please help, any thoughts/links/examples on how to achieve data sharing via Redshift Serverless with PowerBI

Regards

[tharunkumarRTK · Accepted solution]

Hi @icassiem ,

I guess you are looking for redshift conectivity in Power BI. 

Any user with appropriate database credentials can connect to redshift database.

If the database is protected with firewall then On premises data gateway connection is required for Power BI service and appropriate VPN configuration is required to connect from Power BI desktop.

Power BI desktop and service natively support redshift database. However you can use ODBC connection as well. 
(Microsoft recommends native connector over ODBC). 

Please refer to this page for more information: https://learn.microsoft.com/en-us/power-query/connectors/amazon-redshift

I hope I answered your question. 

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[icassiem]

Hi, @tharunkumarRTK 

Apologies, Please Help

The Basic Auth has been rebuffed.

I want to use Native, how do i use DB Credentials with Service SSO?

Would this also be Basic or ?

Power Query Amazon Redshift connector - Power Query | Microsoft Learn

Microsoft Entra ID Single Sign-On (SSO) through Power BI service

Im trying to use Native Connector to Redshift, but we cant expect clients to have a server/ec2 for an on-prem gateway

[tharunkumarRTK]

@icassiem 

If you want to configure SSO then there are few limitations that you should note

1. it only works with direct query data model

2. I guess SSO works with Microsoft AAD (Entra) based authentication only but not with basic auth.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[icassiem]

@tharunkumarRTK 

I dont know what to do 😞 

i cant use Basic but SSO and need to use Native to Serverless as clients dont have server/ec2 for onprem-gateway

Please what's my best options, what would you do?

[icassiem]

Thank You very much @tharunkumarRTK 

this what i was looking for, please clarify below:

1. Power BI desktop and service natively connectivity, i am not sure it's possible for Redshift Serverless?

2. The firewall, would this be internally set on Redshift Serverless DB or clients firewall, if internal can it be dropped or configured how?

[tharunkumarRTK]

@icassiem 

1. Power BI desktop and service natively connectivity, i am not sure it's possible for Redshift Serverless?

I believe redshift native connector in power bi does  support serverless db as well.  However if you want to connect with your redshift profile then it is not possible  with native connector..
https://medium.com/@dtyshecky/powerbi-connectivity-with-redshift-serverless-using-odbc-90f2e93e69a3

2. The firewall, would that be internally set on Redshift Serverless or clients firewall?

internall set from the AWS end.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[icassiem]

Apologies, im lost on

Point1.  i dont have redshift serverless setup as we planning.

Is it possible or must the clients & internal connect via ODBC > can you test/clarify please, i dont wanyt to use ODBC expecially with clients

Point2. Can the firewall be dropped or configured to allow, how & risks?

3. With Auth Basic Redshift credentials on Desktop, when deploy to service what would the datasource connection properties be?

    A. an online gateway to the redshift url

    B. or is an on-pren gateway for the client need to be configured "which is not an option")

Is the below the datasource properties and i just ignore the gateway seletcion option?

[tharunkumarRTK]

Point1.  i dont have redshift serverless setup as we planning.

Is it possible or must the clients & internal connect via ODBC > can you test/clarify please, i dont wanyt to use ODBC expecially with clients

As I said, you can use the native connector if you dont want to use ODBC setup. The limitation with native connector is that you can use AWS profile login. 

 Point2. Can the firewall be dropped or configured to allow, how & risks?

This depends on the standard protocols that your clients follow. Generally large organisations protect their systems with firewall.

3. With Auth Basic Redshift credentials on Desktop, when deploy to service what would the datasource connection properties be?

You can use the same authentication method both in the service and in the desktop. The only point that is to be noted here is:

if the firewall rules are enabled then you need to first setup the on premises data gateway and configure the gateway connection in power bi service and then map the dataset to the newly created connection.

else you need to create a cloud connection in power bi service and map the dataset to the cloud connection.

The image you attached above is to configure a cloud/on-prem connection. For firewall enabled instances, select the gateway and then enter the credentails and complete the connection setup. For normal instances, you can keep the gateway as 'none'.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[icassiem]

Thank You

With native connector, can i use IAM Role credentials?

If firewall is dropped i use the same method of AWS IAM credentials on desktop and serverless correct? else create a cloud connection in power bi service (How)?

[tharunkumarRTK]

No, with native you cannot use AWS IAM. You should use basic/db authentication crendeitnals. With ODBC you can use AWS iAM but there few limitations, kindly refer this article 
https://medium.com/@dtyshecky/powerbi-connectivity-with-redshift-serverless-using-odbc-90f2e93e69a3

[icassiem]

Hi, @tharunkumarRTK 

Apologies, Please Help

The Basic Auth has been rebuffed. I want to use Native, how do i use DB Credentials with Service SSO? Would this also be Basic or ?

Power Query Amazon Redshift connector - Power Query | Microsoft Learn

Microsoft Entra ID Single Sign-On (SSO) through Power BI service

[icassiem]

with the ODBC/JDBC in dektop, then an on-prem gateway is required for service?

[tharunkumarRTK]

@icassiem 

Yes, for ODBC connections on prem gateway is required.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[icassiem]

Thank You for all your Help, PatIiance & Knowledge Sharing.

Much Appreciated 🙂