'SSL connection is required' error when connecting to Azure MySQL database in R

[Kevin_Silberberg]

When I try to run the following code (credentials omitted) in R which connects to an Azure MySQL database, I receive an SSL error:

library(DBI)

con <- dbConnect(RMySQL::MySQL(),
                 dbname = dbname,
                 host = host,
                 port = port,
                 user = user,
                 password = password)

Error in .local(drv, ...) : 
  Failed to connect to database: Error: SSL connection is required. Please specify SSL options and retry.

I'm able to connect to the database from my IP on MySQL Workbench.

If I disable the SSL requirements on Azure, the error disappears. I've attempted using the RMariaDB connector as well as adding sslmode = 'require' and I still get the exact same error.

What's particularly strange about this error is the above code snippet works completely fine when I run it in RStudio Cloud. However, it doesn't work when I run it on my local RStudio or RGui session. I've attempted it on another computer and I get the same error, only RStudio Cloud seems to work.

Here's my sessionInfo():

R version 3.6.3 (2020-02-29)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18363)

Matrix products: default

locale:
[1] LC_COLLATE=English_Canada.1252  LC_CTYPE=English_Canada.1252    LC_MONETARY=English_Canada.1252 LC_NUMERIC=C                    LC_TIME=English_Canada.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] DBI_1.1.0

loaded via a namespace (and not attached):
[1] compiler_3.6.3 tools_3.6.3    RMySQL_0.10.20 packrat_0.5.0

[tmr207]

I got the same problem.scratched my head for two days and finally i'm able to connect. Follow the below steps.

1. you can Download required MariaDB Driver from the below link
   https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.6/

2. then install RMariaDB library in Rstudio

3. Download the below file
   https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem

now after that run the below code with required credentials and path for pem file

library(RMariaDB)
mydb <- dbConnect(
RMariaDB::MariaDB(),
username=" ",
password=" ",
dbname=" ",
host=" ",
port= ,
ssl.ca="Path/to/the/file/BaltimoreCyberTrustRoot.pem.cer"
)