0

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

Improve this question

1 Answer 1

Reset to default
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!

Improve this answer
12
  • 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)
    – KodeNode
    Commented Jan 6, 2021 at 16:23
  • 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
    Commented Jan 7, 2021 at 7:59
  • 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.
    – CoderRambo
    Commented Jan 7, 2021 at 8:04
  • 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.
    – KodeNode
    Commented Jan 7, 2021 at 12:09
  • ok if its the same account, can you please tell me your SQL server and windows server versions?
    – CoderRambo
    Commented Jan 7, 2021 at 12:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.