Client unable to establish connection, macOS, django, odbc 17, pyodbc

Question

Everything was working until I restarted my laptop.

I have this setup:

python 3.8.9
django 3.1.1
pyodbc 4.0.30
pyodbc.drivers() shows this: ['ODBC Driver 17 for SQL Server']
SQLServer
openssl version shows this: OpenSSL 1.1.1l 24 Aug 2021
isql -v -k "DRIVER=ODBC Driver 17 for SQL Server;SERVER=<my server>,<my port>;UID=<my username>;PWD=<my password>" connects to the database with no issues
servername, port, user, and password seems correct also because the jdbc driver works without any issues with them.
cat /etc/odbcinst.ini and cat /etc/odbc.ini - both return this:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib
UsageCount=10

odbcinst -j returns this:

unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/sgalich/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

in the django settings:

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'NAME': os.environ.get('DB_NAME'),
        'USER': os.environ.get('DB_USER'),
        'PASSWORD': os.environ.get('DB_PASSWORD'),
        'HOST': os.environ.get('DB_HOST'),
        'PORT': os.environ.get('DB_PORT'),
        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    },
}

cat ~/.bash_profile shows this:

export PATH="/usr/local/opt/openssl@1.1/bin:$PATH"
export CPATH="/usr/local/opt/openssl@1.1/include"
export LDFLAGS="-L/usr/local/opt/openssl@1.1/lib"
export CPPFLAGS="-I/usr/local/opt/openssl@1.1/include"
export LIBRARY_PATH="/usr/local/opt/openssl@1.1/lib"
export DYLD_LIBRARY_PATH="/usr/local/opt/openssl@1.1/lib"
export DYLD_FALLBACK_LIBRARY_PATH="/usr/local/opt/openssl@1.1/lib"

I reinstalled the ODBC Driver with this official Microsoft instruction. But it didn't help. I'm still failing to start the django server and facing this error:

