ERROR 1045 (28000): Access denied for user 'user@hostname' (using password: NO) while using ssl certs

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi all,

I am trying to connect to mysql using python lib pymysql and user with ssl certs but getting below error. I have created user with null password

ERROR 1045 (28000): Access denied for user ‘user@hostname’ (using password: NO) while using ssl certs

conn=pymysql.connect(database=“xyzzy”,user=“test”,host=“10.0.0.8”,password=“”, port=3346,ssl={‘ssl’: {‘ca’:“/home/oracle/mtls/ca.pem”,‘key’:“/home/oracle/mtls/client-key.pem”,‘cert’:“/home/oracle/mtls/cert.pem”}})

Can someone help with what can be issue

mysqld> SHOW VARIABLES LIKE “version%”;
±------------------------±----------------------------------------------------+
| Variable_name | Value |
±------------------------±----------------------------------------------------+
| version | 8.0.32-24 |
| version_comment | Percona Server (GPL), Release 24, Revision e5c6e9d2 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.13 |
| version_suffix | |
±------------------------±-----

2

±------------------------±--------------±----------------------±----------------------+
| user | host | authentication_string | plugin |
±------------------------±--------------±----------------------±----------------------+
|xyz | 10.0.0.8 | | mysql_native_password |

3

@Shruti14,
Please show how you created the user.

4

CREATE USER ‘’@‘%’ REQUIRE SUBJECT '/CN value;

5

Please provide the output of your CN from the cert
openssl x509 -in certificate.crt -text -noout Do they match?

6

Yes that is matching

7

@Shruti14,
Please provide ALL commands. Exact/complete commands used to create the SSL certificates, and to create the MySQL user. I cannot help you without more information. I’d like to test your same commands in my environment.

8

User creation :User creation for Non Prod DB :

CREATE USER ‘test.abc.com’@‘10.0.2.0’ REQUIRE SUBJECT ‘/CN=test.abc.com/OU=management:id.group.10784/O=ABC Inc./ST=California/C=US’;
GRANT SELECT ON performance_schema.* TO ‘test.abc.com’@‘10.0.2.0’ ;
GRANT SELECT ON sys.* TO ‘test.abc.com’@‘10.0.2.0’;
GRANT SELECT ON mysql.* TO ‘test.abc.com’@‘10.0.2.0’;
flush privileges;

9

used openssl command to create certs

10

Can you please provide this? I asked above for ALL commands including the ones to create the SSL certificates.

11

Ok. I spent a fair amount of time debugging this.

Certs:

-- CA
$ openssl x509 -text -noout -in data/ca.pem | less
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 1 (0x1)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolCA
        Validity
            Not Before: Aug 29 23:41:40 2022 GMT
            Not After : Aug 26 23:41:40 2032 GMT
        Subject: C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolCA

-- Server
$ openssl x509 -text -noout -in data/server-cert.pem
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 2 (0x2)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolCA
        Validity
            Not Before: Aug 29 23:44:58 2022 GMT
            Not After : Aug 26 23:44:58 2032 GMT
        Subject: C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolServer

-- Client
$ openssl x509 -text -noout -in client.pem
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 3 (0x3)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolCA
        Validity
            Not Before: Aug 29 23:46:41 2022 GMT
            Not After : Aug 26 23:46:41 2032 GMT
        Subject: C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolClient

NOTE: All three certs have different Common Names (CN). This is required by MySQL.

Testing:

-- New user, password auth
mysql [localhost:8035] {root} ((none)) > CREATE USER certy@'%' IDENTIFIED BY '12345';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8035] {root} ((none)) > Bye
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85

-- Change user to REQUIRE SSL
-- User presents the same CA in use by MySQL. This does not do deep SSL cert checking, only disallows non-encrypted connections
mysql [localhost:8035] {root} ((none)) > ALTER USER 'certy'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

-- No cert/CA presented
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345
ERROR 1045 (28000): Access denied for user 'certy'@'localhost' (using password: YES)

-- Present CA
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345 --ssl-ca=data/ca.pem
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85

-- Change user to require a valid certificate signed by server's CA, no deep SSL checks
mysql [localhost:8035] {root} ((none)) > ALTER USER 'certy'@'%' REQUIRE X509;
Query OK, 0 rows affected (0.00 sec)

-- No client cert is presented, only CA; connection rejected
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345 --ssl-ca=data/ca.pem
ERROR 1045 (28000): Access denied for user 'certy'@'localhost' (using password: YES)

-- No CA, only client cert
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345 --ssl-cert=client.pem --ssl-key=client.key
ERROR 1045 (28000): Access denied for user 'certy'@'localhost' (using password: YES)

-- Provide CA and client cert
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345 --ssl-ca=data/ca.pem --ssl-cert=client.pem --ssl-key=client.key
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85

-- Change user to require specific SSL issuer
mysql [localhost:8035] {root} ((none)) > ALTER USER 'certy'@'%' REQUIRE ISSUER '/C=US/ST=Anywhere/L=MyCity/O=Percona/OU=TrainingDept/CN=MyCoolCA';
Query OK, 0 rows affected (0.01 sec)

~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345 --ssl-ca=data/ca.pem --ssl-cert=client.pem --ssl-key=client.key
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85

