Skip to content

Regression in 1.17.0: Connection.close() fails on Azure Synapse dedicated SQL pool ("Setting IsolationLevel to ReadCommitted is not supported") #882

Closed

Description

Describe the bug

Since 1.17.0, Connection.close() raises on an Azure Synapse Analytics dedicated SQL pool when connection pooling is on (the default) and autocommit is on. The same code works on 1.16.0 and 1.15.0.

Exception message:
mssql_python.exceptions.ProgrammingError: Driver Error: Syntax error or access violation; DDBC Error: [Microsoft][SQL Server]Setting IsolationLevel to ReadCommitted is not supported.

Stack trace:
Traceback (most recent call last):
  File "repro.py", line 27, in <module>
    conn.close()
  File "<venv>/lib/python3.12/site-packages/mssql_python/connection.py", line 2240, in close
    _raise_connection_error(e)
  File "<venv>/lib/python3.12/site-packages/mssql_python/connection.py", line 240, in _raise_connection_error
    raise exc from None

To reproduce

Run this against any dedicated SQL pool. It signs in with a Microsoft Entra access token from the Azure CLI (run az login first); the sign-in method shouldn't matter.

import os
import struct
import subprocess

import mssql_python

SERVER = os.environ["REPRO_SERVER"]      # <server>.database.windows.net
DATABASE = os.environ["REPRO_DATABASE"]  # dedicated SQL pool name

token = subprocess.run(
    ["az", "account", "get-access-token", "--resource", "https://database.windows.net/",
     "--query", "accessToken", "-o", "tsv"],
    capture_output=True, text=True, check=True,
).stdout.strip().encode("utf-16-le")
SQL_COPT_SS_ACCESS_TOKEN = 1256

print("mssql-python", mssql_python.__version__)
conn = mssql_python.connect(
    f"Server=tcp:{SERVER};Database={DATABASE};Encrypt=yes;TrustServerCertificate=no",
    attrs_before={SQL_COPT_SS_ACCESS_TOKEN: struct.pack("=i", len(token)) + token},
)
conn.setautocommit(True)
cursor = conn.cursor()
cursor.execute("SELECT @@VERSION")
print(cursor.fetchone()[0])
cursor.close()
conn.close()
print("closed OK")
REPRO_SERVER=<server>.database.windows.net REPRO_DATABASE=<dedicated-pool> python repro.py

On 1.17.0 it prints @@VERSION, then conn.close() raises the error above.

Same script and server on each version:

mssql-python Autocommit on, default pooling Autocommit off mssql_python.pooling(enabled=False)
1.15.0 works works not tested
1.16.0 works works not tested
1.17.0 close() raises works works

Expected behavior

close() succeeds, as it does in 1.16.0. If a connection can't be cleaned up for the pool, it should be discarded rather than raising, because the caller has already finished with it.

Further technical details

Python version: 3.12.4
SQL Server version: Azure Synapse Analytics dedicated SQL pool (Microsoft Azure SQL Data Warehouse - 10.0.22627.0, SERVERPROPERTY('EngineEdition') = 6)
Operating system: reproduced on macOS 26.7.2 (arm64); first noticed on Ubuntu 24.04

Additional context

Regression: #820 (commit 7465c93, first released in 1.17.0).

With pooling on, ConnectionHandle::close() calls Connection::prepareForPool() (connection.cpp L970-L1004 at v1.17.0). #820 added an autocommit-on path to prepareForPool() that runs this batch (L806-L810):

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Dedicated SQL pools reject SET TRANSACTION ISOLATION LEVEL READ COMMITTED. The batch fails, and close() discards the connection and re-raises the error (L994-L998). With autocommit off, prepareForPool() rolls back with SQLEndTran instead, which is why that case works.

In 1.16.0, the isolation level was only reset when a pooled connection was reused (Connection::reset(), L603-L650 at v1.16.0). A failure there just dropped that candidate (connection_pool.cpp L172), so callers never saw it. The close-time cleanup itself came from #777 in 1.16.0 and worked on Synapse.

Possible fixes

  • Don't raise from close() when pool cleanup fails; discard the connection instead, which matches the 1.16.0 outcome.
  • Skip the isolation reset on engines that don't support READ COMMITTED (a dedicated SQL pool reports EngineEdition 6), or only reset it when the session changed it.

Workarounds

  • Pin mssql-python<1.17.
  • Call mssql_python.pooling(enabled=False) before connecting.

Unrelated, but it may help anyone reproducing on macOS: I had to add Homebrew's openssl@3/lib to DYLD_LIBRARY_PATH before the driver would load OpenSSL.

Activity

  1. github-actions commented on Nov 6, 2026

    @github-actions

    Hi Faraz Shaikh (@fshaikh-caa), thank you for opening this issue!

    Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

    If you have additional information to share, please feel free to update the issue.

    Thank you for your patience!

  2. added
    bugSomething isn't working
    regressionTracks issues which are regressions
    triage doneIssues that are triaged by dev team and are in investigation.
    area: connectivity-authConnection lifecycle, Entra/SP/NTLM auth, tokens, TLS, conn-string parsing, Fabric endpoints.
    and removed
    triage neededFor new issues, not triaged yet.
    on Nov 11, 2026
  3. jahnvi480 commented on Nov 13, 2026

    @jahnvi480
    Contributor

    The fix has been merged to main. Closing this issue

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

area: connectivity-authConnection lifecycle, Entra/SP/NTLM auth, tokens, TLS, conn-string parsing, Fabric endpoints.bugSomething isn't workinginADOregressionTracks issues which are regressionstriage doneIssues that are triaged by dev team and are in investigation.under development

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions