There are some strange limits on what an application role
password can be set to when using ODBC encryption. If the
password starts with a number or contains a dash (there
may be other situations as well), the following statement
fails with a "Syntax error or access violation" error:

exec sp_setapprole 'sales', {ENCRYPT N'123'}, 'ODBC'

but this works without the encryption:

exec sp_setapprole 'sales', '123'

Is this a bug?

Yes, this is a bug. You can contact PSS if you require a fix.

I can't see any workarounds. We are (wrongly) expecting an 'identifier'
(starts with a letter, terminates at whitespace/operators/etc). So when
parsing the string, if it starts with a number, we don't consider it an
'identifier'. If it contains a dash, we stop parsing at the dash (other
operators like '+', etc should behave the same).

Brannon

"Chuck Rudolph" <crudolph@iatrosoft.com> wrote in message
news:04d401c37e29$e75e1f10$a401280a@phx.gbl...
> Before anybody asks, I'm using SQL Server 2000 SP3a.
>
> >-----Original Message-----
> >There are some strange limits on what an application role
> >password can be set to when using ODBC encryption. If the
> >password starts with a number or contains a dash (there
> >may be other situations as well), the following statement
> >fails with a "Syntax error or access violation" error:
> >
> >exec sp_setapprole 'sales', {ENCRYPT N'123'}, 'ODBC'
> >
> >but this works without the encryption:
> >
> >exec sp_setapprole 'sales', '123'
> >
> >Is this a bug?
> >.
> >