Having hard time to connect to remote Database using SSL keys

Install / Update Setup
1

So, I am having a little bit of hard time connecting to remote database using SSL keys.

I was successfully able to setup ERPNext with remote database without SSL keys. But using SSL keys it is throwing me an error:

pymysql.err.OperationalError: (1045, “Access denied for user ‘_1bd3e0294da19198’@‘ip_address’ (using password: YES)”)

Now, when I try to connect via command line:

mysql -h ip_address -u root -p --ssl-ca=/home/ca.pem --ssl-cert=/home/client-cert.pem --ssl-key=/home/client-key.pem

It successfully connects.

I configured the keys on .my.cnf for my current user frappe

[client]
ssl-ca = /home/ca.pem
ssl-cert = /home/client-cert.pem
ssl-key = /home/client-key.pem

and I can connect using command line:

mysql -h ip_address -u root -p

But, when I modify common_site_config.json by adding

“db_host”: “ip_address”,
“db_port”: 3306,
“ca”: “/home/ca.pem”,
“cert”: “/home/client-cert.pem”,
“key”: “/home/client-key.pem”,

and then executing:

bench new-site remote.site OR
bench new-site remote --mariadb-root-username root --mariadb-root-password password

I get the error

pymysql.err.OperationalError: (1045, “Access denied for user ‘root’@‘ip_address’ (using password: YES)”)

2

Okay, I fixed it.

I am going to write the solution here in case anyone is having hard time.

On common_site_config.json replace what is above with this:

“db_host”: “ip_address”,
“db_port”: 3306,
“db_ssl_ca”: “/home/ca.pem”,
“db_ssl_cert”: “/home/client-cert.pem”,
“db_ssl_key”: “/home/client-key.pem”,

Also if you are having another error after this like:

ssl.CertificateError: hostname ‘ip_address’ doesn’t match ‘MySQL_Server_5.7.29_Auto_Generated_Server_Certificate’

Modify apps/frappe/frappe/database/mariadb/database.py from:

self.ssl = {
‘ca’:frappe.conf.db_ssl_ca,
‘cert’:frappe.conf.db_ssl_cert,
‘key’:frappe.conf.db_ssl_key
}

to:

self.ssl = {
‘ca’:frappe.conf.db_ssl_ca,
‘cert’:frappe.conf.db_ssl_cert,
‘key’:frappe.conf.db_ssl_key,
‘check_hostname’: False
}

Anf then execute

bench new-site remote1.site

You are all set.

3 Likes