I'm trying to connect to a SQL server database using pyodbc in Python 3. But I get an error when I'm trying to establish the connection.

I do something like this:

import pyodbc
conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=192.168.2.250;Database=DB;UID=username;PWD=password;')

And I get this:

OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol][error:140B40C7:SSL routines:SSL_do_handshake:peer did not return a certificate] (-1) (SQLDriverConnect)')

Does anybody know how to solve this? The database is not my own, so I hope there is a solution that doesn't require changing any settings there.

I'm running Ubuntu within the Windows Subsystem for Linux.

CodePudding user response：

There is a breaking change in ODBC Driver 18 for SQL Server

Similar to the HTTP to HTTPS default changes made in web browsers a few years back (and the security reasons for them), we are changing the default value of the Encrypt connection option from no to yes/mandatory.

ODBC Driver 18.0 for SQL Server Released

So this

conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=192.168.2.250;Database=DB;UID=username;PWD=password;')

is the same as

conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=192.168.2.250;Database=DB;UID=username;PWD=password;Encrypt=yes')

If you don't want an encrypted connection you must opt out:

conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=192.168.2.250;Database=DB;UID=username;PWD=password;Encrypt=no')

We also changed the behavior of TrustServerCertificate to not be tied to the Encrypt setting

So if your server is using a self-signed certificate, you also must opt out of certificate validation. so

conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=192.168.2.250;Database=DB;UID=username;PWD=password;Encrypt=no;TrustServerCertificate=yes')

CodePudding user response：

I ended up taking my script out of WSL. Running the same command (with David's additions or ODBC Driver 17 for SQL Server instead of 18) under Windows works without issues in my case.