When it comes to data analysis and business intelligence, connecting to your database securely is a must. For those using Power BI to analyse data stored in a MySQL database, an SSH tunnel presents a secure method to establish this connection, especially when direct access might be restricted or considered too risky. This blog will tell you the steps to connect to MySQL database from Power BI using SSH tunnel, ensuring that your data transmission remains secure.
Table of Contents
Steps to connect MySQL database from Power BI using SSH Tunnel
First, make sure you have PuTTY installed on your computer. If you don’t, downloading and installing it is easy – just follow the steps in the installer. Once you’ve got PuTTY installed, you’re ready to create an SSH tunnel.
1. To start, open PuTTY. Look for a section called “Category,” then navigate to “Session.” Here, you’ll need to type in the name of your domain and the port number. Also, remember to choose SSH as the type of connection you want to make.
2. Next, navigate to Connection -> SSH -> AUTH in the menu and upload your private key file to authenticate your session.
3. For setting up the tunnel, proceed to SSH -> Tunnels. Here, you’ll need to input the local port you wish to forward and specify the remote address as the destination.
4. Ensure that both the ‘Local’ and ‘Auto’ options are selected, then click ‘Add’ to proceed.
5. Type in your username and press Enter. You should see a terminal window open, signaling that the connection has been successfully established.
After you’ve successfully established a connection, you can access the database from Power BI Desktop. Just go to ‘localhost:3306’ (use the local port number you specified in the earlier steps) and then enter the name of your database.
How to refresh data from Power BI service
Refreshing data from the Power BI service when you’re using an on-premise data source requires a few steps, since there isn’t a direct connection between the online reports and the database on your remote server. To enable data refresh, you’ll need to set up an on-premise data gateway. This gateway acts as a bridge, allowing your reports in Power BI service to get updated data from your on-premise database. Here’s a simplified guide on how to get started:
1. Download the On-Premise Data Gateway: First, you’ll need to download the on-premise data gateway installer. You can find it at the Power BI website or directly through this link: Power BI Gateway.
2. Install the Gateway: Run the installer you’ve downloaded. During installation, you’ll be presented with two options:
- Enterprise Mode: Choose this if you want the gateway to be shared and reused by multiple users. It’s suitable for a collaborative environment.
- Personal Mode: Select this for individual use. This mode ties the gateway to you and your Power BI account.
3. Set Up the Gateway in Power BI Service:
- After installation, head over to the Power BI service online.
- Navigate to Settings (you’ll find this in the top right corner) and then click on Manage Gateways.
- Here, you can create a new data source that connects to your on-premise database through the gateway you just installed.
4. Now, in Power BI service, go to Settings, then Datasets.
5. Find Gateway connection and match your dataset with the correct gateway connection you’ve set up.
Your data can now be updated directly through the Power BI service. To automate this process, enable the “Schedule refresh” feature and select the frequency of refreshes—either daily or weekly, based on your needs.
Conclusion
Setting up an SSH tunnel for connecting Power BI to a MySQL database adds an essential layer of security to your data analysis workflows. While the setup process might seem complex at first, the added security and peace of mind it offers are well worth the effort. Whether you’re analyzing sensitive financial data, customer information, or any other critical data, ensuring the secure transfer of data between your database and analysis tool is crucial in today’s digital landscape.
Remember, maintaining data security is an ongoing process that extends beyond the initial connection setup. Regularly review your security practices, keep your software up-to-date, and stay informed on the latest security threats and countermeasures to protect your valuable data assets effectively.
