Snipe-IT - setup issues with database permissions

[Bev]

I have been trying to setup Snipe-IT today, following this instructions.

 

https://blog.masteringmdm.com/snipe-it-installation-on-windows-server

 

It has all worked well, and I have the snipe Pre-Flight Check on the screen, but the database is showing an error.

 

D'oh! Looks like we can't connect to your database. Please update your database settings in your .env file. Your database says: SQLSTATE[HY000] [1045] Access denied for user 'snipe'@'localhost' (using password: YES) (SQL: select 2 + 2)

 

I have checked the database name / username / password are all corrected in the .env file, and have deleted the database / user and recreated them a few times but I can't seem to get past this message.

 

I was just wondering if anyone had any suggestions.

[bald_pig]

Just want to make sure, the guide instructs you to create a db user with the username "snipeit", whereas the username in the error is "snipe". Did you definitely create the db user with that username?

[Bev]

Yes. I started with snipeit but then tried Creating a new user with the name snipe to see if that would help.

[georgeescott]

Access denied would indicate a permissions issue. Can you connect with the root account?

 

You can test the database connection via maridb directly. Open CMD, run:

mysql -h 127.0.0.1 -u snipeit -p snipeit

Run a test command like

SHOW TABLES;

to show all the tables and see if that works.

 

 

You can also open CMD in the Snipe root directory and run:

php artisan db

to test the DB connection using the settings in your .env file.

Edited April 27, 2022 by georgeescott

[Steve21]

I was just wondering if anyone had any suggestions.

 

The log should show the exact error rather than the generic check, which will narrow it down a lot as many places that could be

 

But the most silly reason last year I've seen that is if you have a # in your password it breaks it

 

Steve

[Bev]

I also found the # issue when I googled - fortunately, there are no # in my password.

 

I have tried the command line

 

C:\Program Files\MariaDB 10.0\bin>mysql -h 127.0.0.1 -u snipe -p snipeit

Enter password: *********

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found.

 

and I tried

 

c:\inetpub\wwwroot\snipe-it>php artisan db

 

 

Command "db" is not defined.

 

Did you mean one of these?

db:seed

db:wipe

 

 

my .env file looks like this

 

--------------------------------------------

# REQUIRED: DATABASE SETTINGS

# --------------------------------------------

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_DATABASE=snipeit

DB_USERNAME='snipe'

DB_PASSWORD=password-is-here

DB_PREFIX=null

DB_DUMP_PATH='C:\\PROGRA~1\\MARIAD~1.0\\bin'

DB_CHARSET=utf8mb4

DB_COLLATION=utf8mb4_unicode_ci

 

 

Where would I find the log file for more details?

[bald_pig]

What version of mySQL are you running?

[Bev]

I used MariaDB to set everything up - this is 10.0.14-MariaDB

 

However the GUI is called HeidiSQL and that's 8.3.0.4694

 

I can't check in MySQL on the command line as everytime I try and use it I get the message about the Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found. Even if I use the root user.

[Steve21]

When you created your user did you use MySQL native password option?

 

If not trying altering it to use native and restarting sql service

 

Steve

[bald_pig]

When you created your user did you use MySQL native password option?

 

If not trying altering it to use native and restarting sql service

 

Steve

This, MariaDB only supports native encryption

[Bev]

I think the problem is that it wouldnt let me create the datebase with the port 3006, so I created it as 3007. I don't know how to tell SNIPE to use the different port -

If I look in MySQL workbench on 3006 I can see the user is set to caching_sha2_password, but on 3007 it is set to Standard.

[andyfield]

You can define the port number in your .env file

 

DB_CONNECTION=mysql

DB_HOST=*.*.*.*

DB_PORT=3307

[Bev]

I have managed to get it working by using the original database which was set to 3306 (it was from an earlier attempt to setup SNIPE last year).

I had to alter the user to use the correct version of security.

ALTER USER 'snipeit'@'localhost' IDENTIFIED WITH mysql_native_password

and critically, I discovered that if you change the .env file then you need to flush the cache.

php artisan config:cache

 

It's all working now, just working on getting my data transferred across.