Fix Azure Synapse connection error

Fixed regression caused by the fix for :ticket:`8231` released in 1.4.40
where connection would fail if the user does not have permission to query
the dm_exec_sessions or dm_pdw_nodes_exec_sessions system view when trying
to determine the current transaction isolation level.

Fixes: #8475
Change-Id: Ie2bcda92f2ef2d12360ddda47eb6e896313c71f2

1 files changed, 25 insertions, 18 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 94e082611..82a5bb6f7 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -3008,27 +3008,34 @@ class MSDialect(default.DefaultDialect):
                 )
 
             view_name = "sys.{}".format(row[0])
-            cursor.execute(
-                """
-                    SELECT CASE transaction_isolation_level
-                    WHEN 0 THEN NULL
-                    WHEN 1 THEN 'READ UNCOMMITTED'
-                    WHEN 2 THEN 'READ COMMITTED'
-                    WHEN 3 THEN 'REPEATABLE READ'
-                    WHEN 4 THEN 'SERIALIZABLE'
-                    WHEN 5 THEN 'SNAPSHOT' END AS TRANSACTION_ISOLATION_LEVEL
-                    FROM {}
-                    where session_id = @@SPID
-                """.format(
-                    view_name
+
+            try:
+                cursor.execute(
+                    """
+                        SELECT CASE transaction_isolation_level
+                        WHEN 0 THEN NULL
+                        WHEN 1 THEN 'READ UNCOMMITTED'
+                        WHEN 2 THEN 'READ COMMITTED'
+                        WHEN 3 THEN 'REPEATABLE READ'
+                        WHEN 4 THEN 'SERIALIZABLE'
+                        WHEN 5 THEN 'SNAPSHOT' END
+                        AS TRANSACTION_ISOLATION_LEVEL
+                        FROM {}
+                        where session_id = @@SPID
+                    """.format(
+                        view_name
+                    )
                 )
-            )
-            row = cursor.fetchone()
-            assert row is not None
-            val = row[0]
+            except self.dbapi.Error as err:
+                raise NotImplementedError(
+                    "Can't fetch isolation level;  encountered error {} when "
+                    'attempting to query the "{}" view.'.format(err, view_name)
+                ) from err
+            else:
+                row = cursor.fetchone()
+                return row[0].upper()
         finally:
             cursor.close()
-        return val.upper()
 
     def initialize(self, connection):
         super(MSDialect, self).initialize(connection)