Unable to connect to SQL 2019 using Pyodbc

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

     

    • This topic was modified 5 months, 3 weeks ago by  yyang5823.
  • 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

  • Finally I resolved the issue with following steps:

    1. Use PowerShell command Net-SelfSignedCertificate to create a new certificate
    2. Add new certificate to the "Trusted Root Certificate Authories" in Certificate Tool in Windows
    3. Run SQL Server Configuration Manager and configure the certificate
    4. Starting the SQL Server Service with option of "Encrypt Connections"

    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