MySQL Bugs: #106852: Cannot connect using named pipe on windows if the connection was the first

Bug #106852	Cannot connect using named pipe on windows if the connection was the first
Submitted:	28 Mar 2022 12:38	Modified:	11 Apr 2022 2:00
Reporter:	William Tang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S2 (Serious)
Version:	8.0.28	OS:	Windows (Windows 11 Pro (build 22000.556))
Assigned to:		CPU Architecture:	x86 (x64)
Tags:	named pipe

Description:
If the mysql server is restarted, and you connect it using named pipe, the connection will fail and it will continue to fail until you connect it using tcp.  Please refer to the following command line output for what I mean.

```log
[William@desktop ~]$ mysql --protocol pipe -u root -p
Enter password: **********
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

[William@desktop ~]$ mysql --protocol tcp -u root -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

[William@desktop ~]$ mysql --protocol pipe -u root -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
```

How to repeat:
1. Restart the mysql server
2. Connect to the server using command "mysql --protocol pipe -u root -p"

Suggested fix:
N/A

Hello Tang William,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Posted by developer:
 
This appears to be correct behaviour to me: see the documentation at https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchang...

When a named pipe connection is used for the first connection for a particular user (since the MySQL server started), the server has not cached the password for that user and thus attempts a full authentication exchange with the client. The full authentication exchange cannot be performed over a named pipe as it is not a secure channel, hence the client error message. 

When a different connection has already successfully connected with the server for the user, attempting to connect for the same user over a named pipe will succeed via the "fast authentication" route as the cached password for the user is already present on the server.

In order to successfully connect over a named pipe for the first time, one can use the --get-server-public-key mysql command line option, so that the full authentication exchange between the client and server can be achieved without sending the password in plaintext over the insecure named pipe connection.

Hi, Daniel Blanchard.

As the default behavior, it's very confusing to most of the users.  And why mysql cannot establish secure connection using local method like named pipe while tcp has no such problem?  Is it because tcp fetch the public key from the server by default?  If so, can you make named pipe do the same thing by default?