Gateway - PBI Services Connection Issue

[saimnaveed]

Hi,

I have a private redshift cluster and I want to connect it with semantic models on pbi services. For the connecting semantic model on pbi services to redshift cluster I have configured an aws ec2 instance in the same vpc in which redshift cluister resides and I have configured pbi data gateway on it. I am able to query redshift db table using commands in cmd of that ec2 instance. pbi gateway is visible in pbi services and its status is online
Problem which I am facing is that when i create a new connection under that gateway using endpoint of redshift cluster, connection becomes visible in list of connections and shows status of online but it doesnot become available to choose in the list of connections to map uder gateway connections setting of any semantic model even after waiting for more than 1-2 days so i am unable to connect to redshift cluster using gateway and enpoint of redshift cluster but when i create a connection under that same gateway using enpoint of network load balancer, it gets created and becomes available to choose in the gateway connection settings.
I have checked all settings of ec2 instance esp security group and others and they all seem fine.
Please let me know how to solve this issue so that i can connect to redshift cluster through pbi gateway in pbi services using redshift endpoint otherwise configuring gateway in the vpc of redshift cluster seems useless exercise.

Let me know you need some other info

[v-nmadadi-msft]

Hi @saimnaveed ,
We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation

[v-nmadadi-msft]

Hi @saimnaveed,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[saimnaveed]

I am able to add data source connection under the configured gateway and its added after doing a connection test but any of that connection using redshift cluster endpoint is not available in the list of available data source connections in the settings of semantic model.
Secondly I can query redshift db from ec2 isntance.
So uptill now problem is not resolved

[v-nmadadi-msft]

Hi @saimnaveed ,

Can you please verify if Your account is listed in the Users tab of the data source within the gateway configuration. If not done you need to be added to that list by the administrator of the gateway.
Also please try to create a new gateway connection and check if the issue still persists.

Thanks and Regards

[saimnaveed]

I have configured pbi gatewayso i am its administrator and i have created the data source connection so i am inlist of ldata source users. Problem is that I am able to create data source connection(using prinvate redshift cluster endpoint) but that connection never appears in the list of connections available to select in the settings of any semantic model so i am unable to use it. I have also tried configuring odbc based data source connection but it has the similar problem.
Can somebody from pbi support or development team respond on this query?

[v-nmadadi-msft · Accepted solution]

Hi @saimnaveed,
Sorry to know it didn’t help. Please consider reaching out to Microsoft Support. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious. 

Below is the link to create Microsoft Support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you

[bhanu_gautam]

@saimnaveed 

Here are a few steps and considerations that might help resolve the issue:

Network Configuration: Ensure that the security group of your Redshift cluster allows inbound traffic from the EC2 instance where the Power BI gateway is installed. This includes allowing the necessary port (default is 5439 for Redshift) and the correct IP range.

Gateway Configuration: Verify that the Power BI gateway is correctly configured to handle the Redshift connection. Since the connection works with a network load balancer endpoint but not directly with the Redshift endpoint, there might be a network routing or DNS resolution issue.

DNS Resolution: Ensure that the EC2 instance can resolve the DNS name of the Redshift cluster. You can test this by running a simple nslookup or ping command from the EC2 instance to the Redshift endpoint.

Power BI Service: Sometimes, Power BI service might take some time to reflect new connections. However, waiting for 1-2 days is unusual. Ensure that the Power BI service is updated and there are no pending updates or issues on the Power BI service side.

Connection String: Double-check the connection string and credentials used for the Redshift connection. Ensure that they are correct and have the necessary permissions.

Logs and Diagnostics: Check the logs on the Power BI gateway for any errors or warnings that might give more insight into why the connection is not becoming available. The logs can often provide detailed information about what might be going wrong.

Alternative Approach: As a workaround, since the connection works with the network load balancer endpoint, you might consider using this setup if it meets your requirements. Ensure that the load balancer is properly configured to handle the traffic and that it does not introduce any latency or performance issues.

[saimnaveed]

dns settings of vpc are enabled, security group of ec2 has all relevant rules, redshift sg allow inbound traffic from vpc, tried using ping and nslookup on ec2 instance on which pbi gateway is installed and in both ways it was not able to resolve the redshift cluster endpoint.
Whats the reasons for this anamolous behavior and how it can be resolved effectively?

[v-nmadadi-msft]

Hi  @saimnaveed,

A few different scenarios could be responsible for this error:

• The server and database name don't match what was entered in Power BI Desktop and the data source configured for the gateway. These names must be the same. They aren't case sensitive.
• Your account isn't listed in the Users tab of the data source within the gateway configuration. You need to be added to that list by the administrator of the gateway.
• Your Power BI Desktop file has multiple data sources within it, and not all of those data sources are configured with the gateway. You need to have each data source defined with the gateway for the gateway to show up within  semantic model.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards