Can’t login as mysql user root from normal user account in ubuntu 16.04

Before I outline the solution to the problem, let’s first discuss why you might want to log in and why you might not.

Why you should login as root

I know at least one scenario where you need to log in as root because sudo is not an option. The keyword is SFTP. If you remotely manage a Linux machine, you will often want to use SFTP to edit system configuration files or system scripts which requires root permissions.

Of course, you can also login via SSH and then edit the files with sudo. However, editing large scripts with an editor such as nano is not really fun. It is much more convenient and efficient if you can work with your favorite GUI editor on your local machine.

Some SFTP clients like WinSCP allow you to send a sudo su after login, but many popular tools, such as Transmit for macOS, don’t offer this option.

If you want to remotely edit system files in such cases, you have two options for EC2 instances.

Enable root login on EC2 instances

The first option is simply to edit the authorized_keys file in /root/.ssh/. All you have to do is remove this text:

 ,command="echo 'Please login as the user \"ec2-user\" rather than the user \"root\".';echo;sleep 10;exit 142" 

It probably makes sense to leave the beginning of authorized_keys: «no-port-forwarding,no-agent-forwarding,no-X11-forwarding.»

Enable root login on an EC2 instance

Also, notice that whenever you create an AMI from this instance and then launch a new instance, AWS will modify the authorized_keys file, and you will have to remove the text above again.

Create a second root user

sudo useradd -m -ou 0 -g 0 sysop
sudo mkdir /home/sysop/.ssh
sudo cp /home/ubuntu/.ssh/authorized_keys /home/sysop/.ssh/

Conclusion

Subscribe to 4sysops newsletter!

asked Feb 1, 2011 at 6:26

answered Feb 6, 2011 at 2:44

2 silver badges3 bronze badges

1. Check your logs (/var/log/messages, /var/log/security)
2. Check your user has a valid login shell
3. Check the output of faillog -u $USER (and reset with pam_tally)
4. Check for the presence of /etc/nologin
5. Check the output of chage -l $USER
6. reset the password to a known, simple value and try again
7. Post the details of what happenned when you ran through the steps above here and what is now appearing the logs

answered Dec 13, 2021 at 23:58

2 gold badges24 silver badges35 bronze badges

passwd [user]

8 gold badges69 silver badges88 bronze badges

answered Apr 6, 2012 at 4:02

On a ubuntu 16.04 server (OVH public cloud)

I create a standard account :

adduser www

I add my public key into .ssh/authorized_keys
Then I do a

ssh www@myserver

And I got :

Please login as the user "ubuntu" rather than the user "root"

I was expecting «www» instead of «root», but not : strange.

The public key is correctly read because before setting it I got permission denied. In fact, the login process begins correctly because in auth.log I got :

May 14 09:12:42 serveur-1 sshd[16069]: Accepted publickey for www from 86.252.248.254 port 38686 ssh2: RSA SHA256:+X9bylqQgjb/bjeLL8P0AVx0WFJZloWXacKPE43oD4E
May 14 09:12:42 serveur-1 sshd[16069]: pam_unix(sshd:session): session opened for user www by (uid=0)
May 14 09:12:42 serveur-1 systemd: pam_unix(systemd-user:session): session opened for user www by (uid=0)
May 14 09:12:42 serveur-1 systemd-logind[1291]: New session 27 of user www.
May 14 09:12:52 serveur-1 sshd[16129]: Received disconnect from 86.252.248.254 port 38686:11: disconnected by user
May 14 09:12:52 serveur-1 sshd[16129]: Disconnected from 86.252.248.254 port 38686
May 14 09:12:52 serveur-1 sshd[16069]: pam_unix(sshd:session): session closed for user www
May 14 09:12:52 serveur-1 systemd-logind[1291]: Removed session 27.

Any idea ?

#Authentication
PermitRootLogin yes

This had no effect.

Now, the message I get when I try to log in as ‘root’ is:

