Issue 1:
Describe the bug

When executing a query using mssql+pyodbc driver, the following error occurs if using SQLAlchemy version 1.4.16 or newer with pyodbc 4.0.31:

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

To Reproduce

Install dependencies pyodbc (tested on latest, 4.0.31) and SQLAlchemy 1.4.16 or newer.

from sqlalchemy import create_engine

connection_string = "mssql+pyodbc://localhost/master?driver=ODBC+Driver+17+for+SQL+Server?Trusted_Connection=yes"

engine = create_engine(connection_string)
engine.execute("select 1 as result")

Error

Traceback (most recent call last):
File "...\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
return fn()
File "...\sqlalchemy\pool\base.py", line 301, in connect
return _ConnectionFairy._checkout(self)
File "...\sqlalchemy\pool\base.py", line 761, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "...\sqlalchemy\pool\base.py", line 419, in checkout
rec = pool._do_get()
File "...\sqlalchemy\pool\impl.py", line 145, in _do_get
self._dec_overflow()
File "...\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "...\sqlalchemy\util\compat.py", line 207, in raise_
raise exception
File "...\sqlalchemy\pool\impl.py", line 142, in _do_get
return self._create_connection()
File "...\sqlalchemy\pool\base.py", line 247, in _create_connection
return _ConnectionRecord(self)
File "...\sqlalchemy\pool\base.py", line 362, in __init__
self.__connect()
File "...\sqlalchemy\pool\base.py", line 605, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "...\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "...\sqlalchemy\util\compat.py", line 207, in raise_
raise exception
File "...\sqlalchemy\pool\base.py", line 599, in __connect
connection = pool._invoke_creator(self)
File "...\sqlalchemy\engine\create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "...\sqlalchemy\engine\default.py", line 584, in connect
return self.dbapi.connect(*cargs, **cparams)
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

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

Traceback (most recent call last):
File "...\example.py", line 6, in <module>
engine.execute("select 1 as result")
File "<string>", line 2, in execute
File "...\sqlalchemy\util\deprecations.py", line 390, in warned
return fn(*args, **kwargs)
File "...\sqlalchemy\engine\base.py", line 3107, in execute
connection = self.connect(close_with_result=True)
File "...\sqlalchemy\engine\base.py", line 3166, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "...\sqlalchemy\engine\base.py", line 96, in __init__
else engine.raw_connection()
File "...\sqlalchemy\engine\base.py", line 3245, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "...\sqlalchemy\engine\base.py", line 3215, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "...\sqlalchemy\engine\base.py", line 2068, in _handle_dbapi_exception_noconnection
util.raise_(
File "...\sqlalchemy\util\compat.py", line 207, in raise_
raise exception
File "...\sqlalchemy\engine\base.py", line 3212, in _wrap_pool_connect
return fn()
File "...\sqlalchemy\pool\base.py", line 301, in connect
return _ConnectionFairy._checkout(self)
File "...\sqlalchemy\pool\base.py", line 761, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "...\sqlalchemy\pool\base.py", line 419, in checkout
rec = pool._do_get()
File "...\sqlalchemy\pool\impl.py", line 145, in _do_get
self._dec_overflow()
File "...\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "...\sqlalchemy\util\compat.py", line 207, in raise_
raise exception
File "...\sqlalchemy\pool\impl.py", line 142, in _do_get
return self._create_connection()
File "...\sqlalchemy\pool\base.py", line 247, in _create_connection
return _ConnectionRecord(self)
File "...\sqlalchemy\pool\base.py", line 362, in __init__
self.__connect()
File "...\sqlalchemy\pool\base.py", line 605, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "...\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "...\sqlalchemy\util\compat.py", line 207, in raise_
raise exception
File "...\sqlalchemy\pool\base.py", line 599, in __connect
connection = pool._invoke_creator(self)
File "...\sqlalchemy\engine\create.py", line 578, in connect
return dialect.connect(*cargs, **cparams)
File "...\sqlalchemy\engine\default.py", line 584, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/14/rvf5)

Versions.

OS: Windows 10

Python: 3.9.4

SQLAlchemy: 1.4.16+

Database: SQL Server 2017

DBAPI: pyodbc

Additional context

The link http://sqlalche.me/e/14/rvf5 points out that this is a problem in the DBAPI driver, and that page suggests it is not a SQL Alchemy error. However, the problem is resolved by simply downgrading SQLAlchemy to 1.4.15 and keeping the same pyodbc driver. Perhaps the connection string is not being passed into pyodbc correctly.

Issue 2:
Using 1.4.20 when I try to make a connection to sql server I get a data source name not found and no default driver specified

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDr...

here is the way I trigger the error

def test_db_connect(db_cnxn_str):
engine = sa.create_engine(db_cnxn_str)
conn = engine.connect() # << ERROR HAPPENS HERE
# rs = conn.execute("SELECT GETDATE()")
assert conn

If I change my sqlalchemy version to 1.4.12 I am able to connect without any issue.

Versions.

OS: Windows 10

Python: 3.9

SQLAlchemy: 1.4.20

Database: MSSQL

DBAPI: pyodbc