When connecting Microsoft Excel, PowerBI Desktop, VBA, Microsoft Report Builder, Virtual Forms for Excel, or other applications that are installed on a user machine to a remote MySQL database we need to open the remote server, where our MySQL database is, to allow remote connections.
Because we need to connect to our MySQL database that is hosted on a remote MySQL server, we need to allow remote access to this server in our cPanel account or create a secured SSH Tunnel to the server.
Login to your cPanel account and go to Remote MySQL
First, we need to login into our cPanel account. To do this, go to the email that our system has sent to you and it is having the subject “New cPanel Account Information”.
- Click on the Control Panel URL
- Type in the Username and Password that is also specified in this email in the section “cPanel Login Details”
- Find the “DATABASES” section and click on “Remote MySQL“
Now that we are in the Remote MySQL, there are currently 3 scenarios we can use to allow remote MySQL access to our MySQL server in cPanel.
- Allow access to all connections (unsecured)
- Allow access to an IP or IP ranges (less secure)
- Use the SSH Tunnel (secured)
Note! Don’t delete the IP address that is already present in Remote MySQL.
Allow access to all connections
If you are connecting from a device that is having a dynamic IP, the IP of the device is changing from time to time, mostly once in 24 hours then one of the solutions could be to allow access to all connections.
To find out what is your device public IP, you can see on a webpage like https://whatismyipaddress.com/
To allow all connections to connect to your MySQL server you can use the wildcard % in the Remote MySQL.
- Log in to your cPanel.
- Scroll down to the Databases section.
- Click on the Remote MySQL icon.
- Enter %
- Click the Add Host button.
Warning! Using the wildcard % to allow access to all IP-s is not secure
Allow access to an IP or IP ranges
If you know that you have a static IP on your device or if you know that only a part of the IP is changing, you can add your static IP address to the list or use the wildcard % for the part of your IP that is changing.
Use the SSH Tunnel
To securely connect to your server you can create a so-called “SSH Tunnel”. In that case, you will use the “localhost” as your Server Name.
First, make sure that your account supports Shell Access (SSH).
You can use a small application called PuTTY to establish a secure connection to your server. You can download this free application from the homepage of the PuTTY project here: https://www.putty.org/
There are plenty of articles on the internet on how to configure PuTTY to connect to cPanel.