-- Remove password
mysql [localhost:8035] {root} ((none)) > ALTER USER 'certy'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0.01 sec)

-- Provide password
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty -p12345 --ssl-ca=data/ca.pem --ssl-cert=client.pem --ssl-key=client.key
ERROR 1045 (28000): Access denied for user 'certy'@'localhost' (using password: YES)

-- No password, SSL issuer verified
~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty --ssl-ca=data/ca.pem --ssl-cert=client.pem --ssl-key=client.key
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85

-- Remove issuer verification, add subject verification
mysql [localhost:8035] {root} ((none)) > ALTER USER 'certy'@'%' REQUIRE ISSUER '';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8035] {root} ((none)) > ALTER USER 'certy'@'%' REQUIRE SUBJECT '/C=US/ST=Anywhere/L=MyCity/O=Percona/OU=TrainingDept/CN=MyCoolClient';
Query OK, 0 rows affected (0.00 sec)

~/dbdeployer/sandboxes/msb_ps8_0_34$ ./use -ucerty --ssl-ca=data/ca.pem --ssl-cert=client.pem --ssl-key=client.key
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85
12

This is the best I could come up with for extracting the CN
openssl x509 -in client.pem -noout -subject -nameopt sep_comma_plus | cut -b9- | sed 's/,/\//g'

13

Python3 test script, password-less, SSL Subject verification:

#!/usr/bin/python3

# pip3 install mysql-connector-python
import mysql.connector

dbHost = "10.10.10.203"
dbPort = 8035
dbUser = "certy"
dbPass = ""

sslCa   = "data/ca.pem"
sslCert = "client.pem"
sslKey  = "client.key"

mydb = mysql.connector.connect(host=dbHost, port=dbPort, user=dbUser, password=dbPass, ssl_ca=sslCa, ssl_cert=sslCert, ssl_key=sslKey)
cur = mydb.cursor(buffered=True)

cur.execute("SHOW STATUS LIKE 'Ssl%'")
for (status) in cur:
  print(status)

cur.execute("SELECT @@hostname, NOW()")
(hn, n) = cur.fetchone()
print(f"Hostname: {hn}, Now: {n}")

cur.close()
mydb.close()
14

thanks let me test that out if it works will post the update

15

can’t i try this via pymysql lib does mysql connector is necessity?

16

You can try it however you like. I used the MySQL Connector because that is the official library from MySQL/Oracle. It looks like pymysql supports SSL options similar to connector.

17

Client cert creation certs :

openssl req -new -newkey rsa:2048 -sha256 -keyout test.abc.com -subj “/CN=test.abc.com” -out test.abc.com.csr

Now generated Private.key, ca.pem and cert.pem from above command

Created user :
CREATE USER ‘test.abc.com’@‘10.0.2.0’ REQUIRE SUBJECT ‘/CN=test.abc.com/OU=management:id.group.10784/O=ABC Inc./ST=California/C=US’;
GRANT SELECT ON performance_schema.* TO ‘test.abc.com’@‘10.0.2.0’ ;
GRANT SELECT ON sys.* TO ‘test.abc.com’@‘10.0.2.0’;
GRANT SELECT ON mysql.* TO ‘test.abc.com’@‘10.0.2.0’;
flush privileges;

Server side :
ssl_ca=/home/mysqld/adb/etc/tls/ca-cert.pem
ssl-cert=/home/mysqld/adb/etc/tls/server-cert.pem
ssl-key=/home/mysqld/adb/etc/tls/server-key.pem

using this to connect from client getting error :
conn=pymysql.connect(user=“test.abc.com”,host=“10.0.2.0”,password=“”,port=3306,ssl={‘ssl’: {‘ca’:“/home/oracle/tls/ca.pem”,‘key’:“/home/oracle/tls/private.key”,‘cert’:“/home/oracle/tls/cert.pem”,‘ssl-mode’:“verify_identity”}})

pymysql.err.OperationalError: (1045, “Access denied for user ‘test.abc.com’@‘10.0.2.0’ (using password: NO)”)

conn=pymysql.connect(user=“test.abc.com”,host=“10.0.2.0”,password=“abc123456”,port=3306,ssl={‘ssl’: {‘ca’:“/home/oracle/tls/ca.pem”,‘key’:“/home/oracle/tls/private.key”,‘cert’:“/home/oracle/tls/cert.pem”,‘ssl-mode’:“verify_identity”}})

pymysql.err.OperationalError: (1045, “Access denied for user ‘test.abc.com’@‘10.0.2.0’ (using password: YES)”)

18

See my note from above:
NOTE: All three certs have different Common Names (CN). This is required by MySQL.

You cannot use the same CN (test.abc.com) for all 3 certs. The subjects must also match EXACTLY in the CREATE USER, you cannot provide the sections out of order. Use the one-liner I provided above to extract out the subject.

19

how can the CN be different i want one user only to work as mtls using certs and openssl cert command will create ca priavte.key and cert file from same Csr so CN will be same. And where in doc is mentioned to have different CN

20

https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

Important
Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate.

openssl cert command will create ca priavte.key and cert file from same Csr so CN will be same

That is not true. Check out the README for our training materials that shows how to create SSL certificates with different CNs training-aws/packer/etc_ssl_mysql/README.md at master · percona/training-aws · GitHub