OperationalError when trying to connect to SQL Server database using pyodbc

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

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.

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

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)

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