Mac M1 ODBC Driver 18 for SQL Server

[Nuckeln]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.11
• pyodbc: 4035
• OS: Ventura 13.1
• DB:database.windows.net
• driver: ODBC Driver 18 for SQL Server

Issue

Often it is easiest to describe your issue as "expected behavior" and "observed behavior".

File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 598, in connect return self.dbapi.connect(*cargs, **cparams) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/14/e3q8)

[gordthompson]

Are you able to establish a connection using plain pyodbc? For example, something like

import pyodbc

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 18 for SQL Server;"
    "Server=whatever.database.windows.net;"
    # and so on …
)
print("Connected.")

[Nuckeln]

Hi gord,

this is my con script.

from dataclasses import dataclass
from typing import Dict, Any, Iterable
from pandas import DataFrame
from sqlalchemy import create_engine, inspect
import urllib
import pandas as pd
import pyodbc

@dataclass(frozen=True)
class ConnectionSettings:
    """Connection Settings."""
    server: str
    database: str
    username: str
    password: str
    #driver: str = '{ODBC Driver 18 for SQL Server}'
    #driver: str = '{ODBC Driver 13 for SQL Server}'
    driver: str = '{ODBC Driver 18 for SQL Server}'
    timeout: int = 30
class AzureDbConnection:
    """
    Azure SQL database connection.
    """
    def __init__(self, conn_settings: ConnectionSettings, echo: bool = False) -> None:
        conn_params = urllib.parse.quote_plus(
            'Driver=%s;' % conn_settings.driver +
            'Server=tcp:%s.database.windows.net,1433;' % conn_settings.server +
            'Database=%s;' % conn_settings.database +
            'Uid=%s;' % conn_settings.username +
            'Pwd=%s;' % conn_settings.password +
            'Encrypt=yes;' +
            'TrustServerCertificate=no;' +
            'Connection Timeout=%s;' % conn_settings.timeout
        )
        conn_string = f'mssql+pyodbc:///?odbc_connect={conn_params}'
        self.db = create_engine(conn_string, echo=echo)
    def connect(self) -> None:
        """Estimate connection."""
        self.conn = self.db.connect()
    def get_tables(self) -> Iterable[str]:
        """Get list of tables."""
        inspector = inspect(self.db)
        return [t for t in inspector.get_table_names()]
    def dispose(self) -> None:
        """Dispose opened connections."""
        self.conn.close()
        self.db.dispose()
    def execute(self, query: str) -> None:
        """Execute query."""
        self.conn.execute(query)

Works well on my other desktop

[gordthompson]

The whole point of my question was to isolate the pyodbc part of the issue and test that.

[Nuckeln]

Sry, I'm a little slow today :)

Same Issue XXXX/import pyodbc.py" Traceback (most recent call last): File "/Users/XXXX/import pyodbc.py", line 3, in <module> cnxn = pyodbc.connect( ^^^^^^^^^^^^^^^ pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)')

[gordthompson]

Okay good. Now does that same pyodbc-only test succeed on your other desktop? (I assume that it will.) If so, how do the two machines differ? Is it possible that the two machines might have different external IP addresses?

[Nuckeln]

Yes without problems, the script runs on my M1 Air, as well as on our web server as an app (Azure).

Only on my new M1 Pro not. Homebrew I have cloned directly the drivers should be correct I think.

It is not the IP. I can connect via Azure Data Studio to the server on my M1 Pro.

[v-johoang]

I wonder what the difference between the computers are? What makes the M1 Pro different from the other 2 computers in terms of setup?

[mkleehammer]

Closing due to inactivity. Reopen if necessary.