Connect Powerbi with AWS RDS with no public accessibility

[Anonymous]

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.

[yogi128]

Hi Does anyone found answer about how to connect RDS Mysql to Power BI.

Note: RDS mysql DB public visibility is - NO

[psdhillon1]

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.  

[Anonymous]

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 localhost

127.0.0.1 localhost
127.0.0.1 xyz::mysql
127.0.0.2 localhost
127.0.0.2 abc::mysql

SSH Tunnel

ssh -L 3306:xyz::mysql:3306 -L 127.0.0.2:3306:abc::mysql:3306 ec2-user@domain.com

MySQL Workbench - server connections

Hostname: 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.

[cauecaviglioni]

Hi! How do I install and configure on-premise power bi gateway on same subnet I hosted the bastion server?

[psdhillon1]

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.  

[Anonymous]

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/1... and https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial. 

[CLWendt88]

Has anyone found a way to do this? 

[v-juanli-msft]

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
Maggie

Community 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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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  

[raymond]

We are having the same issue. It seems as if you need an VPN Access and something called Bastion which is installed on one EC2 instance. With a tool putty you can have access to your DB. With the VPN (we want to use Open VPN) you might get a connection via powerBi to the DB. 

I havent tested it because switching the DB in AWS to private cases so much trouble. We have just let them to public. 

If you have any more information about the succcess of your approach, please let me know. It seems to me as if microsoft deliberatly doesnt want to support aws products. 

[psdhillon1]

Hey @raymond I have got dual securtiy layer the way you mentioned. Bastion server with vpn access. I am trying out with putty, with which i am able to connect & access the database, but my powerbi still not able to see the database, what am i missing ? any clue would be appreciated. 

[Anonymous]

Hi, 

I confirm that RDS in public subnet, we can able to connet this source from differnet tools with correct credentials. How can we tell to Power BI server to use SSL while connectiong to RDS MySql. 

Thank you,

Sri

[raymond]

If any one of you finds a way please let me know. So far we werent able to do it. However we use the public subnet and whitelistet IP Ranges...that worked for us a security measure even though it is not that bullet proof.

[Anonymous]

Only way I have found is to open an ssh tunnel to a box in the VPC and bind your local port the that of the RDS instance itself, kinda like:

ssh -L 3306:<rds.amazonaws.com-custom-url>:3306 <ip-of-vpc-box>