Application Roles ENCRYPT function Valid Password Characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chuck Hawkins - 28 Jun 2005 20:49 GMT

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?

Reply to this Message

Chuck Hawkins - 28 Jun 2005 20:52 GMT

You might ask me what is a good password? Here is a sample:

wZ72¶û·³é6¼ÞÕBºcF÷vR?g¾§oLVmxÚgêÀúsGLkîZ±¯pq¦Q÷ñôbZíÚ<YÙÅÞØüã·Îfëu¨<ú?ýL<¶tÙÁïÈRQ¤ïzCa³8ï½â5åó¨ø®c?É"DXtIæ,QPÉbUétIyÉæ·BSJbqF?é
(without the line return)

> 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?

Reply to this Message

Sue Hoegemeier - 29 Jun 2005 05:54 GMT

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

>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?

Reply to this Message

Chuck Hawkins - 29 Jun 2005 13:04 GMT

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

> You can find the valid characters in the books online help
> topic: Security Rules.
[quoted text clipped - 7 lines]
>>password. What characters are and are not allowed for passwords for
>>application roles while using the ENCRYPT function?

Reply to this Message

Chuck Hawkins - 29 Jun 2005 14:41 GMT

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

> Thank you, Sue. I went back and re-wrote my password generation script to
> remove references to the unallowed characters mentioned in Security Rules
[quoted text clipped - 33 lines]
>>>password. What characters are and are not allowed for passwords for
>>>application roles while using the ENCRYPT function?

Reply to this Message

Chuck Hawkins - 29 Jun 2005 13:06 GMT

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

Reply to this Message