Hi,
I enabled SSL on my cluster using a LetsEncrypt certificate (generated by certbot), and then I set REQUIRE SSL on the root user. Now an upgrade to SingleStore is available and when I run sdb-admin upgrade, I get Error 2455: Access denied for user 'root'@'localhost' (using password: YES). The user is configured with REQUIRE SSL but the connection is not SSL. See https://docs.memsql.com/docs/ssl for information on configuring SSL..
I don’t think I missed anything in the docs but I can’t seem to find a way to make memsqlctl and sdb-toolbox use SSL. Could anybody point me in the right direction?
Thanks,
Boud
Hello @BaloneyGeek !
Thank you for reaching out!
To use memsqlctl with root user set to REQUIRE SSL you should specify path to the CA certificate file in PEM format to authenticate the server certificate in memsqlctl config file. For more information on memsqlctl config file please refer to memsqlctl · SingleStore Documentation.
To discover memsqlctl config file path on the host host1 consider running: sdb-toolbox-config describe-host --host host1 .
Then connect to the host and add the following line
sslCaFile = "/path/to/CA/file"
to the content of the config file, specifying correct path.
For upgrade to succeed, the memsqlctl should be configured this way on all hosts, where at least one node has root user set to REQUIRE SSL.
Hi @mshcherbina,
This was a promising hint, but unfortunately it didn’t work - I get the same error.
As this was a LetsEncrypt certificate the CA certificate I used was the Root X1 Certificate from Chain of Trust - Let's Encrypt - I hope this was the correct one.
I still get the exact same error, btw - so I don’t know if the CA certificate is wrong or if something else is wrong.
Hi @BaloneyGeek!
Please make sure to check SSL intra-cluster communication guide SSL Secure Connections · SingleStore Documentation, ssl_ca variable should contain the same path to the valid CA certificate as well.
To verify that CA is valid for your server certificate, consider using openssl verify -verbose -CAfile path/to/CA/file /path/to/server/certificate or connecting to the node using singlestoredb client with --ssl-ca option.
Could you please specify the current and target server versions?
Thank you!
Hi @mshcherbina,
Sorry to take 21 days to respond, I was busy with other real-life stuff. However, I’ve finally managed to solve this problem and I thought I’d leave the solution here for others to find, and if you’d like to put it into official documentation (I imagine using LetsEncrypt is a fairly common wish for hobbyists).
So the biggest clue was using openssl to verify the chain. Of course that didn’t work, which told me why I wasn’t able to connect to the cluster, but it didn’t tell me how to fix it. Turns out there’s a slightly complicated route you need to take.
In your /etc/letsencrypt/live/<site>/ directory (if you’re using certbot) you’ll find a bunch of certificate files. There’s two important ones there: cert.pem (which is the actual certificate) and chain.pem (which is the CA and intermediate chains). The concatenation of the two is the fullchain.pem. However, using openssl verify -CAfile chain.pem cert.pem will not work, because the Identrust’s DST Root CA X3 isn’t in that chain. So we’ll have to build a new CA chain.
To do this, I downloaded the TrustID X3 Root certificate from here: https://letsencrypt.org/certs/trustid-x3-root.pem.txt and then concatenated this with the chain.pem (putting the TrustID certificate after the chain.pem) to create a new ca.pem file. Using this ca.pem, openssl verify worked. After setting the configuration in the memsqlctl hcl file and the memsql.cnf files, the upgrade finally worked 
I hope this information is of some help to others.