1

I'm trying to connect to a MSSQL database; the issue is that on the network where this script is ran, we use a self-signed certificate internally and I think pyodbc is not happy with that.

The database I am connecting to (MSSQL) lives outside our network - and when I try to connect to the database by running my script on a server (Windows 2016), I get the following errors:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.

I'm not sure how to get pyodbc to recognize that our self-signed cert is 'to be trusted'.

The connection string looks like this:

def get_db_cursor():
    driver = os.environ.get("ODBC_DRIVER") # ODBC Driver 17 for SQL Server
    host = os.environ.get("db_HOST")
    db_name = os.environ.get("db_NAME")
    db_user = os.environ.get("db_USER")
    db_pass = os.environ.get("db_PASSWORD")
    db_port = os.environ.get("db_PORT")
    connection = pyodbc.connect(
        "DRIVER="
        + driver
        + ";SERVER="
        + host
        + ";PORT="
        + db_port
        + ";DATABASE="
        + db_name
        + ";UID="
        + db_user
        + ";PWD="
        + db_pass
        + ";Encrypt=yes;"
    )
    db_cursor = connection.cursor()
    return db_cursor

I use Encrypt=yes because it's leaving our network and I'd like it encrypted.

I can use TrustServerCertificate=True, but that is not the correct/safe way to do it (but it would work).

I am curious if anyone knows of or has any other ideas on how to get pyodbc to accept a self-signed cert?

Maybe I'm going about this all wrong - but I just can't seem to get this to not throw that error. All help is appreciated.

Hanny
  • 580
  • 3
  • 16
  • 44
  • 2
    The issue with a self-signed cert is you must trust it, even if it's the a not the correct/safe approach. The correct/safe method is to avoid using a self-signed cert and use one issued by a trusted authority. – Dan Guzman Jul 18 '22 at 14:58
  • A slightly less bad idea than that might be to import the self-signed cert into Python's list of trusted certificates, wherever that is. But yes, if you have an SQL Server on the global internet you really should have a proper certificate – Charlieface Jul 18 '22 at 15:12
  • I agree with both of you - that using a cert from a trusted authority is ideal. I believe the self-signed cert approach is taken for a network wide security purpose; is there a way to see where Python is looking for that list of trusted certs @Charlieface? So that I might add the self-signed cert to that list? – Hanny Jul 18 '22 at 15:35
  • It depends on the Python client's host operating system as to where that is. e.g.: when running on Windows 10/11, in the account under which the Python process executes, you'd use the Manage Certificates snap-in to import the self-signed certificate's public key into the user's Trusted Root Certificates store. – AlwaysLearning Jul 18 '22 at 21:15

0 Answers0