question Re: Access denied for user 'ambari'@localhost (using password: YES) in Archives of Support Questions (Read Only)

Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 16:40:51 GMT

I'm traying to install Ambari for my cluster HDP, while when I configured MYSQL to be the database for My Serveur Ambari and than Started Ambari Service i got this erreur:

ERROR - Unexpected error, database check failed com.google.inject.CreationException: Guice creation errors: 1) Error injecting constructor, java.lang.RuntimeException: java.sql.SQLException: Access denied for user 'ambari'@'10.166.2.206' (using password: YES) at org.apache.ambari.server.orm.DBAccessorImpl.<init>(DBAccessorImpl.java:83) at org.apache.ambari.server.orm.DBAccessorImpl.class(DBAccessorImpl.java:71) while locating org.apache.ambari.server.orm.DBAccessorImpl while locating org.apache.ambari.server.orm.DBAccessor for field at org.apache.ambari.server.orm.dao.DaoUtils.dbAccessor(DaoUtils.java:36)

Can someone explaine what wrong with My Ambari server Please

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Mon, 06 Nov 2017 16:42:11 GMT

@yassine sihi

As we see the IP Address in your error which usually is the Hostname.

Access denied for user 'ambari'@'10.166.2.206' (using password: YES)

Hence please check if your AmbariServer is running on FQDN (hostname -f) or On IP Address.

# hostname -f
# cat /etc/hosts

Regrading the Database permission issue please make sure that your run the following commands on your MySQL DB

GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'YOUR_AMBARI_HOSTNMAE';
GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'10.166.2.206';
FLUSH PRIVILEGES;

Then restart Ambari Server.

For more details please refer to: https://docs.hortonworks.com/HDPDocuments/Ambari-2.5.2.0/bk_ambari-administration/content/using_ambari_with_mysql.html

Reference Thread: https://community.hortonworks.com/questions/144043/error-1045-28000-access-denied-for-user-ambari.html

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 16:51:01 GMT

My AmbariServer is running on FQDN:
      # hostname -f
      slzusd1mys04.sres.stech


when i execute those commands on my MySQL DB i get :

      ERROR 1045 (28000): Access denied for user 'ambari'@'localhost' (using password: NO)

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 16:54:59 GMT

My AmbariServer is running on FQDN:

# hostname -f
slxxxxxsxxx.sxxx.sxxx

when i execute those commands on my MySQL DB i get :

ERROR 1045 (28000): Access denied for user 'ambari'@'localhost' (using password: NO)

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Mon, 06 Nov 2017 17:09:50 GMT

@yassine sihi

Please login to the MySQL Database (not necessarily on "ambari" DB) but you can login to "mysql" DB instead as following:

# mysql -u root -p
Enter password: <ENTER_YOUR_DB_ROOT_PASSWORD>

mysql> select User,Password,Host from mysql.user;

Then check if the "ambari" user is present in the output of the above command with 'localhost' permission?

If you are able to login to your MySQL DB with the "root" user then you should be able to run the following queries:

    ambari> use mysql;
    GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'%';
    GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'localhost';
    CREATE USER 'ambari'@'standaloneambari.example.com' IDENTIFIED BY 'bigdata';  <---- Replace the host name 
    GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'standaloneambari.example.com';  <---- Replace the host name 
    FLUSH PRIVILEGES;

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Mon, 06 Nov 2017 17:19:26 GMT

@yassine sihi

Just in case if this is a fresh MySQL setup and you do not know your MySQL "root" user password the please try the following link to update the root user password.:

https://community.hortonworks.com/questions/141454/what-is-the-mysql-root-password-from-hive-install.html?childToView=141464#comment-141464

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 17:28:01 GMT

when i type without password i get access to MySQL (this is weird bacause i wrote a password at setup of Ambari):

# mysql -u ambari

This is what i get:

+----------------- | User | Password | host | +----------------------

| root | *76BE106726BEDB650DBACA0B576C7 | localhost

| root | | slxxxxxxxxxxxx.xxxx.sxxx |

| root | | 127.0.0.1 |

