Using Application Roles in SQL Server

[markus.essmayr]

Hello,

I'm using Application Roles with SQL Server databases.

To activate an Application Role, the application has to call the stored procedure sp_setapprole providing the Application Role name and password.

In my application, I use this function to activate an Application Role:

Code: Select all

!!CB!! 279
Function: SetApplicationRole
   Description: Activates the Application Role on the given Database Connection.
         This can be done only once per Connection.
         *Returns: TRUE - Application Role activated, FALSE - Error
   Returns
      Boolean:
   Parameters
      Sql Handle: sqlHandle
         ! [in] Database Connection
      String: applicationRole
         ! [in] Application Role Name
      String: applicationRolePassword
         ! [in] Application Role Password
   Static Variables
   Local variables
      Boolean: result
         ! Result of the method
      String: sql
         ! SQL-Command
      Number: parameterNumber
         ! numeric parameter
      String: parameterString
         ! string parameter
   Actions
      ! Activate Application Role
      Set result = FALSE
      !
      ! Build SQL-Command
      Set sql = "{Call sp_setapprole( :applicationRole, :applicationRolePassword )}"
      !
      ! Activate Application Role - execute
      Call SqlGetParameter( sqlHandle, DBP_AUTOCOMMIT, parameterNumber, parameterString )
      Call SqlSetParameter( sqlHandle, DBP_AUTOCOMMIT, TRUE, '' )
      When SqlError
         ! don't display error message
         Return FALSE
      Call SqlSetResultSet( sqlHandle, FALSE )
      If ( SqlPrepareSP( sqlHandle, sql , STRING_Null) )
         When SqlError
            ! keine Fehlermeldung ausgeben
            Return FALSE
         If ( SqlExecute( sqlHandle ) )
            ! Application Role activated
            Set result = TRUE
            !
            ! Close Result Sets
            Call SqlCloseAllSPResultSets( sqlHandle )
      Call SqlSetResultSet( sqlHandle, TRUE )
      Call SqlSetParameter( sqlHandle, DBP_AUTOCOMMIT, parameterNumber, '' )
      !
      Return result

This basically works but keeps the Application Role active.
Sometimes when repeatedly perform SqlConnect, activate the Application Role and then call SqlDisconnect, the SQL Server client raises the following error:
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

So I want to deactivate the Application Role by calling sp_unsetapprole before disconnecting.

But here is my problem!
To successfully call sp_unsetapprole, I have to query sp_setapprole's output parameter @cookie.
But output parameters are not (yet) supported by TD.
Is this still the case?
(When) Will TD get output parameter support for SQL Server Stored Procedures?

Does anybody of you know another way to use Application Roles in TD which allows deactivation?
Any other suggestions that could help me?

Thanks in advance!
Max