Issue connecting to SQL Server via Python pyodbc

Question

I have an ongoing issue that I just can't solve. It concerns a Python script that loads data into a SQL server database.

import pyodbc 

conn = pyodbc.connect(r'Driver={SQL Server};'
                      r'Server=tcp:MY-SRV-NAMEABC,49133;'                       
                      r'Database=MyDatabase;'
                      r'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT coalesce(max(NextDate),?) FROM [dbo].[TableName]',b)

When I run it from my local machine it works fine, however when I run the same script from a server I get the following error:

conn = pyodbc.connect(r'Driver={SQL Server};' pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error (18) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()). (1)')

This is using the same user account both locally & on the server. Can anyone help out?

Apologies if this appears like a duplicated issue, I've read through many similar sounding issues on StackOverFlow, but none of the solutions help. I know the code is fine as it runs ok locally, but I just can't get it running from the server.

Any advice would be very much appreciated.

Thanks

Answer 1

I too faced it some time back and did the following , please try and let me know:

Edit /etc/ssl/openssl.cnf add or make the following change and let me know.

MinProtocol = TLSv1.0
CipherString = DEFAULT@SECLEVEL=1

or if its a window machine it could be a driver issue kindly change the driver to any one of the following and check

driver='{SQL Server Native Client 11.0}',  
or driver={ODBC Driver 17 for SQL Server};

Also note you might need to download & install the windows odbc driver as given in the below link

https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-ver15#installing-microsoft-odbc-driver-for-sql-server

thanks!