Re: Application Roles ENCRYPT function Valid Password Characters

From: Chuck Hawkins (charles.hawkins_at_NOSPAMjenzabar.net)
Date: 06/29/05

  • Next message: Kevin Madsen: "Re: How to keep SQL-DMZ and SQL-Internal in sync ?" 
    Date: Wed, 29 Jun 2005 09:41:42 -0400

    What I've discovered:
    The password characters are not allowed for or the canonical ENCRYPT function does not work with characters with the following ASCII codes:
    (33,40,41,42,59,63,64,91,93,123,125,130,132,133,134,135,136,137,139,161,162,166,167,168,169,171,172,173,174,175,176,177,180,182,184,187,188,189,190,191,215,247)

    Further, in order for the ENCRYPT function to work, the password cannot be more than 64 characters (vice 128 allowed in Security Rules).

    All that said, it still doesn't work. When I enter the following code, I cannot get the SP_SETAPPROLE to work:

    exec sp_dropapprole 'TEST_APPROLE'
    go

    exec sp_addapprole
    @rolename = 'TEST_APPROLE',
    @password = 'rwq4èÑǺÖ3Ì7ctå³úìEò¤Pn0ìèizwö·ÂhJ6dq¤ädACÆSZËcÓËfmfºiaĵÊfóWüGð'
    go

    sp_setapprole
    @rolename = 'TEST_APPROLE',
    @password = {Encrypt N 'rwq4èÑǺÖ3Ì7ctå³úìEò¤Pn0ìèizwö·ÂhJ6dq¤ädACÆSZËcÓËfmfºiaĵÊfóWüGð'}
    --@password = {Encrypt N 'easy'}
    ,@encrypt = 'odbc'
    go

    Server: Msg 2764, Level 16, State 1, Procedure sp_setapprole, Line 41
    Incorrect password supplied for application role 'TEST_APPROLE'.

    So the question remains, what are valid password characters for application roles in order for the ENCRYPT function to work?

    And now we have a new question, why does the ENCRYPT function limit you to 64 characters? I have my suppositions but I'd love to hear from someone who knows.

    Chuck Hawkins

    "Chuck Hawkins" <charles.hawkins@NOSPAMjenzabar.net> wrote in message news:OUXAtKKfFHA.572@TK2MSFTNGP15.phx.gbl...
    > Thank you, Sue. I went back and re-wrote my password generation script to
    > remove references to the unallowed characters mentioned in Security Rules
    > for passwords - []{}(),;?*! @.
    > I'm still having the problem with the ENCRYPT function. I execute:
    >
    > sp_setapprole
    > @rolename = 'TEST',
    > @password = {Encrypt N
    > 'ro11ùª¨¿®1гPÍñMï0ciï±ÄµTºÕx¡÷àmïÎOKìÆÆe3qJ³ÒÎíDî²tSÝàVçÞ«½îX£¦rиô"SÕ׬?Dãøk2Õ»q6×ðLÛ¹µÀE¬vrv×I1¸m¿èOñEâNycWÓpLvzãjáöLâ²Ç?Ï3kïn'}
    > --@password = {Encrypt N 'easy'}
    > ,@encrypt = 'odbc'
    > go
    >
    > And get:
    > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
    >
    > I know I don't have a syntax error (other than an ugly password). When I
    > switch the TEST app role over to a password of 'easy', it works.
    >
    > Am I supposed to put braces [] around the password somehow?
    >
    > So the question remains, what characters are not allowed for passwords? I
    > know []{}(),;?*! @. are not, but I don't have any of these.
    >
    > Chuck
    >
    > "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
    > news:99a4c1p6996f5mklnjbq0hhmapjunkv95u@4ax.com...
    >> You can find the valid characters in the books online help
    >> topic: Security Rules.
    >> You can find the topic in the index under passwords, rules
    >> for
    >>
    >> -Sue
    >>
    >> On Tue, 28 Jun 2005 15:49:06 -0400, "Chuck Hawkins"
    >> <charles.hawkins@NOSPAMjenzabar.net> wrote:
    >>
    >>>When I try to use the ODBC canonical ENCRYPT function for SP_SETAPPROLE, I
    >>>get an ODBC error when certain otherwise good characters are used in the
    >>>password. What characters are and are not allowed for passwords for
    >>>application roles while using the ENCRYPT function?
    >>>
    >>
    >
    >

  • Next message: Kevin Madsen: "Re: How to keep SQL-DMZ and SQL-Internal in sync ?"

    Relevant Pages