Trouble connecting to Redshift

[Anonymous]

Hi,

I've created a report in Power BI Desktop with a connection to Redshift and published it to My Workspace.  I then went to Settings to configure a refresh.  Under Data Source Credentials, I get an error stating "Your datasource can't be refreshed because the credentials are invalid".  I click "Edit Credentials" and re-enter the same credentials I used to connect via Desktop.  After running for a moment, I get a message stating that there was an "error while trying to connect to the server, Permission Denied, Is the server running on host 'X' and accepting TCP/IP connections on port 'Y'?"  Any idea why this might be happening?

[v-yingjl · Accepted solution]

Hi @Anonymous ,
Based on the error message, it is an issue with permission to access the cluster.

To connect to the cluster from a client tool outside of the network that the cluster is in, add an ingress rule. Add the rule to the cluster security group for the CIDR/IP that you are connecting from:

• If you created your Amazon Redshift cluster in a virtual private cloud (VPC) based on Amazon VPC, add your client CIDR/IP address to the VPC security group in Amazon VPC. For more information about configuring VPC security groups for your cluster, see Managing clusters in a VPC .

• If you created your Amazon Redshift cluster outside a VPC, add your client CIDR/IP address to the cluster security group in Amazon Redshift. For more information about configuring cluster security groups, see Amazon Redshift cluster security groups.

If you attempt to connect to the cluster from a client tool in an Amazon EC2 instance, you also add an ingress rule. In this case, add the rule to the cluster security group for the Amazon EC2 security group that is associated with the Amazon EC2 instance. For more information about configuring cluster security groups, see Amazon Redshift cluster security groups.

In some cases, you might have a layer between your client and server, such as a firewall. In these cases, make sure that the firewall accepts inbound connections over the port that you configured for your cluster.

If not help, you can attempt to connect once then go to File==>Options and Setting ==>Data Source Settings select the Redshift connection then click Edit Permissions Change uncheck the encryption and change the privacy level to Public

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl can I ask more about setting up Redshift as a dataset? I was told that we would need a dataflow to connect Redshift to Power BI Service, but it seems from this thread that this isn't the case? 

[GilbertQ]

Hi there

Can you make sure that your Redshift server can accept connections from the Power BI Service?

I would suggest installing and configuring a Gateway in order to refresh the data from Redshift

[Anonymous]

@GilbertQ Hello! Can I ask why you think it would be better to setup a gateway? I thought one of the benefits of using Redshift was that you don't need to set up a gateway. 

[GilbertQ]

Hi @Anonymous 

Typically a redshift server is not open to the internet, so using the gateway ensures that your server is safe and secure.