don't include PWD in pyodbc connection string if URL does not have a password

[MarcSkovMadsen]

I am trying to use sqlalchemy to create an engine for Microsoft Azure Synapse SQL datawarehouse using pyodbc.

I have developed the code below.

"""Functionality to work with the DataWareHouse from Python"""
# See https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver15
import queue
import threading
from typing import Any
import pyodbc
import pandas as pd
from sqlalchemy import create_engine
import db_config
import pandas as pd

DRIVER = "ODBC+Driver+17+for+SQL+Server"
from urllib.parse import quote_plus

class Datawarehouse:
    """The Datawarehouse provides functionality to work with the ODE Datawarehouse"""

    def __init__(  # pylint: disable=too-many-arguments
        self, server: str, database: str, username=None, password=None, authentication=None
    ):
        """The Datawarehouse instance provides functionality to work ODE Datawarehouse

        Args:
            server (str): [description]
            database (str): [description]
            username ([type], optional): [description]. Defaults to None.
            password ([type], optional): [description]. Defaults to None.
            authentication ([type], optional): [description]. For example SqlPassword, ActiveDirectoryPassword, ActiveDirectoryIntegrated, ActiveDirectoryInteractive. Defaults to None.
        """
        self.server = server
        self.database = database
        self.username = username
        self.password = password
        self.authentication = authentication

    def get_connection_string(self) -> str:
        """Returns a pyodbc connectionstring based on the current configuration

        Returns:
            str: [description]
        """
        server = self.server
        database = self.database
        username = quote_plus(self.username)
        password = quote_plus(self.password)

        if self.password and not self.authentication:
            return f"mssql+pyodbc://{username}:{password}@{server}:1433/{database}?driver={DRIVER}&autocommit=true"
        if self.authentication=="ActiveDirectoryPassword":
            return f"mssql+pyodbc://{username}:{password}@{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryPassword"
        if self.authentication=="ActiveDirectoryIntegrated":
            return f"mssql+pyodbc://{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryIntegrated"
        if self.authentication=="ActiveDirectoryInteractive":
            return f"mssql+pyodbc://{username}@{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryInteractive"

        return f"mssql+pyodbc://{server}:1433/{database}?driver={DRIVER}&autocommit=true"

    def get_engine(self, echo: bool = False) -> Any:
        """Returns a SQL Alchemy Engine based on the current configuration

        Returns:
            [Any]: [description]
        """
        connectionstring = self.get_connection_string()
        return create_engine(connectionstring, fast_executemany=True, echo=echo).execution_options(
            autocommit=True
        )

    def to_sql(self, data: pd.DataFrame, table: str, schema: str, if_exists="append"):
        """Saves the given DataFrame to the specified {schema}.{table}

        Args:
            data (pd.DataFrame): A Pandas DataFrame
            table ([type]): The name of the table. For example 'forecast_hourly'
            schema ([type]): The name of the schema. For example 'noaa'.
            if_exists (str, optional): What to do if the table exists: Replace or Append.
                Defaults to "append".
        """
        engine = self.get_engine()
        with engine.connect() as con:
            if if_exists == "replace":
                con.execute(f"TRUNCATE TABLE {schema}.{table}")
            data.to_sql(
                table, schema=schema, con=con, if_exists="append", index=False, chunksize=100
            )

    def execute(self, sql: str, *args):
        """Executes the specified sql string on the Datawarehouse

        Args:
            sql (str): A select string
        """
        engine = self.get_engine()
        with engine.connect() as con:
            return con.execute(sql, *args)

def test_can_connect_using_activedirectory_interactive_official():
    # from https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

    server = 'tcp:' + db_config.server
    database = db_config.database
    username = db_config.username
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+";Authentication=ActiveDirectoryInteractive")
    # cursor = cnxn.cursor()
    query = "select top 2 * from eia.data_view"
    # Then
    data = pd.read_sql(sql=query, con=cnxn)
    assert len(data)==2

def test_can_connect_using_activedirectory_integrated_official():
    """Can log in directly using AD"""
    # from https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-ver15

    server = 'tcp:' + db_config.server
    database = db_config.database
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+";Authentication=ActiveDirectoryIntegrated")
    # cursor = cnxn.cursor()
    query = "select top 2 * from eia.data_view"
    # Then
    data = pd.read_sql(sql=query, con=cnxn)
    assert len(data)==2

def test_can_connect_using_activedirectory_interactive_sqlalchemy():
    # When
    dwh = Datawarehouse(
        server = db_config.server,
        database= db_config.database,
        username = db_config.username,
        password = db_config.password,
        authentication="ActiveDirectoryInteractive"
    )

    engine = dwh.get_engine(echo=True)
    query = "select top 2 * from eia.data_view"
    # Then
    data = pd.read_sql(sql=query, con=engine)
    assert len(data)==2

