Can't access MariaDB from workstation on green lan

Support
1

Greetings all,

I’m new to Nethserver (previously used SME)
I’m running NethServer release 7.9.2009 (final), fully patched through cockpit.
I’m running a local active directory on the server for authentication.

The problem I’m having, is that I can’t gain access to the inbuilt MySQL/MariaDB database from the local, green lan.

I can access it through the command line via the Nethserver cockpit, or via Putty.

	Welcome to the MariaDB monitor.  Commands end with ; or \g.
	Your MariaDB connection id is 1914
	Server version: 5.5.68-MariaDB MariaDB Server

	Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

The only information I can find, is to enable it on the green local network, which makes sense.
It’s showing the green zone under the service settings in cockpit, and in the config database.

[root@xxxx ~]# config show mysqld
mysqld=service
    LocalNetworkingOnly=no
    MaxAllowedPacket=16M
    TCPPort=3306
    access=green
    status=enabled

I’m attempting to connect to it on port 3306 via my workstation on the green lan, using my DB Editor of choice, DBeaver 21.2.1 (an excellent tool!)
The connection fails, with the message

Could not connect to HostAddress{host='neth.domainname.com', port=3306, type='master'}. 
Host 'machinename.domainname.com' is not allowed to connect to this MariaDB server
Host 'machinename.domainname.com' is not allowed to connect to this MariaDB server
Host 'machinename.domainname.com' is not allowed to connect to this MariaDB server

The mysqld_safe & mariadb-prepare-db-dir logs (all I can find related…) are empty.
I’ve tried the root and a user account, but at this point, I’m stumped. I’ve searched everything I can find in the forums, howtos, documentation and I’m getting nowhere.
I’m wondering if I need to authenticate against a group in AD that I know nothing about?

Is there something obvious I’m missing?

Thanks in advance for any assistance.

Don.

2

Hi and welcome to Nethserver Community.

Did you create a db user that is allowed to connect from remote?

You may use the phpmyadmin module to ease mariadb administration.

2 Likes
3

Hi Markus,

Thanks for the assistance. I’ve managed to get it working. I’ll detail it for those having the same issue.

The service was working correctly on the local LAN.
Turns out DBeaver was hitting the database on port 3306, but MySQL/MariaDB was rejecting the login as the user didn’t have access to the Users\Login Information\Host in PhpMyAdmin.

Steps to resolve.

Please read through to the bottom before attempting any of this. You can probably do this without installing PhpMyAdmin. But it’s probably still a good idea to do so.

  1. Check Nethserver System\Services\mysqld (MariaDB database server) has access to the green network and shows port TCP: 3306.
    If it doesn’t, click on the three vertical dots in the “action” column of the service, and choose “Edit” from the context menu.
    The port number seems hardcoded to the default of TCP 3306 on mine, so choose the “Access” dropdown and select the green network (trusted local LAN)
    You should now see “green x” in a green box below the access dropdown showing it’s been selected.
    Save by selecting “Edit” (This should probably be labelled “Save”?)

  2. Install PhpMyAdmin for a nice GUI to create database users.
    Install StephDL’s repository stephdl_repository [NethServer Wiki]
    Install PhpMyAdmin phpmyadmin [NethServer Wiki]

  3. Once PhpMyAdmin is installed, access it via the Nethserver web interface - Applications\phpMyAdmin click “Open” in the action column.
    This will open a new browser tab with a PhpMyAdmin login screen
    Login with username ‘admin’
    Password is located in /root/.my.cnf - display it with the following terminal command

    cat /root/.my.cnf

    You will see a line
    password=

    Copy and paste the password into the PhpMyAdmin password field on the login screen and select “Go”

    You should now be logged into PhpMyAdmin.

  4. Create a new user, and give them login premission from the local LAN.
    I’ve created a new user. I’d suggest that, rather than modifying an existing system account and breaking something.
    From PhpMyAdmin’s Top menu, select “Users”
    New\Add User

    This will take you to the “Add User” page.
    Create a new user.
    Give them a Username
    Give them a Password

     Now, the ***ALL IMPORTANT*** "Host" field.  This is where the magic happens!!!!!
 Options are;
 "Any Host" - (%) - Wildcard meaning any
 "Local" - localhost ONLY
 "Use Host Table" - stored in mysql.hosts table
 "Use Text Field:" - Enter any value

 Choose "Any Host" this will allow connections via the network for this user.
 The firewall is set to green only, so it won't be accessable outside on the local trusted LAN unless you add the red network zone in step 1.  (Not recommended!)

 For this test account, I chose options "Database for user" \ "Create database with same name" AND "grant all privileges."
 "Global privileges" - Check All.

 Select Go.

 You should see "You have added a new user." at the top of the screen and the SQL code required to do so.  (username & database name are the same for this test user)

 "CREATE USER 'username'@'%' IDENTIFIED BY '***';GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY '***' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;CREATE DATABASE IF NOT EXISTS `databasename`;GRANT ALL PRIVILEGES ON `databasename`.* TO 'username'@'%';

 You could probably use this SQL from MySQL / MariaDB in the Nethserver commandline to create the user and not bother with steps 2 & 3.  I've listed it here for completeness.

  5. Testing in DBeaver (optional database manager software I use) to access the server.
    Create a new database connection, of type MariaDB
    Enter the Server Host - Either the FQDN or IP address of the Nethserver
    Enter the Username for the database user you just created in PhpMyAdmin
    Enter the Password for the database user you just created in PhpMyAdmin

    Click on “Test Connection”

    It should Report “connected” and give the version numbers of the server & driver software.

Hopefully this helps anyone else trying to find the solution.

Don.

1 Like