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?You might ask me what is a good password? Here is a sample:
wZ726BcF_vR?goLVmxgsGLkZpqQbZ<Yfu<?L<t
RQzCa85c?"DXtI,QPbUtIyBSJbqF?
(without the line return)
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message
news:ebySvpBfFHA.3944@.TK2MSFTNGP10.phx.gbl...
> 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?
>|||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?
>|||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
'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??D
k2?q6L?EvrvI1mOENycWpLvz
jL?3kn'}
--@.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:
>
>|||Incidentally, here is the ugly password generation code:
set nocount on
declare @.counter int,
@.password varchar(128),
@.char char(1),
@.charindex int,
@.loop int
/* Unallowed characters:
! = 33
( = 40
) = 41
, = 40
* = 42
; = 59
? = 63
@. = 64
[ = 91
] = 93
{ = 123
} = 125
*/
select @.counter = 1, @.password = ''
while @.counter < 2
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 65 and 90 or @.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 4
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 5
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 10
begin
-- Restrict the password to NOT 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if --@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
--or
(@.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 11
begin
--Restrict the password to 0-9
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if @.charindex between 48 and 57 --or @.charindex between 65 and 90 or
@.charindex between 97 and 122
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
--or @.charindex between 161 and 255 or @.charindex between 130 AND 140
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
while @.counter < 129
begin
--Restrict the password to 0-9, A-Z, and a-z
select @.loop = 1
while @.loop = 1
begin
select @.charindex = convert(int, rand() * 254)
if (@.charindex between 48 and 57 or @.charindex between 65 and 90 or
@.charindex between 97 and 122
or @.charindex between 161 and 255 or @.charindex between 130 AND 140)
and @.charindex not in (33,40,41,42,59,63,64,91,93,123,125)
select @.loop = 0
end
--Accumulate characters for password string
select @.char = char(@.charindex)
select @.password = @.password + @.char
select @.counter = @.counter + 1
end
select RTRIM(@.password) AS Password
"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:
>
>|||What I've discovered:
The password characters are not allowed for or the canonical ENCRYPT functio
n 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 m
ore than 64 characters (vice 128 allowed in Security Rules).
All that said, it still doesn't work. When I enter the following code, I can
not get the SP_SETAPPROLE to work:
exec sp_dropapprole 'TEST_APPROLE'
go
exec sp_addapprole
@.rolename = 'TEST_APPROLE',
@.password = 'rwq4?37ctEPn0izwhJ6dq
dACSZcfmfia?fWG
'
go
sp_setapprole
@.rolename = 'TEST_APPROLE',
@.password = {Encrypt N 'rwq4?37ctEPn0izwhJ6dq
dACSZc
fmfia?fWG'}
--@.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 6
4 characters? I have my suppositions but I'd love to hear from someone who k
nows.
Chuck Hawkins
"Chuck Hawkins" <charles.hawkins@.NOSPAMjenzabar.net> wrote in message news:OUXAtKKfFHA.572@.T
K2MSFTNGP15.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
> 'ro111гPM0ci?TxmOK
e3qJDtSV?Xrи"S??
Dk2?q6L?EvrvI1mOENycWpLv
zjL?3kn'}
> --@.password = {Encrypt N 'easy'}
> ,@.encrypt = 'odbc'
> go
>
> And get:
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
>
> 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...
>
>