def test_can_connect_using_activedirectory_integrated_sqlalchemy():
    # When
    dwh = Datawarehouse(
        server = db_config.server,
        database= db_config.database,
        username = db_config.username,
        password = db_config.password,
        authentication="ActiveDirectoryIntegrated"
    )

    engine = dwh.get_engine(echo=True)
    query = "select top 2 * from eia.data_view"
    # Then
    data = pd.read_sql(sql=query, con=engine)
    assert len(data)==2

It uses a db_config.py file that looks like

server = INSERT
database= INSERT
username = INSERT
password = INSERT

If I run pytest on it via pytest -s -vv test_connection.py I get something like

============================= test session starts =============================
platform win32 -- Python 3.7.6, pytest-6.0.2, py-1.9.0, pluggy-0.13.1 -- C:\<LONGPATH>\.venv\scripts\python.exe
cachedir: .pytest_cache
rootdir: C:\repos\trading_analytics\us-trading, configfile: pytest.ini
collecting ... collected 4 items

users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_interactive_official PASSED
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_integrated_official PASSED
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_interactive_sqlalchemy FAILED
users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_integrated_sqlalchemy FAILED

================================== FAILURES ===================================
________ test_can_connect_using_activedirectory_interactive_sqlalchemy ________

self = Engine(mssql+pyodbc://<USERNAME>%40<DOMAIN>@<SERVERNAME>.database.windows.net:1433/<DATABASE>?Authentication=ActiveDirectoryInteractive&autocommit=true&driver=ODBC+Driver+17+for+SQL+Server)
fn = <bound method Pool.connect of <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>>
connection = None

    def _wrap_pool_connect(self, fn, connection):
        dialect = self.dialect
        try:
>           return fn()

.venv\lib\site-packages\sqlalchemy\engine\base.py:2338:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>

    def connect(self):
        """Return a DBAPI connection from the pool.

        The connection is instrumented such that when its
        ``close()`` method is called, the connection will be returned to
        the pool.

        """
        if not self._use_threadlocal:
>           return _ConnectionFairy._checkout(self)

.venv\lib\site-packages\sqlalchemy\pool\base.py:364:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

cls = <class 'sqlalchemy.pool.base._ConnectionFairy'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
threadconns = None, fairy = None

    @classmethod
    def _checkout(cls, pool, threadconns=None, fairy=None):
        if not fairy:
>           fairy = _ConnectionRecord.checkout(pool)

.venv\lib\site-packages\sqlalchemy\pool\base.py:778:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

cls = <class 'sqlalchemy.pool.base._ConnectionRecord'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>

    @classmethod
    def checkout(cls, pool):
>       rec = pool._do_get()

.venv\lib\site-packages\sqlalchemy\pool\base.py:495:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>

    def _do_get(self):
        use_overflow = self._max_overflow > -1

        try:
            wait = use_overflow and self._overflow >= self._max_overflow
            return self._pool.get(wait, self._timeout)
        except sqla_queue.Empty:
            # don't do things inside of "except Empty", because when we say
            # we timed out or can't connect and raise, Python 3 tells
            # people the real error is queue.Empty which it isn't.
            pass
        if use_overflow and self._overflow >= self._max_overflow:
            if not wait:
                return self._do_get()
            else:
                raise exc.TimeoutError(
                    "QueuePool limit of size %d overflow %d reached, "
                    "connection timed out, timeout %d"
                    % (self.size(), self.overflow(), self._timeout),
                    code="3o7r",
                )

        if self._inc_overflow():
            try:
                return self._create_connection()
            except:
                with util.safe_reraise():
>                   self._dec_overflow()

.venv\lib\site-packages\sqlalchemy\pool\impl.py:140:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF57ADB88>
type_ = None, value = None, traceback = None

    def __exit__(self, type_, value, traceback):
        # see #2703 for notes
        if type_ is None:
            exc_type, exc_value, exc_tb = self._exc_info
            self._exc_info = None  # remove potential circular references
            if not self.warn_only:
                compat.raise_(
>                   exc_value, with_traceback=exc_tb,
                )

.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

    def raise_(
        exception, with_traceback=None, replace_context=None, from_=False
    ):
        r"""implement "raise" with cause support.

        :param exception: exception to raise
        :param with_traceback: will call exception.with_traceback()
        :param replace_context: an as-yet-unsupported feature.  This is
         an exception object which we are "replacing", e.g., it's our
         "cause" but we don't want it printed.    Basically just what
         ``__suppress_context__`` does but we don't want to suppress
         the enclosing context, if any.  So for now we make it the
         cause.
        :param from\_: the cause.  this actually sets the cause and doesn't
         hope to hide it someday.

        """
        if with_traceback is not None:
            exception = exception.with_traceback(with_traceback)

        if from_ is not False:
            exception.__cause__ = from_
        elif replace_context is not None:
            # no good solution here, we would like to have the exception
            # have only the context of replace_context.__context__ so that the
            # intermediary exception does not change, but we can't figure
            # that out.
            exception.__cause__ = replace_context

        try:
>           raise exception

.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>

    def _do_get(self):
        use_overflow = self._max_overflow > -1

        try:
            wait = use_overflow and self._overflow >= self._max_overflow
            return self._pool.get(wait, self._timeout)
        except sqla_queue.Empty:
            # don't do things inside of "except Empty", because when we say
            # we timed out or can't connect and raise, Python 3 tells
            # people the real error is queue.Empty which it isn't.
            pass
        if use_overflow and self._overflow >= self._max_overflow:
            if not wait:
                return self._do_get()
            else:
                raise exc.TimeoutError(
                    "QueuePool limit of size %d overflow %d reached, "
                    "connection timed out, timeout %d"
                    % (self.size(), self.overflow(), self._timeout),
                    code="3o7r",
                )

        if self._inc_overflow():
            try:
>               return self._create_connection()

.venv\lib\site-packages\sqlalchemy\pool\impl.py:137:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>

    def _create_connection(self):
        """Called by subclasses to create a new ConnectionRecord."""

>       return _ConnectionRecord(self)

.venv\lib\site-packages\sqlalchemy\pool\base.py:309:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF57763C8>
connect = True

    def __init__(self, pool, connect=True):
        self.__pool = pool
        if connect:
>           self.__connect(first_connect_check=True)

.venv\lib\site-packages\sqlalchemy\pool\base.py:440:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
first_connect_check = True

    def __connect(self, first_connect_check=False):
        pool = self.__pool

        # ensure any existing connection is removed, so that if
        # creator fails, this attribute stays None
        self.connection = None
        try:
            self.starttime = time.time()
            connection = pool._invoke_creator(self)
            pool.logger.debug("Created new connection %r", connection)
            self.connection = connection
        except Exception as e:
            with util.safe_reraise():
>               pool.logger.debug("Error on connect(): %s", e)

.venv\lib\site-packages\sqlalchemy\pool\base.py:661:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF57ADEC8>
type_ = None, value = None, traceback = None

    def __exit__(self, type_, value, traceback):
        # see #2703 for notes
        if type_ is None:
            exc_type, exc_value, exc_tb = self._exc_info
            self._exc_info = None  # remove potential circular references
            if not self.warn_only:
                compat.raise_(
>                   exc_value, with_traceback=exc_tb,
                )

.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

    def raise_(
        exception, with_traceback=None, replace_context=None, from_=False
    ):
        r"""implement "raise" with cause support.

        :param exception: exception to raise
        :param with_traceback: will call exception.with_traceback()
        :param replace_context: an as-yet-unsupported feature.  This is
         an exception object which we are "replacing", e.g., it's our
         "cause" but we don't want it printed.    Basically just what
         ``__suppress_context__`` does but we don't want to suppress
         the enclosing context, if any.  So for now we make it the
         cause.
        :param from\_: the cause.  this actually sets the cause and doesn't
         hope to hide it someday.

        """
        if with_traceback is not None:
            exception = exception.with_traceback(with_traceback)

        if from_ is not False:
            exception.__cause__ = from_
        elif replace_context is not None:
            # no good solution here, we would like to have the exception
            # have only the context of replace_context.__context__ so that the
            # intermediary exception does not change, but we can't figure
            # that out.
            exception.__cause__ = replace_context

        try:
>           raise exception

.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>
first_connect_check = True

    def __connect(self, first_connect_check=False):
        pool = self.__pool

        # ensure any existing connection is removed, so that if
        # creator fails, this attribute stays None
        self.connection = None
        try:
            self.starttime = time.time()
>           connection = pool._invoke_creator(self)

.venv\lib\site-packages\sqlalchemy\pool\base.py:656:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

connection_record = <sqlalchemy.pool.base._ConnectionRecord object at 0x00000194F4983648>

    def connect(connection_record=None):
        if dialect._has_events:
            for fn in dialect.dispatch.do_connect:
                connection = fn(
                    dialect, connection_record, cargs, cparams
                )
                if connection is not None:
                    return connection
>       return dialect.connect(*cargs, **cparams)

.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x00000194F4B491C8>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;UID=<USERNAME>@<DOMAIN>;PWD=;Authentication=ActiveDirectoryInteractive',)
cparams = {'autocommit': True}

    def connect(self, *cargs, **cparams):
        # inherits the docstring from interfaces.Dialect.connect
>       return self.dbapi.connect(*cargs, **cparams)
E       pyodbc.Error: ('FA003', "[FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0) (SQLDriverConnect); [FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0)")

.venv\lib\site-packages\sqlalchemy\engine\default.py:493: Error

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

    def test_can_connect_using_activedirectory_interactive_sqlalchemy():
        # When
        dwh = Datawarehouse(
            server = db_config.server,
            database= db_config.database,
            username = db_config.username,
            password = db_config.password,
            authentication="ActiveDirectoryInteractive"
        )

        engine = dwh.get_engine(echo=True)
        query = "select top 2 * from eia.data_view"
        # Then
>       data = pd.read_sql(sql=query, con=engine)

users\<USERNAME>\<FILENAME>.py:136:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv\lib\site-packages\pandas\io\sql.py:515: in read_sql
    chunksize=chunksize,
.venv\lib\site-packages\pandas\io\sql.py:1295: in read_query
    result = self.execute(*args)
.venv\lib\site-packages\pandas\io\sql.py:1162: in execute
    *args, **kwargs
.venv\lib\site-packages\sqlalchemy\engine\base.py:2236: in execute
    connection = self._contextual_connect(close_with_result=True)
.venv\lib\site-packages\sqlalchemy\engine\base.py:2304: in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
.venv\lib\site-packages\sqlalchemy\engine\base.py:2342: in _wrap_pool_connect
    e, dialect, self
.venv\lib\site-packages\sqlalchemy\engine\base.py:1584: in _handle_dbapi_exception_noconnection
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
    raise exception
.venv\lib\site-packages\sqlalchemy\engine\base.py:2338: in _wrap_pool_connect
    return fn()
.venv\lib\site-packages\sqlalchemy\pool\base.py:364: in connect
    return _ConnectionFairy._checkout(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:778: in _checkout
    fairy = _ConnectionRecord.checkout(pool)
.venv\lib\site-packages\sqlalchemy\pool\base.py:495: in checkout
    rec = pool._do_get()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:140: in _do_get
    self._dec_overflow()
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
    exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
    raise exception
.venv\lib\site-packages\sqlalchemy\pool\impl.py:137: in _do_get
    return self._create_connection()
.venv\lib\site-packages\sqlalchemy\pool\base.py:309: in _create_connection
    return _ConnectionRecord(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:440: in __init__
    self.__connect(first_connect_check=True)
.venv\lib\site-packages\sqlalchemy\pool\base.py:661: in __connect
    pool.logger.debug("Error on connect(): %s", e)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
    exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
    raise exception
.venv\lib\site-packages\sqlalchemy\pool\base.py:656: in __connect
    connection = pool._invoke_creator(self)
.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114: in connect
    return dialect.connect(*cargs, **cparams)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x00000194F4B491C8>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;UID=<USERNAME>@<DOMAIN>;PWD=;Authentication=ActiveDirectoryInteractive',)
cparams = {'autocommit': True}

    def connect(self, *cargs, **cparams):
        # inherits the docstring from interfaces.Dialect.connect
>       return self.dbapi.connect(*cargs, **cparams)
E       sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('FA003', "[FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0) (SQLDriverConnect); [FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0)")
E       (Background on this error at: http://sqlalche.me/e/13/dbapi)

.venv\lib\site-packages\sqlalchemy\engine\default.py:493: DBAPIError
________ test_can_connect_using_activedirectory_integrated_sqlalchemy _________

self = Engine(mssql+pyodbc://<SERVERNAME>.database.windows.net:1433/<DATABASE>?Authentication=ActiveDirectoryIntegrated&autocommit=true&driver=ODBC+Driver+17+for+SQL+Server)
fn = <bound method Pool.connect of <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>>
connection = None

    def _wrap_pool_connect(self, fn, connection):
        dialect = self.dialect
        try:
>           return fn()

.venv\lib\site-packages\sqlalchemy\engine\base.py:2338:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>

    def connect(self):
        """Return a DBAPI connection from the pool.

        The connection is instrumented such that when its
        ``close()`` method is called, the connection will be returned to
        the pool.

        """
        if not self._use_threadlocal:
>           return _ConnectionFairy._checkout(self)

.venv\lib\site-packages\sqlalchemy\pool\base.py:364:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

cls = <class 'sqlalchemy.pool.base._ConnectionFairy'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
threadconns = None, fairy = None

    @classmethod
    def _checkout(cls, pool, threadconns=None, fairy=None):
        if not fairy:
>           fairy = _ConnectionRecord.checkout(pool)

.venv\lib\site-packages\sqlalchemy\pool\base.py:778:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

cls = <class 'sqlalchemy.pool.base._ConnectionRecord'>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>

    @classmethod
    def checkout(cls, pool):
>       rec = pool._do_get()

.venv\lib\site-packages\sqlalchemy\pool\base.py:495:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>

    def _do_get(self):
        use_overflow = self._max_overflow > -1

        try:
            wait = use_overflow and self._overflow >= self._max_overflow
            return self._pool.get(wait, self._timeout)
        except sqla_queue.Empty:
            # don't do things inside of "except Empty", because when we say
            # we timed out or can't connect and raise, Python 3 tells
            # people the real error is queue.Empty which it isn't.
            pass
        if use_overflow and self._overflow >= self._max_overflow:
            if not wait:
                return self._do_get()
            else:
                raise exc.TimeoutError(
                    "QueuePool limit of size %d overflow %d reached, "
                    "connection timed out, timeout %d"
                    % (self.size(), self.overflow(), self._timeout),
                    code="3o7r",
                )

        if self._inc_overflow():
            try:
                return self._create_connection()
            except:
                with util.safe_reraise():
>                   self._dec_overflow()

.venv\lib\site-packages\sqlalchemy\pool\impl.py:140:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF5DB2488>
type_ = None, value = None, traceback = None

    def __exit__(self, type_, value, traceback):
        # see #2703 for notes
        if type_ is None:
            exc_type, exc_value, exc_tb = self._exc_info
            self._exc_info = None  # remove potential circular references
            if not self.warn_only:
                compat.raise_(
>                   exc_value, with_traceback=exc_tb,
                )

.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

    def raise_(
        exception, with_traceback=None, replace_context=None, from_=False
    ):
        r"""implement "raise" with cause support.

        :param exception: exception to raise
        :param with_traceback: will call exception.with_traceback()
        :param replace_context: an as-yet-unsupported feature.  This is
         an exception object which we are "replacing", e.g., it's our
         "cause" but we don't want it printed.    Basically just what
         ``__suppress_context__`` does but we don't want to suppress
         the enclosing context, if any.  So for now we make it the
         cause.
        :param from\_: the cause.  this actually sets the cause and doesn't
         hope to hide it someday.

        """
        if with_traceback is not None:
            exception = exception.with_traceback(with_traceback)

        if from_ is not False:
            exception.__cause__ = from_
        elif replace_context is not None:
            # no good solution here, we would like to have the exception
            # have only the context of replace_context.__context__ so that the
            # intermediary exception does not change, but we can't figure
            # that out.
            exception.__cause__ = replace_context

        try:
>           raise exception

.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>

    def _do_get(self):
        use_overflow = self._max_overflow > -1

        try:
            wait = use_overflow and self._overflow >= self._max_overflow
            return self._pool.get(wait, self._timeout)
        except sqla_queue.Empty:
            # don't do things inside of "except Empty", because when we say
            # we timed out or can't connect and raise, Python 3 tells
            # people the real error is queue.Empty which it isn't.
            pass
        if use_overflow and self._overflow >= self._max_overflow:
            if not wait:
                return self._do_get()
            else:
                raise exc.TimeoutError(
                    "QueuePool limit of size %d overflow %d reached, "
                    "connection timed out, timeout %d"
                    % (self.size(), self.overflow(), self._timeout),
                    code="3o7r",
                )

        if self._inc_overflow():
            try:
>               return self._create_connection()

.venv\lib\site-packages\sqlalchemy\pool\impl.py:137:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>

    def _create_connection(self):
        """Called by subclasses to create a new ConnectionRecord."""

>       return _ConnectionRecord(self)

.venv\lib\site-packages\sqlalchemy\pool\base.py:309:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
pool = <sqlalchemy.pool.impl.QueuePool object at 0x0000019CF656F208>
connect = True

    def __init__(self, pool, connect=True):
        self.__pool = pool
        if connect:
>           self.__connect(first_connect_check=True)

.venv\lib\site-packages\sqlalchemy\pool\base.py:440:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
first_connect_check = True

    def __connect(self, first_connect_check=False):
        pool = self.__pool

        # ensure any existing connection is removed, so that if
        # creator fails, this attribute stays None
        self.connection = None
        try:
            self.starttime = time.time()
            connection = pool._invoke_creator(self)
            pool.logger.debug("Created new connection %r", connection)
            self.connection = connection
        except Exception as e:
            with util.safe_reraise():
>               pool.logger.debug("Error on connect(): %s", e)

.venv\lib\site-packages\sqlalchemy\pool\base.py:661:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x0000019CF5DB2208>
type_ = None, value = None, traceback = None

    def __exit__(self, type_, value, traceback):
        # see #2703 for notes
        if type_ is None:
            exc_type, exc_value, exc_tb = self._exc_info
            self._exc_info = None  # remove potential circular references
            if not self.warn_only:
                compat.raise_(
>                   exc_value, with_traceback=exc_tb,
                )

.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

    def raise_(
        exception, with_traceback=None, replace_context=None, from_=False
    ):
        r"""implement "raise" with cause support.

        :param exception: exception to raise
        :param with_traceback: will call exception.with_traceback()
        :param replace_context: an as-yet-unsupported feature.  This is
         an exception object which we are "replacing", e.g., it's our
         "cause" but we don't want it printed.    Basically just what
         ``__suppress_context__`` does but we don't want to suppress
         the enclosing context, if any.  So for now we make it the
         cause.
        :param from\_: the cause.  this actually sets the cause and doesn't
         hope to hide it someday.

        """
        if with_traceback is not None:
            exception = exception.with_traceback(with_traceback)

        if from_ is not False:
            exception.__cause__ = from_
        elif replace_context is not None:
            # no good solution here, we would like to have the exception
            # have only the context of replace_context.__context__ so that the
            # intermediary exception does not change, but we can't figure
            # that out.
            exception.__cause__ = replace_context

        try:
>           raise exception

.venv\lib\site-packages\sqlalchemy\util\compat.py:182:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>
first_connect_check = True

    def __connect(self, first_connect_check=False):
        pool = self.__pool

        # ensure any existing connection is removed, so that if
        # creator fails, this attribute stays None
        self.connection = None
        try:
            self.starttime = time.time()
>           connection = pool._invoke_creator(self)

.venv\lib\site-packages\sqlalchemy\pool\base.py:656:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

connection_record = <sqlalchemy.pool.base._ConnectionRecord object at 0x0000019CF5DB20C8>

    def connect(connection_record=None):
        if dialect._has_events:
            for fn in dialect.dispatch.do_connect:
                connection = fn(
                    dialect, connection_record, cargs, cparams
                )
                if connection is not None:
                    return connection
>       return dialect.connect(*cargs, **cparams)

.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x0000019CF6042E48>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Trusted_Connection=Yes;Authentication=ActiveDirectoryIntegrated',)
cparams = {'autocommit': True}

    def connect(self, *cargs, **cparams):
        # inherits the docstring from interfaces.Dialect.connect
>       return self.dbapi.connect(*cargs, **cparams)
E       pyodbc.Error: ('FA001', '[FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0) (SQLDriverConnect); [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0)')

.venv\lib\site-packages\sqlalchemy\engine\default.py:493: Error

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

    def test_can_connect_using_activedirectory_integrated_sqlalchemy():
        # When
        dwh = Datawarehouse(
            server = db_config.server,
            database= db_config.database,
            username = db_config.username,
            password = db_config.password,
            authentication="ActiveDirectoryIntegrated"
        )

        engine = dwh.get_engine(echo=True)
        query = "select top 2 * from eia.data_view"
        # Then
>       data = pd.read_sql(sql=query, con=engine)

users\<USERNAME>\<FILENAME>.py:152:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv\lib\site-packages\pandas\io\sql.py:515: in read_sql
    chunksize=chunksize,
.venv\lib\site-packages\pandas\io\sql.py:1295: in read_query
    result = self.execute(*args)
.venv\lib\site-packages\pandas\io\sql.py:1162: in execute
    *args, **kwargs
.venv\lib\site-packages\sqlalchemy\engine\base.py:2236: in execute
    connection = self._contextual_connect(close_with_result=True)
.venv\lib\site-packages\sqlalchemy\engine\base.py:2304: in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
.venv\lib\site-packages\sqlalchemy\engine\base.py:2342: in _wrap_pool_connect
    e, dialect, self
.venv\lib\site-packages\sqlalchemy\engine\base.py:1584: in _handle_dbapi_exception_noconnection
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
    raise exception
.venv\lib\site-packages\sqlalchemy\engine\base.py:2338: in _wrap_pool_connect
    return fn()
.venv\lib\site-packages\sqlalchemy\pool\base.py:364: in connect
    return _ConnectionFairy._checkout(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:778: in _checkout
    fairy = _ConnectionRecord.checkout(pool)
.venv\lib\site-packages\sqlalchemy\pool\base.py:495: in checkout
    rec = pool._do_get()
.venv\lib\site-packages\sqlalchemy\pool\impl.py:140: in _do_get
    self._dec_overflow()
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
    exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
    raise exception
.venv\lib\site-packages\sqlalchemy\pool\impl.py:137: in _do_get
    return self._create_connection()
.venv\lib\site-packages\sqlalchemy\pool\base.py:309: in _create_connection
    return _ConnectionRecord(self)
.venv\lib\site-packages\sqlalchemy\pool\base.py:440: in __init__
    self.__connect(first_connect_check=True)
.venv\lib\site-packages\sqlalchemy\pool\base.py:661: in __connect
    pool.logger.debug("Error on connect(): %s", e)
.venv\lib\site-packages\sqlalchemy\util\langhelpers.py:69: in __exit__
    exc_value, with_traceback=exc_tb,
.venv\lib\site-packages\sqlalchemy\util\compat.py:182: in raise_
    raise exception
.venv\lib\site-packages\sqlalchemy\pool\base.py:656: in __connect
    connection = pool._invoke_creator(self)
.venv\lib\site-packages\sqlalchemy\engine\strategies.py:114: in connect
    return dialect.connect(*cargs, **cparams)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x0000019CF6042E48>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Trusted_Connection=Yes;Authentication=ActiveDirectoryIntegrated',)
cparams = {'autocommit': True}

    def connect(self, *cargs, **cparams):
        # inherits the docstring from interfaces.Dialect.connect
>       return self.dbapi.connect(*cargs, **cparams)
E       sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('FA001', '[FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0) (SQLDriverConnect); [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0)')
E       (Background on this error at: http://sqlalche.me/e/13/dbapi)

.venv\lib\site-packages\sqlalchemy\engine\default.py:493: DBAPIError
=========================== short test summary info ===========================
FAILED users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_interactive_sqlalchemy
FAILED users/<USERNAME>/<FILENAME>.py::test_can_connect_using_activedirectory_integrated_sqlalchemy
======================== 2 failed, 2 passed in 38.82s =========================

As you can see I can connect and get data via pyodbc on its own. But not via sqlalchemy.

Versions.

• OS: Window 10
• Python: 3.7.6
• SQLAlchemy: 1.3.19
• Database: Microsoft Azure Synapse SQL Datawarehouse
• DBAPI: pyodbc 4.0.30

[zzzeek]

hey there -

if you could reduce the verbosity of stack traces that would be helpful.

There's much to scroll through here but the essential nature seems to be that this is the error:

 (pyodbc.Error) ('FA003', "[FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0) (SQLDriverConnect); [FA003] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Password option must not be specified, if Authentication option is 'ActiveDirectoryInteractive'. (0)")
E       (Background on this error 

the connection string being passed to pyodbc is:

cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;UID=<USERNAME>@<DOMAIN>;PWD=;Authentication=ActiveDirectoryInteractive',)

above we can see PWD is empty. This is the result of the following SQLAlchemy URL:

f"mssql+pyodbc://{username}@{server}:1433/{database}?driver={DRIVER}&autocommit=true&Authentication=ActiveDirectoryInteractive"

where we also see there is no password.

your example that works uses this string:

= pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+";Authentication=ActiveDirectoryInteractive")

and I guess the difference is that the "PWD" prefix is omitted altogether.

we could propose that PWD is omitted from the string if there is no password in the URL. however, for now, since you are working with ODBC strings, just use them directly with SQLAlchemy:

create_engine('mssql+pyodbc:///odbc_connect?='DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+";Authentication=ActiveDirectoryInteractive")

[zzzeek]

@gordthompson issue here would probably be resolved by:

diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py
index 96ac0c1f1..b64d887c8 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -95,7 +95,9 @@ class PyODBCConnector(Connector):
             user = keys.pop("user", None)
             if user:
                 connectors.append("UID=%s" % user)
-                connectors.append("PWD=%s" % keys.pop("password", ""))
+                pwd = keys.pop("password", "")
+                if pwd:
+                    connectors.append("PWD=%s" % pwd)
             else:
                 connectors.append("Trusted_Connection=Yes")

do you see any negative side effects from that? is "PWD=;" with no value ever something needed?

[gordthompson]

@zzzeek

is "PWD=;" with no value ever something needed?

Not in my experience. SQL Server lets us create a login with a blank password, but when connecting via ODBC we can either use UID=anon;PWD= or just omit the PWD= argument altogether.

@MarcSkovMadsen - If you want to test the above patch you can

python -m pip unininstall sqlalchemy
python -m pip install git+https://github.com/gordthompson/sqlalchemy@mssql_pwd_1_3

[MarcSkovMadsen]

I believe there are two problems. One is that you cannot just specify a username without a password. Thats the problem you are adressing above.

But there is also the Cannot use Authentication option with Integrated Security option. (0) problem. 👍

I don't understand the cause. For me it looks like a SQL Alchemy thing that the ";Authentication=ActiveDirectoryInteractive" or ";Authentication=ActiveDirectoryIntegrated" is not supported. Because that thing works with pyodbc on its own as you can see from the tests.

self = <sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc object at 0x0000019CF6042E48>
cargs = ('DRIVER={ODBC Driver 17 for SQL Server};Server=<SERVERNAME>.database.windows.net,1433;Database=<DATABASE>;Trusted_Connection=Yes;Authentication=ActiveDirectoryIntegrated',)
cparams = {'autocommit': True}

    def connect(self, *cargs, **cparams):
        # inherits the docstring from interfaces.Dialect.connect
>       return self.dbapi.connect(*cargs, **cparams)
E       pyodbc.Error: ('FA001', '[FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0) (SQLDriverConnect); [FA001] [Microsoft][ODBC Driver 17 for SQL Server]Cannot use Authentication option with Integrated Security option. (0)')

.venv\lib\site-packages\sqlalchemy\engine\default.py:493: Error

[gordthompson]

@MarcSkovMadsen - Thanks for the clarification. I have pushed another commit to my mssql_pwd_1_3 branch. Please uninstall and reinstall via pip and see if that works any better.

[zzzeek]

these can all be in a single gerrit w/ single changelog

[gordthompson]

@zzzeek - That's the ultimate plan, for sure. We'll also need to do some refactoring as this is mssql-specific so it really belongs in sqlalchemy/dialects/mssql/pyodbc.py, not sqlalchemy/connectors/pyodbc.py. Just trying to pin down the requirements first.

[gordthompson]

@MarcSkovMadsen - It occurred to me that the "authentication=" keyword should be lowercase to be consistent with "driver=" so I pushed another change to my branch. The connection URI I used for testing was

connection_uri = (
    "mssql+pyodbc://@server_name/db_name?"
    "driver=ODBC+Driver+17+for+SQL+Server;"
    "authentication=ActiveDirectoryIntegrated"
)

[MarcSkovMadsen]

I’m really impressed by the collaboration and Communication on this problem.

Will test tomorrow Danish time when i’m Back on Work.

[MarcSkovMadsen]

Steps

I lowercased authentication in the connectionstring and then ran

python -m venv .venv2
source .venv2/Scripts/activate
pip install git+https://github.com/gordthompson/sqlalchemy.git@mssql_pwd_1_3
(Successfully installed SQLAlchemy-1.3.20.dev0)
pip install pyodbc pandas
(Successfully installed numpy-1.19.2 pandas-1.1.2 pyodbc-4.0.30 python-dateutil-2.8.1 pytz-2020.1 six-1.15.0)
pip install pytest
(Successfully installed atomicwrites-1.4.0 attrs-20.2.0 colorama-0.4.3 importlib-metadata-1.7.0 iniconfig-1.0.1 more-itertools-8.5.0 packaging-20.4 pluggy-0.13.1 py-1.9.0 pyparsing-2.4.7 pytest-6.0.2 toml-0.10.1 zipp-3.1.0)

The original error is gone. But I get a new warning.

(.venv2) C:\repos\trading_analytics\us-trading>pytest users\masma\test_ode_connections.py::test_can_connect_using_activedirectory_integrated_sqlalchemy
================================================= test session starts =================================================
platform win32 -- Python 3.7.6, pytest-6.0.2, py-1.9.0, pluggy-0.13.1
rootdir: C:\repos\trading_analytics\us-trading, configfile: pytest.ini
collected 1 item

users\masma\test_ode_connections.py .                                                                            [100%]

================================================== warnings summary ===================================================
users/masma/test_ode_connections.py::test_can_connect_using_activedirectory_integrated_sqlalchemy
  c:\repos\trading_analytics\us-trading\.venv2\lib\site-packages\sqlalchemy\dialects\mssql\base.py:2434: SAWarning: Could not fetch transaction isolation level, tried views: ('sys.dm_exec_sessions', 'sys.dm_pdw_nodes_exec_sessions'); final error was: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]User does not have permission to perform this action. (6004) (SQLExecDirectW)')
    "tried views: %s; final error was: %s" % (views, last_error)

-- Docs: https://docs.pytest.org/en/stable/warnings.html
============================================ 1 passed, 1 warning in 23.23s ============================================

If I run it with pyodbc alone

(.venv2) C:\repos\trading_analytics\us-trading>pytest users\masma\test_ode_connections.py::test_can_connect_using_activedirectory_integrated_official
================================================================================================ test session starts =================================================================================================
platform win32 -- Python 3.7.6, pytest-6.0.2, py-1.9.0, pluggy-0.13.1
rootdir: C:\repos\trading_analytics\us-trading, configfile: pytest.ini
collected 1 item

users\masma\test_ode_connections.py .                                                                                                                                                                           [100%]

================================================================================================= 1 passed in 23.77s =================================================================================================

it works.

My hypothesis is that sqlalchemy is doing some select that PYODBC is not. I'm just asking to execute "select top 2 * from eia.data_view"

Would it be possible to fix?

[gordthompson]

@MarcSkovMadsen - If it's just a warning then it doesn't really need to be "fixed". Azure DW is a bit different from SQL Server when it comes to things like transactions.

[sqla-tester]

Gord Thompson has proposed a fix for this issue in the master branch:

Add support for Azure authentication options https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2241

[sqla-tester]

Gord Thompson has proposed a fix for this issue in the rel_1_3 branch:

Add support for Azure authentication options https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2242

[zzzeek]

it would be good if the driver could get the transaction isolation level, so that warning does say something useful.

[gordthompson]

@zzzeek - We already try checking sys.dm_pdw_nodes_exec_sessions which is the right place to look for Azure DW. In this particular case the problem is:

User does not have permission to perform this action. (6004) (SQLExecDirectW)')

[MarcSkovMadsen]

Thanks for helping out 👍

[dhirschfeld]

Was just going to put in a PR to fix this only to find I was beaten to it - thanks! :tada:

Connecting to an Azure SQL database and having to work around this by passing through a pyodbc DSN directly. Would love to see this fix in a release - is there one planned shortly?

[gordthompson]

Would love to see this fix in a release - is there one planned shortly?

Lately, releases have happened every 6 to 8 weeks and the last one was mid-August so I would expect there to be another one before too long.

[dhirschfeld]

Thanks for the info @gordthompson! Since it's not too far away I can just wait for the new release and pin to that version when it's out...