sp_setapprole parameters are revealed in ODBC trace log

From: Trevor (trev0r2oo1@hotmail.com)
Date: 03/31/03

  • Next message: Jose Molina: "Re: System Administrator Implied Permissions" 
    From: "Trevor" <trev0r2oo1@hotmail.com>
Date: Sun, 30 Mar 2003 18:14:53 -0800

    I noticed that the password for the application role is
    visible in the ODBC trace log.

    Can someone please tell me how I can stop the ODBC trace
    from revealing the password?

    The closest I got was to pass the password as a parameter
    in the query, &#8230; But failed with the message &#8220;Application
    roles can only be activated at the ad hoc level. (15422)&#8221;

    I&#8217;m connecting to a MS SQL Server 7.0 with a test
    application written in Delphi 5.0.

    ===== ODBC TRACE on SQL Query Analyzer =========

    ISQLW fff2dbef-fff3956f ENTER SQLExecDirectW
    HSTMT 00DC019C
    WCHAR * 0x0047E1EC [ -3] "EXEC sp_setapprole 'Test',
    {Encrypt N 'pswd'}, 'odbc'\ d\ a\ 0"
    SDWORD -3

    ===== ODBC TRACE on Test Application =========

    SQL_APP_ROLE_TE d3-e9 ENTER SQLExecDirect
    HSTMT 03251FF0
    UCHAR * 0x0122E8BC [ -3] "EXEC sp_setapprole 'Test',
    {Encrypt N 'pswd'}, 'odbc'\ d\ a\ 0"
    SDWORD -3

    ===== ODBC TRACE on Test Application === password as a
    parameter ======

    SQL_APP_ROLE_TE dc-ee EXIT SQLExecDirect with return code -
    1 (SQL_ERROR)
    HSTMT 03251FF0
    UCHAR * 0x012283EC [ -3] "EXEC sp_setapprole ? ,? \ d\ a\
    0"
    SDWORD -3

    DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL
    Server]Application roles can only be activated at the ad
    hoc level. (15422)

  • Next message: Jose Molina: "Re: System Administrator Implied Permissions"