Password checker automation

Question

Password checker automation

Adam Bean

One Orange Chip

Points: 26631
More actions
January 27, 2014 at 7:28 am

#288816

Hey all,
Is anyone is familiar with any existing software out there that can be leveraged to automate checking of passwords?
Historically we've used the Idera Admin toolset which contains a cool little app that attempts to discover any passwords against a target instance. Unfortunately there is no way to automate this, and it doesn't support SQL 2012 (the old version we own anyway). So before renewing our license, we'd like to look at different options.
We were going to build our own custom process; however this is causing our SQL error logs to blow (due to password failures obviously). So I'm not sure how Idera accomplishes this. So if we can discover how they're not logging to the error log or simply find an existing piece of software out there, we'd be most appreciative.
Thanks

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

Why don't you simply use 'Enforce password policy' of SQL Server?

So what you're saying is that this software makes attempts to login using some sort of dictionary of bad passwords and if any of them succeed then an alert is sent out?

I hope I have misunderstood this.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Michael Meierruth (1/29/2014)
Why don't you simply use 'Enforce password policy' of SQL Server?
So what you're saying is that this software makes attempts to login using some sort of dictionary of bad passwords and if any of them succeed then an alert is sent out?
I hope I have misunderstood this.

Yes, your interpretation is correct. We want to be able to audit our hundreds of instances to generate reports based on where passwords are weak.

While a good thought, we currently don't leverage this largely because the vast majority of our environment is vendor supported and we can not yet enforce such policies. That and we still have old versions of SQL where we can't use policies.

Lowell SSC Guru Points: 323504 More actions · Answer 3

i have this saved in my snippets for auditing weak passwords;

basically i have a dictionary of known weak passwords to compare against, and also check if the password = loginname.

hope this helps:

DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))

INSERT INTO @WeakPwdList(WeakPwd)

SELECT ''

UNION ALL SELECT '123'

UNION ALL SELECT '1234'

UNION ALL SELECT '12345'

UNION ALL SELECT 'abc'

UNION ALL SELECT 'default'

UNION ALL SELECT 'guest'

UNION ALL SELECT '123456'

UNION ALL SELECT '@@Name123'

UNION ALL SELECT '@@Name'

UNION ALL SELECT '@@Name@@Name'

UNION ALL SELECT 'admin'

UNION ALL SELECT 'Administrator'

UNION ALL SELECT 'admin123'

UNION ALL SELECT 'asdf'

UNION ALL SELECT 'asdfasdf'

UNION ALL SELECT 'sa'

UNION ALL SELECT 'biteme'

UNION ALL SELECT 'hds'

UNION ALL SELECT 'hdssa'

UNION ALL SELECT 'password'

UNION ALL SELECT 'pass'

SELECT t1.*, REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]

FROM sys.sql_logins t1

INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1

OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1) --is the password the same as the user name?

WHERE t1.is_policy_checked = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

Lowell,

This is great, I have many ideas already on how to expand this pretty intensely.

That's exactly what I needed, I didn't realize PWDCOMPARE was out there! Unfortunately though, looks like this for 2008 and later, familiar with how to do this prior to 2008?

Thank you!

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 5

Thanks Lowell for this Script...Very handy and very useful 🙂

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 6

Adam Bean (1/29/2014)
Lowell,
This is great, I have many ideas already on how to expand this pretty intensely.
That's exactly what I needed, I didn't realize PWDCOMPARE was out there! Unfortunately though, looks like this for 2008 and later, familiar with how to do this prior to 2008?
Thank you!

Looks like PWDCOMPARE works on older versions of SQL Server as well(2000,2005). Not sure about 6.5 and 7...

I tested this script against one of our 2005 DEV Instances. It's working flawless...:-)

However, For SQL 2000, since we don't have sys.sys_logins, playing around with master.dbo.syslogins might help.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

So I've been working on rewriting this to hit a table of known bad passwords and running into a snag.

LEFT JOIN dbo.[PasswordCheckerList] pc

ON PWDCOMPARE(pc.[Password], sl.[password_hash]) = 1

OR PWDCOMPARE(REVERSE(pc.[Password]), sl.[password_hash]) = 1

OR PWDCOMPARE(REPLACE(pc.[Password],'_Replace_',sl.name), sl.[password_hash]) = 1

What I'm experiencing is duplicate data. I added the second line (REVERSE) to check for known passwords simply used backwards. When I hit a password that in it's actual form is stored in my list as well as the reversed form is stored, I get a dupe.

So for example, I have a user "test" with a password "0001". Both "0001" and "1000" are stored in my PasswordCheckerList. Because of this, that second line with the REVERSE causes two returns.

So I'm assuming my JOIN logic using OR's is incorrect. I've tried multiple variations of the above; however I've results in the same dupe each time.

Any thoughts?

Thanks

Michael Meierruth SSChampion Points: 10051 More actions · Answer 8

You cannot use the extra or-clause and have both '0001' and '1000' in the password list.

Do one or the other, but not both.

The query below does what you are doing and returns two rows - which is correct.

with

db as(select '0001' dbpw),

list as (select '0001' badpw union all select '1000')

select db.*,list.*