| root | | ::1 |

| Adm_replic | *53598BE3DB17445045 | localhost |

| CO_MUS00SPV_SELE | *F1B83C64FA87D87108077B666A5 | % |

| root | *76BE10672EF6D33A0B576C7 | % |

| CO_ZUS10MYS_SYS | *8FFFE041802516F235DDE3A4506E | localhost |

....................................

..........................

| ambari | *C33A05FE652CA69F0DE7FA785D3916 | % |

| hive | *5269CDF94758ACD11C110A254DAE | % |

| hive | *99A15A0B5385DF649D48344E0 | slxxxxxxxxxxxx.xxxx.sxxx |

| oozie | *C3BE1E212509519234736969 | % |

| ambari | | localhost |

| ambari | | slxxxxxxxxxxxx.xxxx.sxxx |

+------------------+-------------------------------------------+-------------------------+

i think that ambari has been without password!!!

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Mon, 06 Nov 2017 17:30:52 GMT

@yassine sihi

You can confirm your Ambari Server Database password inside the following file: (By default the password is not Encrypted, Until you explicitly choose to encrypt the passwords)

[root@sandbox ~]# grep 'server.jdbc.user.passwd' /etc/ambari-server/conf/ambari.properties
server.jdbc.user.passwd=/etc/ambari-server/conf/password.dat

[root@sandbox ~]# cat /etc/ambari-server/conf/password.dat
bigdata

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 17:51:17 GMT

@Jay Kumar SenSharma

I could access to Mysql by root and ran those commands, i got the following result:

when i try to creat Ambari user i get this erreur:

ERROR 1396 (HY000): Operation CREATE USER failed for 'ambari'@'slxxxxxxxx.xxxx.xxx'

thanks for your reply!!

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 18:16:52 GMT

@Jay Kumar SenSharma

Yes i can see the password that i have typed beford, it's literally different than 'bigdata', so why i still can login without password??

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Mon, 06 Nov 2017 18:32:18 GMT

@yassine sihi

It looks like the GRANT for "ambari' user are not properly set inside the DB.

Can you please share the output of the following SQL query from the Database. You can login as "root" user inside the DB and then run the command;

mysql> select User,Password,Host from mysql.user;

Also update the "ambari" suer password using the following command:

mysql>  UPDATE user SET Password=PASSWORD('bigdata') where USER='ambari'; 
mysql>  FLUSH PRIVILEGES;

Then restart Ambari Server.

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Sun, 18 Aug 2019 08:55:08 GMT

@Jay Kumar SenSharma

Ok, This is the output after running UPDATE query:

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Mon, 06 Nov 2017 21:08:40 GMT

@yassine sihi

In the above screenshot the "password" column for user "ambari" is Empty where the Host is "10.166.2.206" which should be set to password.

 UPDATE user SET Password=PASSWORD('bigdata') where USER='ambari';

And then the Grant need to be provided to the user.

<br>mysql> use mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'localhost'; 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'s1xxxxxxxxxxxx';    <--- correct hostname
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'10.166.2.206'; 
mysql> FLUSH PRIVILEGES;

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Mon, 06 Nov 2017 21:28:38 GMT

@Jay Kumar SenSharma

I don't think that's the issue because i have already done it.

thanks Jay

Re: Access denied for user 'ambari'@localhost (using password: YES)

sihi_yassine — Tue, 07 Nov 2017 00:08:52 GMT

I think that i found the solution:

when i started the ambari installation, I stupidly change the default password which is bigdata to an other

So when the server goes to start it retrieve the password which is inside /etc/ambari-server/conf/password.dat in order to connect to Mysql which is obviously not the same.

I forced the change of the password in the path: /etc/ambari-server/conf/password.dat and then it works fine

thanks @Jay Kumar SenSharma

Re: Access denied for user 'ambari'@localhost (using password: YES)

jsensharma — Tue, 07 Nov 2017 00:20:28 GMT

@yassine sihi

Thanks for sharing the solution to check the password file first.

[root@sandbox ~]# cat /etc/ambari-server/conf/password.dat
bigdata