CODEWITHSUNDEEP
pythonsql-serverodbcpyodbc
kev15
kev15

Reputation: 71

OperationalError when trying to connect to SQL Server database using pyodbc

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.

Upvotes: 6

Views: 20160

Answers (5)

halucka
halucka

Reputation: 41

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

Upvotes: 0

Alèxia Losada Fors
Alèxia Losada Fors

Reputation: 1

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)

Upvotes: 0

Muhammad Sohail
Muhammad Sohail

Reputation: 7

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

Upvotes: 0

kev15
kev15

Reputation: 71

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.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89071

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

Upvotes: 10

Related Questions