Add support for Azure authentication options

Fixes: #5592
Change-Id: I0688e5ea0fc6b01a0b72f397daea8f57a2ec0766

4 files changed, 38 insertions, 3 deletions

diff --git a/doc/build/changelog/unreleased_13/5592.rst b/doc/build/changelog/unreleased_13/5592.rst
new file mode 100644
index 000000000..294fadfff
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5592.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, mssql
+    :tickets: 5592
+
+    Fixed issue where a SQLAlchemy connection URI for Azure DW with
+    ``authentication=ActiveDirectoryIntegrated`` (and no username+password)
+    was not constructing the ODBC connection string in a way that was
+    acceptable to the Azure DW instance.
diff --git a/lib/sqlalchemy/connectors/pyodbc.py b/lib/sqlalchemy/connectors/pyodbc.py
index 96ac0c1f1..e1a7c99f4 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -95,9 +95,15 @@ 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")
+                authentication = keys.pop("authentication", None)
+                if authentication:
+                    connectors.append("Authentication=%s" % authentication)
+                else:
+                    connectors.append("Trusted_Connection=Yes")
 
             # if set to 'Yes', the ODBC layer will try to automagically
             # convert textual data from your database encoding to your
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index df340bbca..8bc318ea0 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -52,7 +52,8 @@ name must be URL encoded which means using plus signs for spaces::
 
 Other keywords interpreted by the Pyodbc dialect to be passed to
 ``pyodbc.connect()`` in both the DSN and hostname cases include:
-``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``.
+``odbc_autotranslate``, ``ansi``, ``unicode_results``, ``autocommit``, 
+``authentication`` (e.g., ``authentication=ActiveDirectoryIntegrated``).
 Note that in order for the dialect to recognize these keywords
 (including the ``driver`` keyword above) they must be all lowercase.
 
diff --git a/test/dialect/mssql/test_engine.py b/test/dialect/mssql/test_engine.py
index 27e92d5b5..a29d49c4c 100644
--- a/test/dialect/mssql/test_engine.py
+++ b/test/dialect/mssql/test_engine.py
@@ -156,6 +156,26 @@ class ParseConnectTest(fixtures.TestBase):
             True,
         )
 
+    def test_pyodbc_extra_connect_azure(self):
+        # issue #5592
+        dialect = pyodbc.dialect()
+        u = url.make_url(
+            "mssql+pyodbc://@server_name/db_name?"
+            "driver=ODBC+Driver+17+for+SQL+Server;"
+            "authentication=ActiveDirectoryIntegrated"
+        )
+        connection = dialect.create_connect_args(u)
+        eq_(connection[1], {})
+        eq_(
+            connection[0][0]
+            in (
+                "DRIVER={ODBC Driver 17 for SQL Server};"
+                "Server=server_name;Database=db_name;"
+                "Authentication=ActiveDirectoryIntegrated",
+            ),
+            True,
+        )
+
     def test_pyodbc_odbc_connect(self):
         dialect = pyodbc.dialect()
         u = url.make_url(