MariaDB version 10 & NAS

BigBaz's profile image BigBaz posted 5 years ago in Creating a connection Permalink

Hi I have a NAS (Synology DS412) which has many applications which can be installed and used - one being MariaDB 10.

HeidiSQL says it can connect with MariaDB 10 but I can't get it to do this (see attached). Looking at the help link, the error seems to indicate that MariaDB will only accept an IP of 127.0.0.1 whereas my NAS is on the 192.168.0.xx IP range which home networks often use.

There are steps to take to resolve this but it means accessing some configuration files, which I've no idea how I can do that on my NAS (I've asked Synology about this).

Any thoughts as to how I can resolve this connectivity issue would be appreciated.

1 attachment(s):
  • MariaDBNASIssue
ansgar's profile image ansgar posted 5 years ago Permalink

You need to connect to the MariaDB instance once and change that root account, to make it allow IPs other than 127.0.0.1. I suppose Synology also provides some client for MariaDB, maybe a web based client?

BigBaz's profile image BigBaz posted 5 years ago Permalink

Yes, it provides phpMyAdmin (on the NAS) which works fine. It's just that I need to get into the NAS to use phpMyAdmin and I'd prefer using HeidiSQL as I also connect to several other MSSQL database elsewhere.

The Synology help people are suggesting the issue is with HeidiSQL method of connecting, which is very unhelpful as it's clear to me MariaDB is the problem, restricting access to 127.0.0.1 IP initially. And they haven't givem me a clue as to access the MariaDB configuration files.

ansgar's profile image ansgar posted 5 years ago Permalink

Ok, in phpMyAdmin, you should be able to run a custom query, so you could create a new user like this:

CREATE USER 'root'@'192.168.%' IDENTIFIED BY 'yOurPassW0rd';
GRANT EVENT, DROP, DELETE, CREATE VIEW, CREATE TEMPORARY TABLES, CREATE TABLESPACE,
  CREATE ROUTINE, CREATE, ALTER ROUTINE, ALTER, SHOW VIEW, SHOW DATABASES, SELECT,
  PROCESS, EXECUTE  ON *.* TO 'root'@'192.168.%';
FLUSH PRIVILEGES;
BigBaz's profile image BigBaz posted 5 years ago Permalink

Hi Thanks for this I'll give this a go. However, Synology came back with something similar, creating a new User from phpMyAdmin using the UI, which seemed to go OK. So I now have a new User with Host set as 192.168.0.12 (see attached screenshot). However, when, in HeidiSQL, I press Open I get the error message shown - which is saying Host 192.168.0.28 is not allowed....! Where does it get .28 from when the IP named is .12? Very Odd - HeidiSQL seems to have an incorrect IP address it picks up. I've closed down and opened it again several times and no change.

1 attachment(s):
  • NASMariaDBIssue
ansgar's profile image ansgar posted 5 years ago Permalink

Where does it get .28 from when the IP named is .12

The .28 is yours, from the client side. The .12 is on the server side.

So I now have a new User with Host set as 192.168.0.12

This is wrong, you have to set the account's ip to 192.168.0.28, or to wildcard 192.168.%.

BigBaz's profile image BigBaz posted 5 years ago Permalink

Worked a treat!

Many thanks for your help, great support!

Simon24's profile image Simon24 posted 6 months ago Permalink

Nice thread. May I attach to it? This is actually exactly the same issue that I'm currently facing.

Same situation:

  • MariaDB and phpMyAdmin on my Synology
  • HeidiSQL on my PC

I have no access from HeidiSQL with none of the users (root, Rezept_DB, Simons_Bikeservice) though their host is set with wildcard.

Description

I'm wondering if this is related to the host adress stated in clear text in the error window ('root'@'Desktop-PC.fritz.box' Can anybody give me a hint what I missed to setup properly?

Thanks for any ideas.

Best Regards Simon

ansgar's profile image ansgar posted 6 months ago Permalink

Note your both users with "%" host don't have global privileges. I cannot see if they have database privileges, but if those are missing, then this is likely the problem.

The "root" user with host "198.168.%" probably doesn't match because the server is resolving IP addresses. Not sure. You may create (with phpMyAdmin) another "root" user with host set to "Desktop-PC.fritz.box" to test that. Alternatively, change the ip wildcard "198.168.%" to "%".

Simon24's profile image Simon24 posted 6 months ago Permalink

Thanks for the hint.

Yes that's right. The specific users have not global privileges, only specific ones in their repsonding databases.

The root user's host was set to work only in internal environment. However, it doesn't solve the issue when host is set to '%' or even to 'Desktop-PC.fritz.box': Description

Now I have set a user root defined for 3 hosts and none of them will allow the connection. Is there any other setting that can deny the connection?

ansgar's profile image ansgar posted 6 months ago Permalink

Could it be you just provide a wrong password in the connection? That would at least give exactly that "Access denied" error.

Simon24's profile image Simon24 posted 6 months ago Permalink

Thank you very much! That was the right hint.

But not, that I wrote it wrong. This was quite unlikely as I took it all the time from my Password Manager and pasted it into the field - from creating the user up to HeidiSQL to connect it.

The password was so safe with such specical signs, that I assume it wasn't transferred or interpreted correctly ('\x"^"0#ikI.w'). When I now changed it to a simpler version it worked.

Thanks again, you safed my Sunday. :-)

Best Regards Simon

Please login to leave a reply, or register at first.