Problem with connectin to SQL server

Question

I have 1 server with SQL Server Express and 2 working stations. I wrote a program on Python that work with SQL Server by PYODBC module. It works fine on server, it works fine on my PC station where SSMS, Python etc. is installed. It doesn't work on 2nd station. I installed ODBC Driver on that machine, configure connection string. I even added TrustServerCertificate=True in the string. I still get an error. connection line looks like this: DRIVER={ODBC Driver 18 for SQL Server};Server=ServerName;Database=DataBaseName;UID=LogIn;TrustedServerCertificate=1;PWD=P@$$w0rd Error: 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);.......Client unable to establish connection (-2146893019) What the complications with connection? Why it works on machines where SSMS installed but not where ODBC driver? How to fix issue avoiding to install unnecessary software on each machine?

Answer 1

TrustedServerCertificate=1 should be TrustServerCertificate. That is, you have a misspelling, and I don't think the option takes any argument. At least, the documentation does not say so. ...although, even better is of course to install a proper certificate and make sure that it is in the TrustedCertificates node in the certificate store of all clients. I found this article helpful when I played with this some time back: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=create-install-ssl-tls-certificate-sql-server.

Answer 2

Hi @Maksym Rohozinskyi ,

The error message you are seeing indicates that the certificate chain was issued by an authority that is not trusted. This could be due to a number of reasons such as the certificate being expired or not being installed on the machine.

Another possibility is that the ODBC driver version installed on the second station is different from the one installed on your PC station where SSMS and Python are installed. You can check this from Control Panel.

Please refer to this similar thread:

https://stackoverflow.com/questions/71587239/operationalerror-when-trying-to-connect-to-sql-server-database-using-pyodbc

Download ODBC Driver for SQL Server: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".