Hello! I need to connect MySQL to QuickSight as a new data source. I keep getting errors and have a feeling it is an IP issue as I have not added an AWS IP to my security on the database side. How do I find the IP for US east region 1 (or the correct IP). If this is not the case, what else could be the issue? I have SSL enabled so should not be an issue.
Thanks!
Hi @Tobias - Welcome to AWS QuickSight community and thanks for posting the question. To connect MySQL to QuickSight follow the below steps.
- Update the RDS security group and allow the incoming traffic from QuickSight security group on port 3306. This way you do not need to add any specific IP address.
- Add the VPC connection in Quick sights , ideal way add the VPC and subnets where MYSQL is there.
- Create a new connection, select the VPC, give the MYSQL ip address ( if you are using AWS RDS MySQL give the end point). Give the user name and password, uncheck the SSL and validate it.
- The connection should work.
If you are facing any error, please share the error details.
Regards - Sanjeeb
Hello! Thanks for the help but I am still having errors:
I created a security group and an inbound rule, but used a different port that was specified in by my (non-AWS) hosted database. I used custom TCP and “my IP” but was still not able to connect. I am not sure the issue and do not understand you 2nd step as I am not sure how to add a VPC connection to QuickSight, nor select the VPC when adding a connection. The error I am getting is as follows:
| region: | us-east-1 |
|---|---|
| timestamp: | 1687048354608 |
| requestId: | 325fcce8-88d5-42cb-9e5d-d643f2813d71 |
| sourceErrorCode: | SQL_CONNECTION_FAILURE |
| sourceErrorMessage: | Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. |
Hello - please disregard first message:
I was able to create a security group etc as mentioned in you step 1. During step 2, adding the vpc from the console to Quicksight I have some errors. Firstly none of the security group, VPC, or subnet names/ids populate the quicksight dropdowns so I am unable to impute the right information. Also when I do try and submit my VPC on quicksight I get this error “The role provided is unauthorized to perform the required actions” which makes so sense as I am the admin of my quicksight. Thanks!
@Tobias I know your problem, But not sure how I explain this, But I work with an expert AWS devops, who can put light on this problem.
@saul Can you please share your thought?
@Tobias you need to create a role with the required policies for this, let me give you some steps.
1.- Create a role “your-quicksight-role” with the following policies attached:
- “AWSQuickSightIAMPolicy”
- “AWSQuickSightRDSPolicy”
You need to specify this role while creating the VPC
2.- Create security rules:
You need 2 security rules for this,
2a.- Create a security rule “quicksight-SG” with inbound to all tcp, port range 0 - 65535, source the sg where you have your mysql port, this can be done a several multiple ways so it all depends on how you have it.
2b.- In your mysql sg add inbound rule with all tcp, port range 0 - 65535, source to “quicksight-SG”
3.- identify the subnet where your RDS is connected to. you need 2 subnets when you create RDS and EC2 instances. you need to specify the zone and id while creating the VPC
4.- to populate the subnets, you need to select the VPC id, this is your main VPC not quicksight.
Hope this helps you out !
1 Like
Thanks so much! I got lost trying to create a new role earlier. For step one, creating a role, is it “AWS services” or “AWS account” etc. Also to clarify in case this changes things, I am connecting QuickSight to a third party hosted mysql db, not one hosted with RDS or AWS in general. Thanks!
I went ahead and specified the account type role with the policies you outlined in step 1. For step 2a I created the security group and added the inbound rule with ip set to my current IP. I am not sure I understand 2b as in 2a I thought I already added an inbound rule with those specifications.
I was able to create both the inbound rules, with the second one using the SG as the source. If you could explain a in a little more detail step three and four that would be great - I have not done this in the past and its confusing. Adding a VPC in quicksight still does not let me select my correct VPC and ids etc. Thank you!
@Tobias , I thought you had an RDS instance, in your case you are trying to connect from a database hosted in outside hosting service. at the moment quicksight cant connect to abc.com:3306, at least not working for me, as a solution you would have 3 options:
- move your database to AWS RDS.
- create a vpn connection (advanced vpc)
- upload data dumps and instead of creating a VPC in quicksight, just create a datasource with this info.
Once quicksight enables public connections, all you would need is create a datasource with your credentials.
1 Like
@saul how would I go about creating a VPN connection? I would really like to use quicksight if possible. Thanks
Hi, Tobias.
I encountered the same issue as yours. My tech colleagues told me to we need to add quick sight IP into the white list, so that we can use quick sight to connect MySQL database. But now, I also have no idea about the quick sight IP.
Hope quick sight team members can offer the IP to us.
Hi @Bonnie_Bai - Welcome to AWS QuickSight Community and thanks for posting the question. Please find the IP address of QuickSight for regions in the below link.
Regards - Sanjeeb
Hi, Sanjeeb.
Thanks for your reply! I found the IP according the link you mentioned, but quick sight reported a new error. I am planning to join quick sight team OH to solve it.
BR
Bonnie
1 Like
Thank you @Bonnie_Bai . Once your issue will get resolve, please share the details to the community so that it can help others down the line.
Regards - Sanjeeb