May 20, 2024 at 5:04 pm
Development Environment
I am learning how to use pyodbc and accessing database in SQL server 2019. Here are the software versions I am using:
SQL server 2019
VS Code 1.89.1
Microsoft ODBC driver 18 for SQL Server
Python 3.12.3
pyodbc 5.1.0
Developer laptop: Windows 11
Connection Errors:
When running python test program in VS, it could not connect to SQL DB with following connection string:
DRIVER={ODBC Driver 18 for SQL Server};SERVER=DESKTOP-3K7VIS6;DATABASE=my_db;UID=xxxx;PWD=yyyy
When executing statement: connection=pyodbvc.connect(db_conn_str), the error messages are:
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722 (-2146893019)')
Related tests:
Used the same connection parameters, I configured the "User DSN" and "System DSN" with option of "trust server certificate". These DSNs were to connected to SQL DB successfully. This means the server name, db name, UID and PWD are connect in connection string.
I tried to change UID & PWD to UserID & Password. They didn't make any difference.
Questions:
According to the error message, it looks like the connection falure was related to SQL server certificate. When using DSN, there is an option for "trust server certificate". But, in connection string with ODBC, this option doesn't exist.
When connecting to SQL server in C#, the connection string has option of "TrustServerCertificate=true". I tried to add it to the connection string in python program. It errored out following message:
pyodbc.OperationalError: ('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'TrustServerCertificate' (0) (SQLDriverConnect)")
I need help to resolve this connectivity issue. Any suggestions and advices will be greatly appreciated.
May 20, 2024 at 7:21 pm
You are correct in thinking that you need the TrustServerCertificate=true bit in your connection string. The latest version of SQL Server depends on it, unless you have a server cert properly configured.
I don't use ODBC for SQL Server connections. Do you have the option of using the 'MS OLEDB Driver' connection instead. Your conn string would be something like this:
Provider=MSOLEDBSQL.1;Password=<pwd>;Persist Security Info=True;User ID=<UID>;Initial Catalog=<DBName>;Data Source=<Servername>;Trust Server Certificate=True
(You might have to remove the space characters in Trust Server Certificate – I've seen some odd behaviour with that option recently.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 21, 2024 at 3:14 am
Finally I resolved the issue with following steps:
Once the cert is properly configured, I not longer need to use "Trust Server Certificate" option. The DB connection in python program is successful now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply