MySQL Access Denied (Password 'NO')

Grafana Configuration
,
1

Hi!

Did build grafana from source in windows WSL2 ubuntu 1804.
Everything working as intended, except for MySQL plugin.
At the same WSL2 Ubuntu I do have a fully working MySQL database, that I can connect through Workbench in windows and from CLI in ubuntu, but when I try to connect from GRAFANA I received ‘Access Denied’.

Notes taken:

  • It’s reaching the DB since it does try to login
  • I double checked users and permissions, even tried creating new users for this.
  • No matter if the field “password” is populated or not, the log always return PASSWORD ‘NO’

Full log from request:
logger=tsdb.mysql t=2022-02-10T23:07:22.39-0300 lvl=eror msg=“query error” err=“Error 1045: Access denied for user ‘root’@‘localhost’ (using password: NO)”
logger=context t=2022-02-10T23:07:22.39-0300 lvl=info msg=“Request Completed” method=POST path=/api/ds/query status=400 remote_addr=[::1] time_ms=2 size=218 referer=http://grafana.staged-by-discourse.com/datasources/edit/d9QwgEa7z

image
image1019×499 23.9 KB

image
image1351×43 3.18 KB

2

Couldn’t do it from UI but managed to make it works by using a YAML datasource file.

Anyway, if anyone can help in making the UI works I will appreciate.

apiVersion: 1

datasources:
  - name: MySQL
    type: mysql
    url: localhost:3306
    database: SL_Log
    user: root
    secureJsonData:
      password: ******
    jsonData:
      maxOpenConns: 0         # Grafana v5.4+
      maxIdleConns: 2         # Grafana v5.4+
      connMaxLifetime: 14400  # Grafana v5.4+
3

On most MySQL installs nowadays the root user will be configured to authenticate using the auth_socket plugin and NOT by password authentication. e.g. there is no root database password, only the root system user (or via sudo) will be able to login to the database as root. This is for security. See here for some more information.
It IS possible to configure the root user to authenticate using a password but it’s generally a bad idea.

For any service like Grafana you should create a database user and give that user the most limited set of privileges required. e,g, if it’s read-only reporting then something like:
CREATE USER ‘grafana’@‘localhost’ IDENTIFIED BY ‘???password???’;
GRANT select ON my_db.* TO ‘grafana’@‘localhost’;
Or better still, only grant select access to specific tables necessary.

If you create a user with a password you should be able to use that username and password in the Grafana data source. If it’s not working let us know exactly the method you used for creating user and exact Grafana datasource settings.

2 Likes
4

Hello codlord!

As stated, the root user was working by password because its just a development environment.
Besides that, I did create another user ‘grafana’ and did not work.

I created it using:

CREATE USER ‘grafana’@‘localhost’ IDENTIFIED BY ‘*****’;
GRANT select ON SL_Log.AccessLog TO ‘grafana’@‘localhost’;

Tried too:

CREATE USER ‘grafana’@‘localhost’ IDENTIFIED BY ‘*****’;
GRANT select ON SL_Log.* TO ‘grafana’@‘localhost’;

Tried too creating the user via the MySQL Workbench.

The DataSource and it’s configs didnt change from the print

5

The message “(using password: NO)” seems to indicate that Grafana is trying to connect to the database without a password. Not sure I can explain it. Could be to do with the windows ubuntu subsystem in your case, I have never tried Grafana there.

If you are not already - try using a very simple password with no special characters. Just wondering if maybe some special characters in the password are causing this?

Which version of Grafana? And what flavour/version of MySQL?