February 3, 2014 at 12:30 pm
OK, I understand now this use of replace. Thus you should separate out this kind of check from the rest since the string '_Replace_' will be used in only a few of the 10000 weak passwords.
Also, I was able to get my hands on a ss2000 instance and I was suprised that my simple test script showed excellent performance, i.e. much better than ss2005 and ss2008 and only slightly slower than ss2012.
February 3, 2014 at 1:50 pm
Michael Meierruth (2/3/2014)
OK, I understand now this use of replace. Thus you should separate out this kind of check from the rest since the string '_Replace_' will be used in only a few of the 10000 weak passwords.Also, I was able to get my hands on a ss2000 instance and I was suprised that my simple test script showed excellent performance, i.e. much better than ss2005 and ss2008 and only slightly slower than ss2012.
Not sure how that's possible ... I can't even get data to return from SQL2000!
Using a user/pass of "test":
This works immediately
SELECT PWDCOMPARE('test',0x0100447FE763351FDE2B5FD81811B9CECF47011344CB042AA8C6A70B7534C25AAA0A86AFC17ED2FAC96FF19D1407)
This NEVER finishes
SELECT
sl.[name]AS [Login]
,pc.[Password]
FROM [master].[dbo].[syslogins] sl
JOIN [master].[dbo].[sysxlogins] slx
ON sl.[sid]= slx.[sid]
JOIN dbo.[PasswordCheckerList] pc
ON PWDCOMPARE(pc.[Password], slx.[password]) = 1
WHERE sl.[name] = 'test'
Same test, but with 2k5 and later, works like a champ and takes two seconds
SELECT
sl.[name]AS [Login]
,pc.[Password]
FROM [master].[sys].[sql_logins] sl
JOIN dbo.[PasswordCheckerList] pc
ON PWDCOMPARE(pc.[Password], sl.[password_hash]) = 1
WHERE sl.[name] = 'test'
February 3, 2014 at 2:09 pm
I think my simple performance test may have a weakness due to the random data I throw at it using newid(). This very likely causes pwdcompare to exit very early. In fact, when I run the script with a real hardcoded hash value, things start to slow down dramatically. Before I could run 10 million rows in 8 seconds. But now I'm down to 100,000 rows in 8 seconds.
select count(*),max(comp),min(comp)
from
(
select top 100000 pwdcompare(convert(nvarchar(128),newid()),
0x0200D5AC9406319E826BC8BE31ED96407EB653A650E6B95EE5C415E3A2FF077D21D4D2B8EDA11E1F283B785686EB9C82773917729528429663ED7C6675ABF82E1C62A8838498) comp
from sys.syscolumns c1,sys.syscolumns c2
) t
Thus I decided to load the 10000 weak passwords from the web site you indicated into a table called wpl and run the script below which is basically what you're doing. For 8 logins it runs in 16 seconds on my fast notebook. So it's all consumed by this pwdcompare function.
with
pwd as
(
select name,password_hash
from sys.sql_logins
where type='S'
),
hashcheck as
(
select name,wp,pwdcompare(wp,password_hash) err1,pwdcompare(reverse(wp),password_hash) err2
from pwd,wpl
)
select *
from hashcheck
where err1=1 or err2=1
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply