scm_prepare_database.sh / Mysql / Access denied

[#1]

Hello,

I'm working of installing a brand new cluster, on a fresh dedicated MySql Instance.

Servernames :

1. SCM : scmtst
2. Mysql : mysqltst001

CDH 5.12.1 freshly downalod & Installed

MySQL 5.7.19

MySql connector 5.1.44

CentOs 7.3, configured according to Cloudera specs

When i ran scm_prepare_database.sh on SCM, i keep getting "Access denied for user 'admin@'scmtst'"

/usr/share/cmf/schema/scm_prepare_database.sh -u admin -h mysqltst001 -P 3306 -p mysql scmdb scmuser

 [root@scmtst ~]# /usr/share/cmf/schema/scm_prepare_database.sh -u admin -h mysqltst001 -P 3306 -p mysql scmdb scmuser
Enter database password:
Enter SCM password:
JAVA_HOME=/usr/java/latest/
Verifying that we can write to /etc/cloudera-scm-server
Wed Oct 11 14:50:28 CEST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[ main] DbProvisioner ERROR Exception when creating/dropping database with user 'admin' and jdbc url 'jdbc:mysql://mysqltst001:3306/?useUnicode=true&characterEncoding=UTF-8'
java.sql.SQLException: Access denied for user 'admin'@'scmtst' (using password: YES)

In order to check that admin user was indeed set properly, I install the MySql client on scmtst, and I can indeed create and drop database at will if logged in as admin

While investigating, I extracted the command-line actually called by scm_prepare_database.sh, and once i explicitly add the password (-p & -P) : it worked. The database got created, as well as the user & GRANTS

[root@scmtst ~]# /usr/java/latest//bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbProvisioner --create -h mysqltst001.lab.ams:3306 -u admin -H scmtst.lab.ams -U scmuser -d scmdb -t mysql -p 'admin password' -P 'scm password'
Wed Oct 11 15:51:52 CEST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

=> Works 

However the script will fail later on on command com.cloudera.enterprise.dbutil.DbCommandExecutor.

The file /etc/cloudera-scm-server/db.properties looks good to me, yet it does not connect

[                          main] DbCommandExecutor              INFO  Unable to login using supplied username/password.
[                          main] DbCommandExecutor              ERROR Error when connecting to database.

It's lookng like the scripts scm_prepare_database.sh is not properly processing the passwords when attempting the logging to the MySql server.

I coud not find any similar error, hence this post

Thanks

Chris

[#2]

Same behaviour with CDH 5.10.1 installer

[#3]

I actually install a MySql intance on the SCM server directly, and It exposed the exact same behaviour:

[root@cdhscmtst ~]# /usr/share/cmf/schema/scm_prepare_database.sh -uroot  -p'<root password>' --verbose mysql scmdb scmuser 'scmuser password>'
JAVA_HOME=/usr/java/latest/
Verifying that we can write to /etc/cloudera-scm-server
Database type: mysql
Database user: root
Executing: /usr/java/latest//bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbProvisioner --create -h localhost -u root -H localhost -U scmuser -d scmdb -t mysql
Wed Oct 11 17:06:41 CEST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[                          main] DbProvisioner                  ERROR Exception when creating/dropping database with user 'root' and jdbc url 'jdbc:mysql://localhost/?useUnicode=true&characterEncoding=UTF-8'
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

I kinda wonder if it might related to some special character mis=-intepreted in the passwords.

I will disable the password policy in MySql & run more tests

[#4 · Accepted solution]

The Password policy / password format is indeed guilty :

1. Set the policy validation to low

2.  ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password1234';

3. 

[root@scmtst ~]# /usr/share/cmf/schema/scm_prepare_database.sh -uroot  -pPassword1234 --verbose mysql scmdb scmuser PAssword1234
JAVA_HOME=/usr/java/latest/
Verifying that we can write to /etc/cloudera-scm-server
Database type: mysql
Database user: root
Executing: /usr/java/latest//bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbProvisioner --create -h localhost -u root -H localhost -U scmuser -d scmdb -t mysql
Wed Oct 11 17:14:57 CEST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Creating SCM configuration file in /etc/cloudera-scm-server
Created db.properties file:
        # Auto-generated by scm_prepare_database.sh on Wed 11 Oct 17:14:58 CEST 2017
        #
        # For information describing how to configure the Cloudera Manager Server
        # to connect to databases, see the "Cloudera Manager Installation Guide."
        #
        com.cloudera.cmf.db.type=mysql
        com.cloudera.cmf.db.host=localhost
        com.cloudera.cmf.db.name=scmdb
        com.cloudera.cmf.db.user=scmuser
        com.cloudera.cmf.db.setupType=EXTERNAL
        com.cloudera.cmf.db.password=PAssword1234
Executing:  /usr/java/latest//bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/java/oracle-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbCommandExecutor /etc/cloudera-scm-server/db.properties com.cloudera.cmf.db.
Wed Oct 11 17:14:59 CEST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[                          main] DbCommandExecutor              INFO  Successfully connected to database.
All done, your SCM database is configured correctly!