1

I'm trying to connect to an MS SQL Server 2016 from my ubuntu server 22.04.

I've installed the odbc driver following the official MS documentation: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16

The installation works fine and I can connect to newer MS SQL servers, the problem is to connect to 2016 SQL Server, looks like an issue with OpenSSL version. Ubuntu 22.04 uses by default the openssl 3.x.x.

I've enabled some debug options by adding to /etc/odbcinst.ini

[ODBC]
Trace = Yes
TraceFile = /dev/stdout

And the output doesn't help much. I've found some solutions but are always related to old ubuntu.

Here is the output:

[ODBC][62430][1664301799.879691][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x55b6b35d3870
[ODBC][62430][1664301799.879839][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x55b6b35d3870
        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'

[ODBC][62430][1664301799.880405][SQLAllocHandle.c][513]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x55b6b35d4590
[ODBC][62430][1664301799.880465][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x55b6b35d4590
            Window Hdl = (nil)
            Str In = [Driver={ODBC Driver 18 for SQL Server};Server=tcp:xxxx,1433;Database=xxx;UID=xxx;PWD=**********;][length = 116 (SQL_NTS)]
            Str Out = 0x7fffe858da30
            Str Out Max = 2048
            Str Out Ptr = (nil)
            Completion = 0
[ODBC][62430][1664301799.903128][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x55b6b370ad10
[ODBC][62430][1664301799.903245][SQLGetEnvAttr.c][157]
        Entry:
            Environment = 0x55b6b370ad10
            Attribute = 65002
            Value = 0x7fffe8586090
            Buffer Len = 128
            StrLen = 0x7fffe858602c
[ODBC][62430][1664301799.903298][SQLGetEnvAttr.c][273]
        Exit:[SQL_SUCCESS]
[ODBC][62430][1664301799.903319][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x55b6b370ad10
        DIAG [08001] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746

        DIAG [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

[ODBC][62430][1664301799.903893][SQLDriverConnect.c][1637]
        Exit:[SQL_ERROR]
[ODBC][62430][1664301799.903976][SQLError.c][424]
        Entry:
            Connection = 0x55b6b35d4590
            SQLState = 0x7fffe858f046
            Native = 0x7fffe858f040
            Message Text = 0x7fffe858f050
            Buffer Length = 500
            Text Len Ptr = 0x7fffe858f03e
 [08001][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2746
[08001][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

Also, I've installed the OpenSSL 1.0.1k manually compiling the code, but looking the strace from the isql or sqlcmd it stills using the openssl 3 has you can see here:

openat(AT_FDCWD, "/lib/x86_64-linux-gnu/libcrypto.so.3", O_RDONLY|O_CLOEXEC) = 4
openat(AT_FDCWD, "/lib/x86_64-linux-gnu/libssl.so.3", O_RDONLY|O_CLOEXEC) = 4

Do you know how to change the libssl from the odbc drive?

BigLeo
  • 6,906
  • 2
  • 13
  • 12
  • `TCP Provider: Error code 0x2746` suggests that the target server does not support TLS 1.2. Are you **absolutely certain** that the target server is SQL Server 2016? SQL Server versions 2008 through 2014 could get TLS 1.2 support via [service packs and hot fixes](https://support.microsoft.com/en-us/topic/e4472ef8-90a9-13c1-e4d8-44aad198cdbe), but SQL Server 2016 and later have TLS 1.2 support out of the box. Unless, of course, TLS 1.2 has been disabled on the host operating system (which is possible via the SCHANNEL registry settings). – AlwaysLearning Sep 27 '22 at 21:49
  • @AlwaysLearning thanks for your reply, yes the SQL server is 2016 but the windows server is 2012. I've added to the SCHANNEL the registry setting, before was only SSL 2.0, now I've added the TLS 1.2 but I still got the same issue – BigLeo Sep 29 '22 at 14:52
  • 1
    I have the same issue trying to connect to an old SQL Server 2014. Did you solve this problem? – Björn Dec 01 '22 at 15:27
  • There is a long thread [here on SO](https://stackoverflow.com/questions/57265913/) which proposes several solutions, one of them hacking a few config values in file `/etc/openssl/openssl.cnf` . Even if you do this, your PHP or other interpreters might stll have been compiled to load an openssl 3 lib. – knb Jan 10 '23 at 09:26
  • @Björn I have fixed by upgrading the windows server 2012, the TLS on our windows server 2012 was too old and the Linux OpenSSL doesn't connect to it – BigLeo Jan 11 '23 at 11:00
  • @Leonardo Cuquejo Thank you! The project has been a long one so I will investigate this next time I'm working on it. – Björn Jan 12 '23 at 08:45
  • I have the same issue. I don't have have control over the Windows Server and the solutions pointed in stack overflows are for open ssl 1.1 and I am using openssl 3.0. – Jorge Moraleda Jun 16 '23 at 22:06

0 Answers0