Start a new topic

Virtual user table SQL

reima @ Tue Apr 01 00:04:03 EEST 2014
I'm trying to use CrushFTP with an existing SqlServer user table:
[code]
CREATE VIEW ftp.V_USERS (
userid, username, password, server_group
)
AS
SELECT pd.IDPERSON, pd.USERNAME, 'MD5:' + CONVERT(VARCHAR(32), HashBytes('MD5', pd.[PASSWORD]), 2), 'MainUsers'
from dbo.WO_PERSDATA pd
union ALL
select userid, username, password, server_group from ftp.USERS
[/code]

it seems to me, that the password hash, created by SqlServer does not match the password expected by CrushFTP.

Is there anything I can do - or even use plain text passwords.

Unfortunately I cannot import the existing user table, because this is maintained on another place - and it is fairly large (18000 entries).

Thanks
Martin

spinkb @ Tue Apr 01 03:41:08 EEST 2014
Is the other table storing the password in plain text? Because it looks like your hashing the other password here in this view...and i would assume the other table is already a hash.
reima @ Tue Apr 01 06:16:46 EEST 2014
yes - the other table has passwords in clear text
spinkb @ Tue Apr 01 06:36:07 EEST 2014
OK, so check was SQL is doing then...

The password of "password" should have a MD5 value of "5f4dcc3b5aa765d61d8327deb882cf99". And CrushFTP expects that to be:

"MD5:5f4dcc3b5aa765d61d8327deb882cf99"

What are you getting if you try?
reima @ Tue Apr 01 06:57:53 EEST 2014
Thank you,

I'm impressed by the speed of your reply...

SqlServer gives the same paswword - but in upper case.

Putting my MD5 into upper case should solve my problem.

Thank you
Martin
spinkb @ Tue Apr 01 06:58:40 EEST 2014
The case of the hash doesn't matter, but the prefix characters of "MD5:" must be uppercase as this is what CrushFTP is looking for.
reima @ Tue Apr 01 07:19:25 EEST 2014
Thank you,

now I see that the Problem is with SqlServer: The SqlServer hash-function gives the right result, when I hash the string 'password'. But there is a different hash value, when a table field with content 'password' is hashed.

I will post the solution, when I found it.

Regards
Martin
reima @ Tue Apr 01 07:45:04 EEST 2014
Solution:

It is working when the MD5 is called from a Sqlserver function:

[code]
CREATE FUNCTION ftp.MD5
(
@value varchar(255)
)
RETURNS varchar(36)
AS
BEGIN
RETURN 'MD5:' + CONVERT(VARCHAR(32), HashBytes('MD5', @value), 2)
END

--------

CREATE VIEW ftp.V_USERS (
userid, username, password, server_group
)
AS
SELECT pd.IDPERSON, pd.USERNAME, ftp.MD5(pd.[PASSWORD]), 'MainUsers'
from dbo.WO_PERSDATA pd
[/code]

Reason is: The password field from the external table was NVARCHAR - and therefore a multi byte charset, which obviously gives a different MD5 hash value


Regards
Martin
Login to post a comment