Invalid connection string attribute on MS SQL 14 with ODBC Driver 17

[GeiserX]

Issue Description and Expected Result

I have connected to a wide range of MS SQL Server without problems, but I have a particular problem with a given MS SQL v14 using the ODBC Driver 17 for SQL Server.

After connecting with

con <- dbConnect(odbc::odbc(),
                 Driver = "ODBC Driver 17 for SQL Server",
                 Server = "my.site,5000",
                 UID    = "user",
                 PWD    = "pass",
                 Port   = "5000",
                 Database = "mydb")

The error I'm getting is

Error: nanodbc/nanodbc.cpp:1021: 00000: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746  [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection  [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute
Execution halted

I tried any combination, however, I tried with SSMS for Windows and it connected smoothly, so it should not be a problem with the database.

After sniffing with Wireshark, the payload is almost the same if we compare it to the way it is being done in SSMS:

(In red, ODBC connection, in green, SSMS connection)
But somehow the connection string is 155bits larger in the ODBC connection.

Database

SQL Server 2014

More context

EDIT: I have tried with the same driver using Python 3. It's working without any issues:

>>> import pyodbc
>>> server = "whatever,5000"
>>> database = "mydb"
>>> username = "myuser"
>>> password = "mypass"
>>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
>>> cursor = cnxn.cursor()
>>> cursor.execute("SELECT @@version;")
<pyodbc.Cursor object at 0x7f338f180300>
>>> row = cursor.fetchone()
>>> while row:
...     print(row[0])
...     row = cursor.fetchone()
...
Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)
        Jul 20 2019 21:42:29
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

EDIT 2: It seems it's only happening if the client is in Ubuntu 20.04. Ubuntu 18.04 is free of this bug. I installed OpenSSL 1.1.1 onto Ubuntu 20.04 and the error persists.

[shrektan]

If the driver is ODBC Driver 17 for SQL Server and you MS SQL Server's version is 2014, it's probably due to the SSL version's problem.

In short, the MS SQL Server 2014 uses a out-dated SSL version but ODBC Driver 17 forces to use a newer one by default.

The solution is to replace the content of your /etc/ssl/openssl.cnf to this openssl.cnf.txt

See the disscussion below.

microsoft/msphpsql#1112 (comment)

(The reason that I know this is I faced the same case in Jan 2021, and it costs me 2 hours to debug this ... a totally waste of time... 🤷 )

@DrumSergio Please let me know if the solution works for you or not, thanks!

[pkbarbiedoll]

"The solution is to replace the content of your /etc/ssl/openssl.cnf to this openssl.cnf.txt"

This sounds like a terrible idea especially on STIG configured systems.

[hadley]

Thanks @shrektan !