SSL routine: tls_process_server_certificate:certificate verify failed - Adding support for TrustServerCertificate=yes

[locorecto]

I am using bcpandas on SQL Server 2018 and using the same driver version. The db server I am connecting to server uses a self signed certificate. I am getting this error when I run to_sql:

SSL routine: tls_process_server_certificate:certificate verify failed: self signed certificate

We deal with this in pyodbc and sqlalchemy by adding TrustServerCertificate=yes in the connection string.

However, the same doesn't work in bcpandas. I tryied adding odbc_kwargs when I create the SqlCreds object like this

SqlCreds(server=server, database=db, username=uname, password=pwd, odbc_kwargs=dict(TrustServerCertificate='yes')

but it didn't work.

bcp documentation specifies you need to add the -u flag to Trust server certificate. But this is not available in bcpandas.

Any workarounds?

[locorecto]

I was able to bypass certificate verification this by adding odbc_kwargs={'encrypt': 'no'} to my SqlCreds constructor. Again, only do this is you trust the server you a connecting to and the communication is behind a firewall.

[gordthompson]

Duplicate of #126