Connect Power Query to wordpress mariaDB SQL database

[gazmoz17]

Hi,

I've downloaded the connector however struggling to connect it correctly when clicking Test DSN.

I'm guessing the details I'm entering here should be username & password for my database?

Do I need to amend anything on cpanel to enable a connection?

Many thans

 

[smozgur]

Is your database allowed to be accessed remotely? The hosting Control Panel (CP) might (or not) allow you to change the database access to be remote on your server. Then you should be able to get the database connection IP and port from the CP (likely the URL of the domain that is hosting your database).

Note: I removed the server name, port, and user name from your image. Please do not share sensitive information in your questions.

 

[gazmoz17]

Hi smozgur. Thanks for taking the time there and removing the sensitive data

Ive been given the logon details by our web developer to cpanel. Is there a way to check within cpanel if remote access is enabled?

Thought this would be a quick job linking power query to my wordpress products to set up an excel control.

Thanks

 

[smozgur]

Actually, from the port number in your initial post, I can tell you that it is the port number for CP access. So, I don't think it is for database connection.

As the default setting, by the way, the port number is supposed to be 3306 for MySQL (or MariaDB) database server. Yes, it could be changed for security purposes, but the port number that you posted is surely for the admin panel.

I believe, your developer gave you the CP credentials, so you can log in to the panel, get to the Databases section, find your database, and set/get options/instructions from there. Just put the port number right after the domain name (like - domain.com:1234) in a browser address field, and you'll get to the panel. Then I would use the username and password there.

 

[smozgur]

Additional note: I am not using CP but found a good article about allowing remote database on the server:

How To Use A Remote MySQL® Database With cPanel | cPanel Blog

Learn how to host website and ecommerce data in remote MySQL databases managed by cPanel & WHM. We take a deep dive into remote MySQL and MySQL profiles.

blog.cpanel.com

I would ask for administrator (or developer) help if you are dealing with a production database.

 

[gazmoz17]

Hi, thanks for the link. Link looks thorough but unfortunately I don't have the option Remote My SQL in Cpanel. I've tried looking for articles how to add that option but no joy.

 

[smozgur]

When you get to the database settings and select that particular database, there is no option for remote access or any kind of remote connection parameters?

Or, when you create a new database in the admin panel, does it ask you to make it localhost or remote access?

Final question: You mentioned WordPress in the title. Can you take a look at the WP config file (it should be a PHP file in the root, called wp-config.php), and see the database connection parameters? Like server name, port, password? Maybe the database is already allowed for remote access but you are just using the wrong credentials.

Also, note please (after you got the remote connection issue is solved and if you still get the handshake error): The connector that you installed on your computer should be compatible with the MariaDB version on the database. I am not on a Windows computer at the moment to verify that, however, let's say you have MariaDB version X installed on the server, but the connector only supports the Y version and later. Just wanted to mention this too.

 

[smozgur]

Just as a note: Please do not share the wp-config.php information on the board