7

I am trying to connect to my database on MS SQL Server 2016 using pyodbc via the below python script from my laptop (on Windows 10) and planning to have the code deployed in a Linux RHEL 6.4 server.

conn=pyodbc.connect('Driver={SQL Server};'
                'Server=DB_Instance;'
                'Database=DB_Name;'
                'UID=user_name;'
                'PWD=password;'
                'Trusted_Connection=no;');

At my laptop, SQL Server (version: 10.00.17763.01) and SQL Server Native Client 11.0 (version: 2011.110.7493.04) are already available.

While executing the python script from my laptop, I am getting the below error message.

pyodbc.operationalError: ('08001', '[08001] [Microsoft] [ODBC SQL Server Driver][DBNETLIB]SSL Security error (18) (SQLDriverConnect); [08001] [Microsoft] [ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECDoClientHandshake()). (772)')

As per the organization standard, TLS 1.0 is disabled on the windows server where the SQL Server is installed in the network. Since I am accessing the database via python script, we cannot temporarily enable TLS 1.0. I am looking for a direction. Any help is greatly appreciated!!

Guna
  • 71
  • 2
  • 8
  • 3
    Can you try using ODBC Driver 17 for SQL Server and see if that works for you? – Gord Thompson Jun 28 '20 at 21:24
  • Thank you Gord. This fixed my issues in my laptop on Windows 10. Will see how this can be done in Linux server and keep it posted. Appreciate your help! – Guna Jun 30 '20 at 09:50
  • 3
    I was facing different set of errors. While adding the port number in addition to the server name, the problem got resolved. The ODBC Driver name also needs to be updated. `conn=pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DB_Instance,port;' 'Database=DB_Name;' 'UID=user_name;' 'PWD=password;' 'Trusted_Connection=no;');` – Guna Jul 04 '20 at 12:03

2 Answers2

4

Gord Thompson pointed Guna in the right direction in the comments:

Can you try using ODBC Driver 17 for SQL Server and see if that works for you?

– Gord Thompson

Guna said that this worked:

I was facing different set of errors. While adding the port number in addition to the server name, the problem got resolved. The ODBC Driver name also needs to be updated. conn=pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' 'Server=DB_Instance,port;' 'Database=DB_Name;' 'UID=user_name;' 'PWD=password;' 'Trusted_Connection=no;');

– Guna

Posting this as a community Wiki just so that any searchers can see quickly that it was answered.

Abraham
  • 479
  • 7
  • 23
  • Literally all I changed was from "{ODBC Driver 18 for SQL Server}" to "{ODBC Driver 17 for SQL Server}" and it started working. Both 17 and 18 are installed, but only 17 works for me on my mac. – thedude Mar 07 '22 at 16:59
  • I believe the reason that 17 works and 18 does not is because in 18 the connection option for 'encrypted' is set to 'yes' by default. In 17, it is not. Sending unencrypted credentials anywhere beyond the box it's on is not secure and not recommended. – Hanny Jul 13 '22 at 20:02
-1

PROBLEM: - When trying to perform password change for MSSQL server, you get the following error message:-SSL Security error

SOLUTION: - It's likely an issue with the version of the SQL Native Client driver being used to establish the connection.

  1. Please Download & install "ODBC Driver 13 for SQL Server" on the CPM servers. https://www.microsoft.com/en-us/download/confirmation.aspx?id=50420 Download 32 bit driver : x86\msodbcsql.msi

  2. Install as usual

  3. Edit MSSQL Platforms to change the connection command from Driver={SQL Server};Server=%ADDRESS%;Database=%DATABASE%;Uid=%USER%;Pwd=%LOGONPASSWORD%; to Driver={ODBC Driver 13 for SQL Server};Server=%ADDRESS%;Database=%DATABASE%;Uid=%USER%;Pwd=%LOGONPASSWORD%;

  4. Restart the CPM service to pick up the platform changes & retest

Sivaramakrishnan Sundaram
  • 1