Connection error with SQL Server 2012 with Python

Asked

Viewed 475 times

3

I am trying to connect to SQL Server 2012 using Python 3.7 and Ubuntu 20.04.1 LTS as follows:

import pyodbc
server = 'tcp:192.168.0.7, 1433'
database = 'teste'
username = 'teste'
password = 'teste'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

While trying the connection I get the following error message:

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) pyodbc.Operationalerror: ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:Unsupported Protocol] (-1) (Sqldriverconnect)')

I read a topic in git about and even got to test a downgrade in openssl but still could not make it work.

I have 2 versions of SQL inserir a descrição da imagem aqui I just noticed that: In version 11.0.2100.60 and I have the error I described at the beginning. In version 11.0.6020.0, I noticed now that I get a different error:

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+' ;PWD=' + password+';Encrypt' + Encrypt) pyodbc.Operationalerror: ('08001', '[08001] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (Sqldriverconnect)')

python python-3.x sql-server linux

  • To install ODBC on Ubuntu 20.04, did you follow the steps of "Install Microsoft ODBC Driver for SQL Server (Linux)"? -> https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sqlallproducts-allversions

  • Also check that SQL Server 2012 instance has installed support for TLS 1.2. " TLS 1.2 support for Microsoft SQL Server" -> https://support.microsoft.com/en-us/help/3135244/kb3135244-tls-1-2-support-for-microsoft-sql-server

  • 1

    To install the ODBC Driver for SQL Server (Linux) was exactly this step by step that I followed, I will check the second link.

  • @Josédiz, from what I understand on the microsoft site my version of SQL does not support the correct connection? I added 2 instances I have in different versions, in the newer version I noticed that the error is different.

  • Are you sure the variable server is correct? It shouldn’t just be server = '192.168.0.7'?

  • @Paulomarques Yes, link I consulted at doc

  • @Wilian, based on documentation # server = 'myserver,port' # to specify an alternate port has no space after the comma. It doesn’t hurt to try...

  • @Paulomarques test, same error, tried like this: server = 'myserver,port', server = 'tcp:myserver,port' server = 'tcp:myserver' in new sql version always the error ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (Sqldriverconnect)')

  • @Wilian, based on the first message, the error is SSL, no problem with the connection, but with the negotiation... See this post

  • from what I read in git, what happens is that there is an attempt being made to connect with tsl 1.2 and the version of the updated sql server only allows 1.0, it would be interesting to update the bd or downgrade the driver

  • @Willian The images you added in the description do not refer to the version of SQL Server but to the components of Management Studio.

Show 6 more comments

2 answers

1

It is necessary to check whether the instance with SQL Server 2012 handles the TLS v1.2 protocol. The first step is to find out what the update level of SQL Server software is. One simple way is

PRINT @@version

In order for the TLS v1.2 protocol to be recognized, it is necessary to evaluate which service pack is installed and what the update level is. If 2012 SP2 should apply the patch 2012 SP2 CU10 that will make build# 11.0.5644.2. But if it is 2012 SP3 then one should apply the patch 2012 SP3 CU1 that will make build# 11.0.6518.0. But the best is to apply the patch 2012 SP4 and then the 2012 SP4 GDR, which will have all updates available for SQL Server 2012.

If you cannot update the SQL Server 2012 software, then you can configure to have Openssl accept TLS v1.1 or v1.0.

Reading suggestion:

  • TLS version issue solved, thank you.

0

Browser other questions tagged python python-3.x sql-server linux

You are not signed in. Login or sign up in order to post.