OperationalError when trying to connect to SQL Server database using pyodbc

Question

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.

Elytron

To which version of SQL Server are you connecting? The error message suggests that it's a very old version, or not up-to-date with service packs or cumulative updates, if it's not supporting TLS 1.2 - that's what ssl_choose_client_version:unsupported protocol is telling you. If you can't update the remote server you'll have to see if you can enable TLS 1.1 support in the OpenSSL cnf file inside WSL. TLS 1.0 and TLS 1.1 are disabled by default in modern stacks (and sometimes not even compiled in) because they're considered insecure. – Comose

SQL Server version: Microsoft SQL Server 2008 (SP4) - 10.0.6241.0 (Intel X86) Apr 17 2015 11:02:30 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM) I tried enabling TLS 1.0 or 1.1 as described here, but that did not solve the problem. Still the same error message. – Elytron

SQL Server 2008 SP4 didn't support TLS 1.2 until 10.0.6547.0. You should talk to the server's administrator about installing the TLS 1.2 update. Although SQL Server 2008 hasn't been supported by Microsoft for a number of years, now, you should urge them to upgrade to a newer supported version. – Comose

Even old encryption is better than no encryption at all. There is a certain method required to update the OpenSSL cnf file properly. Take a look at my previous SO answer to see if that helps you to enable TLS 1.1. – Comose

Answer 1

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')

Deicer

This seems like a good start. If I include Encrypt=no, the error gets a bit shorter: OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol] (-1) (SQLDriverConnect)') (the last part with SSL_do_handshake:peer did not return a certificate is gone). – Elytron

See updated answer. – Deicer

Thanks, but I still get the same error even with Encrypt=no;TrustServerCertificate=yes – Elytron

SQL Server 2008 is out of support, so this might not be a working combination. The older ODBC drivers are still available for download. – Deicer

Answer 2

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.

Elytron

Answer 3

I used the pyodbc library, but kept getting errors. Using pymssql worked for me:

import pymssql
conn = pymssql.connect(server='172.30.1.19')
cursor = conn.cursor()
cursor.execute(query)
rows = cursor.fetchall()

No need for port, username and password

Melissamelisse

Answer 4

For SQL Servers older than 2016, try using JDCB driver instead of ODBC driver.

Maven JDBC:

com.microsoft.sqlserver:mssql-jdbc:8.4.1.jre8

Python / Pyspark code:

jdbcHostname = "SERVER_NAME"
jdbcPort = 1433
jdbcDatabase = "DATABASE_NAME"
jdbcUsername = "USERNAME"
jdbcPassword = "PASSWORD"

jdbcUrl = f"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};database={jdbcDatabase}"

connectionProperties = {
    "user": jdbcUsername,
    "password": jdbcPassword,
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

df = spark.read.jdbc(url=jdbcUrl, table="TABLE_NAME", properties=connectionProperties)

Lydgate

Answer 5

This goes beyond the scope of the original question. Preferably it would be a comment on the answer by @david-browne-microsoft, but I can't post a comment yet.

For those using sqlalchemy (especially versions older than 1.4 that don't have sqlalchemy.engine.URL.create method, otherwise see How do I use SQLAlchemy create_engine() with password that includes an @), you can connect like this:

engine = create_engine('mssql+pyodbc://{0.username}:{0.password}@{0.host}/{0.database}?Encrypt=no&TrustServerCertificate=yes&driver={0.driver}&charset=utf8&port={0.port}'.format(options))

So Encrypt=no&TrustServerCertificate=yes can simply be added to the query parameters. I kept seeing trusted_connection=yes in other answers to related questions, but that did not work for me. The reason for trusting the connection here is that it is a development database on localhost with self-signed certificates, this is not safe for production.

In my connection string the driver parameter should be already URL encoded, e.g. 'ODBC+Driver+18+for+SQL+Server'.

Lasser