Layer7 API Management

Expand all | Collapse all

CA API Gateway DB user(gateway) password reset

Jump to Best Answer
  • 1.  CA API Gateway DB user(gateway) password reset

    Posted 07-03-2018 10:11 AM

    I am getting below error/warning in API gateway audit logs:

     

    2018-07-02T16:50:12.812+0100 WARNING 100 com.l7tech.server.cluster.DatabaseReplicationMonitor: 2380: Error accessing host/database <hostname>/ssg: Could not get JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'gateway'@'<hostname>' (using password: YES)

     

    How can we reset the password of "gateway"(DB user) user? We have installed API Gateway on appliance.

     

    I have tried to change the password using below methods but no luck:

     

    1) Executing below query on mysql of API gateway:

    ALTER USER 'gateway'@'<hostname>' IDENTIFIED BY '<password>';

    Getting error as: Mysql query error.

     

    2) Via resetadmin.sh script which is present under /opt/SecureSpan/Appliance/bin directory.

    Haven't got any error but still getting the same above error in logs.

     

    3) Through ssgconfig UI, by selecting options:
    2 - Display CA API Gateway Configuration meu
    8 - Reset Admin password

    Getting error as: "The administrative username, 'gateway', does not exist."
    Also tried with gateway@hostname however no luck.

     

    Have I missed something? Can anybody help me to resolve the above mentioned error in logs?

     

     

    Thanks,
    Nikunj



  • 2.  Re: CA API Gateway DB user(gateway) password reset

    Posted 07-03-2018 10:41 PM

    Dear Nikunj_Padhiyar ,

     

    In mysql, 'gateway'@'host1' is different with 'gateway'@'host2'.

     

    'gateway'@'host1' means the permission of initializing the mysql connection from host1 to mysql server with user gateway.

     

    ie. if you connect from host1, resetting password of 'gateway'@'host2' will not work. (if you only have 'gatway'@'localhost', any remote connection is impossible)

     

    So, first of all, ensure 'gateway'@'<the host to connect to mysql>' exists.

    Alternatively, you may create user 'gateway'@'%' to match any host.

     

    And then grant the right permission and password.

     

    Regards,

    Mark



  • 3.  Re: CA API Gateway DB user(gateway) password reset

    Posted 07-04-2018 10:39 AM

    I think I may have confused you here.

     

    We have 2 servers in cluster say host1(with Fully qualified domain name(FQDN)) and host2(with Fully qualified domain name(FQDN)).


    Replication is happening fine between these hosts.

     

    Now, I am getting below error in host1 in ssg logs:

     

    2018-07-04T14:50:14.003+0100 WARNING 100 com.l7tech.server.cluster.DatabaseReplicationMonitor: 2380: Error accessing host/database host2/ssg: Could not get JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'gateway'@'host1' (using password: YES)

     

    And getting below error in host2:

     

    2018-07-04T06:36:54.262-0700 WARNING 101 com.l7tech.server.cluster.DatabaseReplicationMonitor: 2380: Error accessing host/database host1/ssg: Could not get JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'gateway'@'host2' (using password: YES)

     

    I could see user gateway is present in "mysql" DB in "user" table on both the servers host1 and host2.

     

    And for "gateway" user, below user-host combinations are present in the "user" table on host2:

     

    host1                                     | gateway
    host2                                     | gateway
    <only host1(without FQDN)> | gateway
    %                                           | gateway
    localhost                                 | gateway
    localhost6                               | gateway
    localhost.localdomain             | gateway
    localhost6.localdomain6          | gateway

     

    And below are present in host1:

     

    host1                               | gateway
    %                                     | gateway
    host2                               | gateway
    localhost                         | gateway
    localhost6                      | gateway
    localhost.localdomain    | gateway
    localhost6.localdomain6 | gateway

     

    Now, from the error, it is understood that host1 is not able to connect to host2/ssg DB and we are getting access denied for user "gateway" on its own host: host1.

     

    I've tried to reset the password directly from Mysql for this user and via resetadmin script. But no luck. Getting error as mentioned in my earlier update.

     

    When I am trying to reset it via SSGCONFIG shell(option 2 then option 8(Reset Admin password)) on host2, I am seeing below error while resetting any of the users like'gateway'@'host1', 'gateway'@'host2', 'gateway'@'localhost', etc passwords:

     

    Unable to update admin account due to error 'Access denied for user 'gateway'@'host2' (using password: YES)'.

     

    And getting below error on host1:

     

    The administrative username, 'gateway'@'host1', does not exist.
    The administrative username, 'gateway'@'host2', does not exist.

     

    Hope this makes sense now.

     

    Can you help me to resolve this error?

     

    Let me know for any further queries.

     

    Thanks,
    Nikunj



  • 4.  Re: CA API Gateway DB user(gateway) password reset

    Posted 07-04-2018 08:22 PM

    Dear Nikunj,

    for example, on host1, if you have problem to connect to host2 mysql, then you need to config the user and permission on host2.

    ie. on host2, create user 'gateway'@'host1', 'gateway'@'%'; set password; grant permission to work on ssg database.

     

    The same, on host2, if have problem to connect to host1 mysql, need to work on host1, and config user 'gateway'@'host2' (or 'gateway'@'%')

     

    The following SQL commands should always work (run on both mysql server),

     

    CREATE USER 'gateway'@'%' IDENTIFIED BY '7layer';

    grant all on *.* to 'gateway'@'%'  WITH GRANT OPTION;

    FLUSH PRIVILEGES;

     

    You can replace '7layer' to any password you want; you can replace grant all on *.* to grant all on ssg.*

    You can verify the connection from host1 to host2 by run the command below on host1,

    mysql -h host2 -u gateway -p ssg

     

    if you can login mysql successfully, that means the gateway user setting on host2 should have no problem.

     

    Regards,

    Mark



  • 5.  Re: CA API Gateway DB user(gateway) password reset
    Best Answer

    Posted 07-04-2018 08:29 PM

    Hi Nikunj_Padhiyar ,

    It seems you already have 'gateway'@'%' user on both nodes, then you can reset the password by mysql command below,

     

    SET PASSWORD FOR 'gateway'@'%' = PASSWORD('cleartext password');

     

    Regards,

    Mark



  • 6.  Re: CA API Gateway DB user(gateway) password reset

    Posted 07-05-2018 06:49 AM

    Thanks Mark for all your help. 

     

    I ran the "SET PASSWORD" command on host2 for gateway user and can see below log message on host1:

     

    2018-07-05T08:27:34.458+0100 WARNING 100 com.l7tech.server.cluster.DatabaseReplicationMonitor: 2382: Replication recovered for host/database host2/ssg

     

    So, set password worked successfully.

     

    Many Thanks again for your help. 

     

    Cheers,

    Nikunj