Exception in thread django-main-thread:
Traceback (most recent call last):
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
    self.connect()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 200, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/mssql/base.py", line 326, in get_new_connection
    conn = Database.connect(connstr,
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/threading.py", line 932, in _bootstrap_inner
    self.run()
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.8/lib/python3.8/threading.py", line 870, in run
    self._target(*self._args, **self._kwargs)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/utils/autoreload.py", line 64, in wrapper
    fn(*args, **kwargs)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/core/management/commands/runserver.py", line 121, in inner_run
    self.check_migrations()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/core/management/base.py", line 486, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/migrations/executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/migrations/loader.py", line 53, in __init__
    self.build_graph()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/migrations/loader.py", line 220, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/migrations/recorder.py", line 77, in applied_migrations
    if self.has_table():
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/migrations/recorder.py", line 55, in has_table
    with self.connection.cursor() as cursor:
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 259, in cursor
    return self._cursor()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/mssql/base.py", line 230, in _cursor
    conn = super()._cursor()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 235, in _cursor
    self.ensure_connection()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
    self.connect()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
    self.connect()
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/django/db/backends/base/base.py", line 200, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/Users/sgalich/Library/Python/3.8/lib/python/site-packages/mssql/base.py", line 326, in get_new_connection
    conn = Database.connect(connstr,
django.db.utils.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

odbc log shows this:

[ODBC][33439][1634323619.168260][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x7fb18c951200
[ODBC][33439][1634323619.168500][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x7fb18c951200
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 4
[ODBC][33439][1634323619.168927][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][33439][1634323619.169074][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fb18c951200
        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'

[ODBC][33439][1634323619.169343][SQLAllocHandle.c][513]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7fb19d4b2000
[ODBC][33439][1634323619.170140][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x7fb19d4b2000
            Window Hdl = 0x0
            Str In = [DRIVER=ODBC Driver 17 for SQL Server;SERVER=<my server>,<my password>;UID=<my username>;PWD=********************;DATABASE=growth;unico...][length = 144 (SQL_NTS)]
            Str Out = 0x0
            Str Out Max = 0
            Str Out Ptr = 0x0
            Completion = 0
[ODBC][33439][1634323619.291639][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x7fb19d4c0a00
[ODBC][33439][1634323619.291819][SQLGetEnvAttr.c][157]
        Entry:
            Environment = 0x7fb19d4c0a00
            Attribute = 65002
            Value = 0x70000ee057a0
            Buffer Len = 128
            StrLen = 0x70000ee05784
[ODBC][33439][1634323619.292027][SQLGetEnvAttr.c][273]
        Exit:[SQL_SUCCESS]
[ODBC][33439][1634323619.292192][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x7fb19d4c0a00
[ODBC][33439][1634323619.292399][SQLDriverConnectW.c][699]
        Exit:[SQL_ERROR]
[ODBC][33439][1634323619.292558][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x7fb19d4b2000
            Window Hdl = 0x0
            Str In = [DRIVER=ODBC Driver 17 for SQL Server;SERVER=<my server>,<my password>;UID=<my username>;PWD=********************;DATABASE=growth;unico...][length = 144 (SQL_NTS)]
            Str Out = 0x70000ee06160
            Str Out Max = 2048
            Str Out Ptr = 0x0
            Completion = 0
        DIAG [08001] [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection

        DIAG [01S00] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

[ODBC][33439][1634323619.346187][SQLDriverConnect.c][1637]
        Exit:[SQL_ERROR]
[ODBC][33439][1634323619.346372][SQLGetDiagRecW.c][535]
        Entry:
            Connection = 0x7fb19d4b2000
            Rec Number = 1
            SQLState = 0x70000ee08504
            Native = 0x70000ee084ec
            Message Text = 0x7fb19dad5e00
            Buffer Length = 1023
            Text Len Ptr = 0x70000ee08502
[ODBC][33439][1634323619.346517][SQLGetDiagRecW.c][596]
        Exit:[SQL_SUCCESS]
            SQLState = [08001]
            Native = 0x70000ee084ec -> 0 (32 bits)
            Message Text = [[Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection]
[ODBC][33439][1634323619.346654][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fb19d4b2000
[ODBC][33439][1634323619.346751][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]

All answers I've managed to find didn't help, including this answer: ODBC can't find correct OpenSSL version after upgrading OpenSSL

That's pretty weird that everything was working for a long time until I restarted my laptop (no OS update, just restarting). May be this issue related to exporting some variables with path? Please help me solve this problem.

I am also getting the same error, did you find any solution? Thanks! — DPs, Oct 23 '21 at 14:59
@DPs in `/usr/local/bin` I copied all files inside folder `openssl@1.1` into `openssl` folder and it started to work. Not sure if it is an acceptable solution though. — sgalich, Oct 23 '21 at 17:24
thank you for the comment, I tried this but I do not have openssl@1.1 folder in my /usr/local/bin folder, Rajat's solution worked for me. Thank you — DPs, Oct 24 '21 at 02:52
related: https://github.com/microsoft/homebrew-mssql-release/issues/59 — Gord Thompson, Oct 24 '21 at 14:16

score 10 · Answer 1 · edited Oct 28 '21 at 12:58

10

I am not sure if you were able to find this. But the following solution worked for me. My odbc was not able to find the ssl version 1. I did this: In my /usr/local/opt/

rm openssl

or

rm openssl@3

The idea is to remove the link to any openssl it is pointing to. You may check with your finder as well.

Then create a new link pointing to openssl1.1

ln -s /usr/local/Cellar/openssl@1.1/1.1.1l_1 /usr/local/opt/openssl

edited Oct 28 '21 at 12:58

Nimantha

6,405
6
28
69

answered Oct 23 '21 at 18:13

rajat yadav

374
2
7

1

Rajat thank you for the answer. I had done this before I posted this question here and it didn't help me. But what did help me is this: I copied all files inside folder `openssl@1.1` into `openssl` folder. – sgalich Oct 23 '21 at 19:34

score 1 · Answer 2 · answered Oct 25 '21 at 11:48

I was having the exact same error with similar setup. Since I am running on an M1 macbook, I found that my problem was on Python installation on Intel x86-64 arch, but msodbcsql/unixodbc drivers running in Arm64 arch.

See if that is your problem by running the following commands:

% file $(which odbcinst)
% file $(which isql)  
% file $(which python3)

This should either output:

Mach-O 64-bit executable x86_64, or
Mach-O 64-bit executable arm64

Everything should return the same output. If one is different, that is your problem.

If that is your case, identify in what arch you should be, and reinstall the components in the correct arch.

In my case, I had to use Rosetta Terminal to install brew in the Intel arch (/usr/local/bin/brew) and install all components with it.

Now my pipenv is running python in Intel and using Intel odbc drivers. Installation folders:

% file $(which python3)
/usr/local/bin/python3: Mach-O 64-bit executable x86_64

% file $(which isql)    
/usr/local/bin/isql: Mach-O 64-bit executable x86_64

% file $(which odbcinst) 
/usr/local/bin/odbcinst: Mach-O 64-bit executable x86_64

Client unable to establish connection, macOS, django, odbc 17, pyodbc

2 Answers2