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-NAME\ABC,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

CoderRambo · Accepted Answer · 2021-01-08 15:30:05Z

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://learn.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!

edited Jan 8, 2021 at 15:30

answered Jan 6, 2021 at 15:28

CoderRambo

4023 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

KodeNode Over a year ago

Hi, thanks for the idea, do you know where I might find this config file? Also I should mention that the server I'm using is Windows based. OpenSSL is not installed on the server either (assuming the config file you mention, relates to this tool)

CoderRambo Over a year ago

Apologies I should have asked you the OS details. With windows I am not sure about this config. However a few suggestions please see if this helps you. I see you are using a 'Trusted_Connection=yes;' since from the OS where it ran successfully I think your username had permission to connect SQL server. Can you reframe your connection as below: cnxn = pyodbc.connect(driver='{SQL Server}', host=server, database=db1,user=uname, password=password) And use some generic username like sa and connect.

CoderRambo Over a year ago

While using 'Trusted_Connection=yes;' it uses windows authentication, so when using this we need to be sure if the user who is logged on when running the code should have permission to connect to SQL server , if not we need to ask DBA to provide the permission to user. In your case , your userid had the permission to connect to SQL server, and when you migrated the code to the remote server I guess the user with which you ran the code did not have permssion. I hope this should solve otherwise let me know we can check whats wrong.

KodeNode Over a year ago

Thanks CoderRambo, really appreciate the suggestions. Unfortunately we know its not permissions as the account used to run locally, was the same used as the account used to run this script on the server. This has been tested.

CoderRambo Over a year ago

ok if its the same account, can you please tell me your SQL server and windows server versions?

|

Collectives™ on Stack Overflow

Issue connecting to SQL Server via Python pyodbc

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related