from db

left join list on db.dbpw=list.badpw or db.dbpw=reverse(list.badpw)

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

So what if any resolution is there?

In the example that this account has a password of 0001, and that's stored in the password list ... the reverse of that is also in the list. I just had assumed I am doing something wrong with the OR logic.

Because technically, once it hits that first criteria in the JOIN and find's a match, why is it still evaluating the OR at that point? I should have moved onto the next login being that 0001 was matched?

Nadrek SSC-Insane Points: 20039 More actions · Answer 10

If you want to get into more advanced checking, I would recommend using Hashcat (http://hashcat.net/oclhashcat/[/url]) against passwords extracted in bulk (i.e. multi-server query) with a rules-based dictionary attack.

You will need to separate out SQL2000 passwords (which start with 0x0100), SQL2005+SQL2008+SQL2008R2 passwords (which also start with 0x0100, but are a different length), and SQL2012 passwords (which start with 0x0200).

Lowell SSC Guru Points: 323504 More actions · Answer 11

Adam i think you just want to do wrap your query as a cte/subselect to get distinct logins with weak passwords, since your new logic allows multiple matches.

if '0001' and '1000' are both rows in yout PasswordCheckerList, then that's two rows that are going to match..once for regular, and once for a reverse.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nadrek SSC-Insane Points: 20039 More actions · Answer 12

Separately, the phpbb password list is an excellent, small starter list - add in words related to your company, your software products, names from your employee list, etc. Just dump every SQL Server account name into your weak password list for simplicity :).

Don't forget to add 0-999 to the end of them; those are very common passwords, perhaps a little 1337 speak, etc. to go along with your REVERSE.

Make sure you can catch P@ssw0rd123! and P@$$w0rd123!

Adam Bean One Orange Chip Points: 26631 More actions · Answer 13

Lowell (1/30/2014)
Adam i think you just want to do wrap your query as a cte/subselect to get distinct logins with weak passwords, since your new logic allows multiple matches.
if '0001' and '1000' are both rows in yout PasswordCheckerList, then that's two rows that are going to match..once for regular, and once for a reverse.

Lowell, yeah I was thinking along the same lines and already tried that. There is another time where a duplicate can occur - when it fails two tests (in the known list + same as user), so I had to pull that out with a distinct. The distinct can't work on this scenario because it is two seperate passwords with the reverse.

This is my current running query:

WITH [CTE_PasswordResults]

AS

(

SELECT

sl.[name]AS [Login]

,REPLACE(pc.[Password],'_Replace_',sl.[name])AS [Password]

,sl.[create_date]

,sl.[modify_date]

FROM sys.[sql_logins] sl

LEFT JOIN dbo.[PasswordCheckerList] pc

ON PWDCOMPARE(pc.[Password], sl.[password_hash]) = 1-- Was the password stored in the list?

OR PWDCOMPARE(REVERSE(pc.[Password]), sl.[password_hash]) = 1-- How about known passwords reversed?

OR PWDCOMPARE(REPLACE(pc.[Password],'_Replace_',sl.name), sl.[password_hash]) = 1-- Is the password the same as the user name?

LEFT JOIN [dbo].[Split_fn](@LoginName,',') ln

ON sl.[name] = ln.[item] COLLATE DATABASE_DEFAULT

LEFT JOIN [dbo].[Split_fn](@LoginNameExclude,',') lne

ON sl.[name] = lne.[item] COLLATE DATABASE_DEFAULT

WHERE sl.[type] = 'S'-- Only check SQL Logins

AND ((ln.[item] IS NOT NULL AND @LoginName IS NOT NULL) OR @LoginName IS NULL)-- Specified login(s), or all

AND lne.[item] IS NULL-- All but excluded login(s)

)

SELECT

*

FROM

(

SELECT DISTINCT -- DISTINCT to avoid duplicates (when a password is stored in the list and it's the same as a login)

[Login]

,CASE

WHEN [Password] IS NULL THEN 'PASS'

ELSE 'FAIL'

END AS [Result]

,CASE

WHEN [Password] = [Login] THEN 'Same as Login'

WHEN [Password] = '' THEN 'Blank'

WHEN [Password] IS NOT NULL THEN 'Bad Password'

ELSE NULL

END AS [Reason]

,[Password]

FROM [CTE_PasswordResults]

) t

WHERE ([Result] = @Results OR @Results IS NULL)

Adam Bean One Orange Chip Points: 26631 More actions · Answer 14

Nadrek (1/30/2014)
Separately, the phpbb password list is an excellent, small starter list - add in words related to your company, your software products, names from your employee list, etc. Just dump every SQL Server account name into your weak password list for simplicity :).
Don't forget to add 0-999 to the end of them; those are very common passwords, perhaps a little 1337 speak, etc. to go along with your REVERSE.
Make sure you can catch P@ssw0rd123! and P@$$w0rd123!

Hey Nadrek, I pulled the list from here that I'm working off of: https://xato.net/passwords/more-top-worst-passwords/#.UuqD-_ldV8E

You bring up some good points though that make me now want to expand this list. I was hoping to just find compilations of all known/bad passwords out there and continue to build out this table.