Forum Discussion
Connect Powerbi with AWS RDS with no public accessibility
We're trying to integrate Power BI in our company. However, we're failing to connect with AWS services.
Currently, we use a MySql database hosted on AWS RDS, the problem is that, we don't use public accessibility, which reckon to say that this is the reason we always get the error: MySQL: Unable to connect to any of the specified MySQL hosts, whenever we try to connect it.
Could someone help us please? A step-by-step tutorial would be very much appreciated.
Thank You in advance.
By the way, AWS RedShift is not an option for us, since it adds a cost that we're not willing to pay for now.
16 Replies
Hi Anonymous
Here is a video you could refer to
AWS - Power BI to RDS MySQL Connection
What do you mean by "don't use public accessibility" and "AWS RedShift is not an option for us"?
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank v-juanli-msft for replying,
I tried this video before and it didn't work.
If you pay atention at 0:35 in the video you'll notice that Public Accessible is "Yes", which is not our case.
Also, AWS Redshift is quite expensive for us.
Thank you again, any tips about PowerBI is very welcome. I'm trying for a while but not getting anywhere with it.
Hi stsau ,
Just started my journey into AWS side; I am also came into same situation.
Interested to know how to connect to RDS (MySQL) on private subnet with Power BI for reporting.
Thank you,
Sri
Hi,
Just to confirm - it's not possible, without the gateway, to connect to a database with SSL? Maintaining/patching/making highyly available a gateway is our only option?
Are there any options at all for secure access? SQLServer? Postgres? MySQL? Redshift? Perhaps something running specifically in Azure rather than on-premise or another cloud?
Thank you
Has anyone found a way to do this?
We've same challenge where we want to build visuals using data from Amazon AWS RDS PostgreSQL. DB is not publically accessible (neither it should be), AWS RedShift is kind of overkill for us, therefore we're not clear what path forward we should take. There are some clues in https://community.powerbi.com/t5/Service/Power-BI-solution-architecture-with-AWS-RDS-database/td-p/1285812 and https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial.
We have been able to connect to our desktop to mysql server through ssh tunnel using putty. We also had bastion server installed as another security layer in between the final database.
Now the next hurdle, when we are pushing it to powerbi service. How to refresh the dataset.
Do we need to make use of gateway or something else.
Any lead would be appreciable.We have been able to connect to our desktop to mysql server through ssh tunnel using putty. We also had bastion server installed as another security layer in between the final database.
Now the next hurdle, when we are pushing it to powerbi service. How to refresh the dataset in powerBi service considering we have bastion security layer with vpn and ssh tunnel to connect to desktop.
Do we need to make use of gateway or something else.
Any lead would be appreciable.Yes - Create an EC2 Windows server instance and install & configure on-premise power bi gateway on same subnet you hosted the bastion server.
Optional: And in Power BI Desktop change hard-coded server name in mysql queries with newly created ServerName Parameter and re-publish the report.
Now in Power BI Service add new MySQL datasource (main servername) under Settings=> Manage gateways using with database user credentials.
After configuring the gateway you should able to schedule the refresh intervals.
If you want to connect to MySQL workbench on your local machine effectively - you can change hosts file under 'C:\Windows\System32\drivers\etc' with administrator rights
# localhost name resolution is handled within DNS itself.
# 127.0.0.1 localhost
# ::1 localhost127.0.0.1 localhost
127.0.0.1 xyz::mysql
127.0.0.2 localhost
127.0.0.2 abc::mysqlSSH Tunnel
ssh -L 3306:xyz::mysql:3306 -L 127.0.0.2:3306:abc::mysql:3306 [email protected]
MySQL Workbench - server connectionsHostname: 127.0.0.1; Port: 3306 for connecting to xyz::msql server
Hostname: 127.0.0.2; Port: 3306 for connecting to abc::msql server
Hope this helps.
Hi! How do I install and configure on-premise power bi gateway on same subnet I hosted the bastion server?
