0

I am using Python 3.7 and for a few years I have had a Python script that connected to an SQL server with the below code:

con_string = 'DRIVER={SQL Server};SERVER='+ server +';DATABASE=' + database
cnxn = pyodbc.connect(con_string)
cursor = cnxn.cursor()

Recently when running the script I get the below error:

OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error (18) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (SECCreateCredentials()). (1)')

I am not quite sure how to interpret this and have tried looking for solutions but had trouble understanding much of it?

Thanks

Update 18 August 2020

With Gords help I have updated my drivers and built a new connection string based on the answer below:

Pyodbc error Data source name not found and no default driver specified paradox

con_string = 'DRIVER={ODBC Driver 17 for SQL Server};TrustServerCertificate=No;Network=DBMSSOCN;DATABASE='+database+';WSID=L13-CHRISTOPHER;APP={Microsoft® Windows® Operating System};Trusted_Connection=Yes;SERVER='+server+';'

But I am still getting the error

OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error (18) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (SECCreateCredentials()). (1)')

Thanks

Christopher Ell
  • 1,878
  • 3
  • 16
  • 25
  • The "SQL Server" driver is very old. Try using a more modern driver like "ODBC Driver 17 for SQL Server". – Gord Thompson Aug 13 '20 at 11:04
  • Thanks Gord, I replaced "SQL Server" with "ODBC Driver 17" and got a new error message InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') – Christopher Ell Aug 14 '20 at 00:22
  • You need to install a driver before you can use it. Check the list returned by `pyodbc.drivers()` to see what drivers are currently available to your Python app. – Gord Thompson Aug 14 '20 at 02:43
  • Thanks again Gord. I have updated the driver and it still wasn't working. So I found the below link and used the method to rebuild my connection string:https://stackoverflow.com/questions/32662123/pyodbc-error-data-source-name-not-found-and-no-default-driver-specified-paradox But the new connection string which worked on the test when buildin it is giving me the operational error again – Christopher Ell Aug 15 '20 at 12:36
  • New connection string in an edit in original question. – Christopher Ell Aug 15 '20 at 12:42

1 Answers1

1

Method-1:- Check this out:

con_string = pyodbc.connect(driver='{SQL Server}', host=server, database=db1, trusted_connection=tcon) cursor = cnxn.cursor()

If Method-1 doesn't work for you then maybe your SQL Server drivers require TLS 1.0 which would be disabled on your computer. Changing to the newest version of SNAC will fix the problem for you

con_string = pyodbc.connect(driver='{SQL Server Native Client 11.0}', host=server, database=db1, trusted_connection=tcon)

Ansh
  • 58
  • 6