Executing sp_setapprole from Python/pyodbc fails

I have not been able to successfully execute the SQL Server stored procedure sp_setapprole via pyodbc

SQL Server 2019
Client OS: Windows 10
Python: v3.8
PyODBC: v4.0.34
SQLAlchemy: v1.4.39

End-user security is is initially managed via Active Security Groups

There is a SQL Server Logon for the Active Directory group that is a member of the SQL server's public role and has the "Connect SQL" permission for the SQL Server

Additionally, the SQL Server Logon is mapped to a user account in the target database. That user account is a member of the "public" role for the database, and has only been explicitly granted "CONNECT" permission to the database.

I've tested the SQL code from within SQL Server Management Studio (SSMS) and it works without any issues.

When I run the same code using Python/pyodbc, it generates errors that I have not been able to resolve.

NOTE 1: The error messages vary depending on which ODBC driver I use. (see below)

SQL Server (SQLSRV32.DLL)

ERROR: An exception/error has occurred.
Traceback (most recent call last):
  File "C:\Users\...\testcode.py", line ..., in ...
    cursor_obj.execute(sql, sp_val)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Application roles can only be activated at the ad hoc level. (15422) (SQLExecDirectW)")

SQL Server Native Client 11.0 (SQLNCLI11.DLL)

ERROR: An exception/error has occurred.
Traceback (most recent call last):
  File "C:\Users\...\testcode.py", line ..., in ...
    cursor_obj.execute(sql, sp_val)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The procedure 'sys.sp_setapprole' cannot be executed within a transaction. (15002) (SQLExecDirectW)")

ODBC Driver 17 for SQL Server (MSODBCSQL17.DLL)

ERROR: An exception/error has occurred.
Traceback (most recent call last):
  File "C:\Users\...\testcode.py", line ..., in ...
    cursor_obj.execute(sql, sp_val)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The procedure 'sys.sp_setapprole' cannot be executed within a transaction. (15002) (SQLExecDirectW)")

NOTE 2: All other code in the app works. Although the bulk of the interaction with the database is via SQL Alchemy, other routines that utilize pyodbc work without issue.

NOTE 3: I am not trying to run "sp_setapprole" multiple times. This issue/problem occurs the first time I try to run the stored procedure, prior to doing anything else with the database.

NOTE 4: db_engine is the SQLAlchemy engine object created from the database connection string. The following connection string was in use when the first error message above was generated:

connection_string = "DRIVER={SQL Server};SERVER=SQLserverName;DATABASE=MyDatabase;TRUSTED_CONNECTION=YES"

The following snippet contains the relevant code to the sp_setapprole problem:


app_role_info = {'@rolename' : 'MyAppRole',
                 '@password' : 'AppRolePassword',
                 '@fCreateCookie' : 1} # MSSQL True = 1
connection = db_engine.raw_connection()
try:
    cursor_obj = connection.cursor()
    first_key = True
    sp_var=' '
    sp_var_list=[]
    for k,v in app_role_info.items():
        if not first_key:
            sp_var = sp_var + ', '
        sp_var = sp_var + k + ' = ?'
        sp_val_list.append(v)
        first_key = False
        sp_val = tuple(sp_val_list)
        
    sql = f"""
    SET NOCOUNT ON;
    DECLARE @ret_value VARBINARY(8000);
    EXEC sp_setapprole {sp_var}, @cookie=@ret_value OUTPUT;
    SELECT @ret_value AS AppRoleCookie;
    """
    
    cursor_obj.execute(sql, sp_val)

Any assistance / guidance / words of wisdom will be greatly appreciated.

UPDATE: Taking into consideration @siggemannen comments, I made the following changes to the code and removed SQL Alchemy from the equation...

connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=SQLserverName;DATABASE=MyDatabase;TRUSTED_CONNECTION=YES"

app_role_info = {'@rolename' : 'MyAppRole',
                 '@password' : 'AppRolePassword',
                 '@fCreateCookie' : 1} # MSSQL True = 1
connection = pyodbc.connect(connection_string, autocommit=True)

try:
    cursor_obj = connection.cursor()
    first_key = True
    sp_var=' '
    sp_var_list=[]
    for k,v in app_role_info.items():
        if not first_key:
            sp_var = sp_var + ', '
        sp_var = sp_var + k + ' = ?'
        sp_val_list.append(v)
        first_key = False
        sp_val = tuple(sp_val_list)
        
    sql = f"""
    SET NOCOUNT ON;
    DECLARE @ret_value VARBINARY(8000);
    EXEC sp_setapprole {sp_var}, @cookie=@ret_value OUTPUT;
    SELECT @ret_value AS AppRoleCookie;
    """
    
    cursor_obj.execute(sql, sp_val)

Running the updated code (above) now causes the following exception/error:

ERROR: An exception/error has occurred.
Traceback (most recent call last):
  File "C:\Users\...\testcode.py", line ..., in ...
    cursor_obj.execute(sql, sp_val)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Application roles can only be activated at the ad hoc level. (15422) (SQLExecDirectW)")

That error is different than the earlier error message for the ODBC Driver 17 for SQL Server earlier in this post.

I believe that @siggemannen comments were correct and setting autocommit to True corrected the Transaction/Nesting issue. I still have no idea how to correct the "...ad hoc level." error.

Solution

PyOdbc does not support .callproc, as noted in the docs so it's probably not going to work at all the way you are doing it. It seems sp_setapprole cannot be called from a parameterized batch at all.

The only thing that might work is to use ODBC call escape syntax:

tuple = (role_name, pass, "none", "odbc", true, null,)
cursor_obj.execute("{call sp_setapprole(?, ?, ?, ?, ?, ?)}", tuple)

and then the last of those tuple values should become the output parameter