DISABLE_ROOT_OPTS = (
"no-port-forwarding,no-agent-forwarding,"
"no-X11-forwarding,command=\"echo \'Please login as the user \\\"$USER\\\""
" rather than the user \\\"root\\\".\';echo;sleep 10\"")

It seems to me that the offending ssh configuration directives are ‘no-X11-forwarding’ and the other two. I’ve come to this conclusion since they seem to be associated with the offending message.

I’m guessing that the command directive instructs the ssh daemon to show this message when the associated directives are violated. Am I on the right track?

Update: The python script mentioned below is part of the Ubuntu CloudInit package.

# PAM configuration for the Secure Shell service

# Standard Un*x authentication.
@include common-auth

# Disallow non-root logins when /etc/nologin exists.
account    required     pam_nologin.so

# Uncomment and edit /etc/security/access.conf if you need to set complex
# access limits that are hard to express in sshd_config.
# account  required     pam_access.so

# Standard Un*x authorization.
@include common-account

# SELinux needs to be the first session rule.  This ensures that any
# lingering context has been cleared.  Without this it is possible that a
# module could execute code in the wrong domain.
session [success=ok ignore=ignore module_unknown=ignore default=bad]        pam_selinux.so close

# Set the loginuid process attribute.
session    required     pam_loginuid.so

# Create a new session keyring.
session    optional     pam_keyinit.so force revoke

# Standard Un*x session setup and teardown.
@include common-session

# Print the message of the day upon successful login.
# This includes a dynamically generated part from /run/motd.dynamic
# and a static (admin-editable) part from /etc/motd.
session    optional     pam_motd.so  motd=/run/motd.dynamic
session    optional     pam_motd.so noupdate

# Print the status of the user's mailbox upon successful login.
session    optional     pam_mail.so standard noenv # [1]

# Set up user limits from /etc/security/limits.conf.
session    required     pam_limits.so

# Read environment variables from /etc/environment and
# /etc/security/pam_env.conf.
session    required     pam_env.so # [1]
# In Debian 4.0 (etch), locale-related environment variables were moved to
# /etc/default/locale, so read that as well.
session    required     pam_env.so user_readenv=1 envfile=/etc/default/locale

# SELinux needs to intervene at login time to ensure that the process starts
# in the proper default security context.  Only sessions which are intended
# to run in the user's context should be run after this.
session [success=ok ignore=ignore module_unknown=ignore default=bad]        pam_selinux.so open

# Standard Un*x password updating.
@include common-password

# Package generated configuration file
# See the sshd_config(5) manpage for details

# What ports, IPs and protocols we listen for
Port 22
# Use these options to restrict which interfaces/protocols sshd will bind to
#ListenAddress ::
#ListenAddress 0.0.0.0
Protocol 2
# HostKeys for protocol version 2
HostKey /etc/ssh/ssh_host_rsa_key
HostKey /etc/ssh/ssh_host_dsa_key
HostKey /etc/ssh/ssh_host_ecdsa_key
HostKey /etc/ssh/ssh_host_ed25519_key
#Privilege Separation is turned on for security
UsePrivilegeSeparation yes

# Lifetime and size of ephemeral version 1 server key
KeyRegenerationInterval 3600
ServerKeyBits 1024

# Logging
SyslogFacility AUTH
LogLevel INFO

# Authentication:
LoginGraceTime 120
PermitRootLogin yes
StrictModes yes

RSAAuthentication yes
PubkeyAuthentication yes
#AuthorizedKeysFile %h/.ssh/authorized_keys

# Don't read the user's ~/.rhosts and ~/.shosts files
IgnoreRhosts yes
# For this to work you will also need host keys in /etc/ssh_known_hosts
RhostsRSAAuthentication no
# similar for protocol version 2
HostbasedAuthentication no
# Uncomment if you don't trust ~/.ssh/known_hosts for RhostsRSAAuthentication
#IgnoreUserKnownHosts yes

