I have a python script using pyodbc that connects to a remote server with sql server running on it. I have a package I wrote with functions using sqlalchemy that I was able to use on one of my computers. I connected with this string:
driver = 'SQL+Server+Native+Client+11.0'
engine_string = prefix + '://' + username + ':' + password + '@' + server + '/' + database + '?driver=' + driver
On another computer, I was not able to install the native client 11.0 which I understand is deprecated. I tried switching the value to
driver = 'ODBC+Driver+18+for+SQL+Server'
I got an error with that version
[ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.
I then tried just a generic odbc connection with the windows utility and got the same error. I was able to get that odbc manager connection to work when I checked 'Trust Server Certificate'
This is probably not good long term, but is there a way to add that attribute to the first string I have above? I tried several variations, but nothing worked.
I was able to get a working connection with the following:
cnxn = pyodbc.connect(
driver = '{ODBC Driver 18 for SQL Server}',
server = server,
database = database,
uid = username,
pwd = password,
encrypt='no',
trust_server_certificate='yes')
but that connection did not work with the package I wanted to use. thanks!
The connection error is due to a change in default behavior for the newest versions of SQL Server Drivers (ODBC v18+, JDBC v10+, .Net Microsoft.Data.SqlClient v4.0+).
ODBC release notes: https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228
The correct ODBC keyword to use is TrustServerCertificate