# To enable empty passwords, change to yes (NOT RECOMMENDED)
PermitEmptyPasswords no

# Change to yes to enable challenge-response passwords (beware issues with
# some PAM modules and threads)
ChallengeResponseAuthentication yes

# Change to no to disable tunnelled clear text passwords
PasswordAuthentication yes

# Kerberos options
#KerberosAuthentication no
#KerberosGetAFSToken no
#KerberosOrLocalPasswd yes
#KerberosTicketCleanup yes

# GSSAPI options
#GSSAPIAuthentication no
#GSSAPICleanupCredentials yes

X11Forwarding yes
X11DisplayOffset 10
PrintMotd no
PrintLastLog yes
TCPKeepAlive yes
#UseLogin no

#MaxStartups 10:30:60
#Banner /etc/issue.net

# Allow client to pass locale environment variables
AcceptEnv LANG LC_*

Subsystem sftp /usr/lib/openssh/sftp-server

# Set this to 'yes' to enable PAM authentication, account processing,
# and session processing. If this is enabled, PAM authentication will
# be allowed through the ChallengeResponseAuthentication and
# PasswordAuthentication.  Depending on your PAM configuration,
# PAM authentication via ChallengeResponseAuthentication may bypass
# the setting of "PermitRootLogin without-password".
# If you just want the PAM account and session checks to run without
# PAM authentication, then enable this but set PasswordAuthentication
# and ChallengeResponseAuthentication to 'no'.
UsePAM yes

I launched an Ubuntu 18 instance on EC2.

Then I ran:

rsync --archive --chown=newuser:newuser ~/.ssh /home/newuser

Authenticating with public key «imported-openssh-key»

I get the same error from both MobaXterm and PuTTY.

asked Jul 8, 2019 at 21:24

no-port-forwarding,no-agent-forwarding,no-X11-forwarding,command="echo 'Please login as the user \"ubuntu\" rather than the user \"root\".';echo;sleep 10"

and keep the ssh-rsa and key that comes after it.

answered Jul 11, 2019 at 11:32

1 gold badge3 silver badges9 bronze badges

cat /root/.ssh/authorized_keys 
no-port-forwarding,no-agent-forwarding,no-X11-forwarding,command="echo 'Please login as the user \"ubuntu\" rather than the user \"root\".';echo;sleep 10"

remove this line and keep the ssh-rsa and key that comes after it.

save the file then try again.

answered Jul 9, 2019 at 20:59

3 silver badges8 bronze badges

The proper way to address this is by using ssh’s -l flag. Not by tampering with warning messages.

Example:

$ sudo ssh -i *path/xxxxx.pem* n.n.n.n
Please login using xxxxx

$ sudo ssh -i keys/xxxxxx n.n.n.n -l ubuntu
Welcome to Ubuntu 18.04.1 LTS (GNU/Linux 4.15.0-1021-aws x86_64)

Hope this helps!

56 gold badges215 silver badges327 bronze badges

answered Jul 9, 2020 at 22:55

Because it kept on annoying me I created this oneliner:

# sed -i -e 's/.*exit 142" \(.*$\)/\1/' /root/.ssh/authorized_keys

answered Jun 15, 2022 at 9:16

2 bronze badges

In short, on MariaDB

UPDATE mysql.user SET plugin = 'mysql_native_password', 
      Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

where you replace NEWPASSWORD with the password you want, and everything else verbatim.

mysql -uroot -p

select User,host,plugin from mysql.user; 
+----------------+-----------+-----------------------+
| User           | host      | plugin                |
+----------------+-----------+-----------------------+
| root           | localhost | auth_socket           |
+----------------+-----------+-----------------------+

On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. NEWPASSWORD is your password. mysql_native_password you type verbatim.

UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

(It’s possible that setting the plugin to empty would work. YMMV. I didn’t try this. So this is an alternative.)

UPDATE mysql.user SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD';

FLUSH PRIVILEGES;