SQL Login Password Audit

Question

SQL Login Password Audit

Jim Youmans-439383

SSCommitted

Points: 1657
More actions
April 3, 2017 at 11:18 pm

#336238

Comments posted to this topic are about the item SQL Login Password Audit

Viewing 9 posts - 1 through 8 (of 8 total)

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

Andreas Michael SSC Eights! Points: 936 More actions · Answer 1

Hi, is it possible to have some serious sources on howto create and fill the table CommonPwds?
With Google I'll get a huge set of suspect hacking-sites or Clickbaits.

thanks in advance

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 2

I liked your tracking on how old the password. I was never sure if PWDCOMPARE takes into account the case of letters. In current releases of SQL Server the password value for and Upper and Lower value of the same letter might as well be a different letter. "A is not equal to a".

I have been using a modified version of the script from this SQL Server. http://www.sqlservercentral.com/articles/Security/howtomakesureyouhavegoodpasswords/1299/

I have run password list of 100,000s of password, just to stress test a new server 🙂 Getting these lists required visiting sites that are now blocked at work for me. They rarely uncovered anything new. So I created a shorter list (which I wont share) using information about the company. Like company name, products, branch locations, vendors, applications, and so forth. I have not found any passwords using Microsoft, but don't be surprise some lesser known vendors would use its name in the password when they install it. 🙂

I do like your check for when a password was last changed, I wont be surprise with everything getting tighter for security it will be something I will need to address.

David Bird

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Andreas Michael - Tuesday, April 4, 2017 1:47 AM
Hi, is it possible to have some serious sources on howto create and fill the table CommonPwds?
With Google I'll get a huge set of suspect hacking-sites or Clickbaits.
thanks in advance

The key here is that you really only need a table of Common Passwords (frequently referred to as a "Rainbow Table") if you're a hacker. Having a proper policy and checks in place will prevent the use of most such passwords.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jim Youmans-439383 SSCommitted Points: 1657 More actions · Answer 4

Here is where I found my list of common passwords.
https://github.com/danielmiessler/SecLists/tree/master/Passwords
Most security sites will post lists of most common passwords as well.

Thomas Franz Hall of Fame Points: 3896 More actions · Answer 5

Short remark: a FILLFACTOR = 90 on a Identity Column (= auto increment) in a read-only-table (at least no space-growing varchars) makes no sense and wastes 10 % space. Even the updates in your script changes usually only the most recent record(s) and could be replaced, when you do this weak-password-calculations in the temp table that you use for the MERGE

God is real, unless declared integer.

Andreas Michael SSC Eights! Points: 936 More actions · Answer 6

Jim Youmans-439383 thanks für the link, it looks fine to me.

k_v@telenet.be SSChasing Mays Points: 601 More actions · Answer 7

Hi Jim

While I do understand the issue you solve here, I have some questions about the strategy in this post.

The merge statement updates every login that has changed, deletes every login that was dropped and inserts every login that was created since the previous inventory, right? So, in short, won't the table hold exactly the same data as the temp table?
Doesn't that remove the need for the merge, and by consequence, for the temp table?
Isn't it easier to truncate and insert the whole result set again?

Also, why are there 3 update statements after the insert to set columns that could be set in one go?

Except for the optional check against the dictionary, wouldn't this be enough:

SELECT @@ServerName ServerName

, a.name AS SQL_Login

, b.sysadmin AS IsSysAdmin

, CAST(LOGINPROPERTY(a.[name], 'PasswordLastSetTime') AS DATETIME) AS PwdLastUpdate

, DATEDIFF(day,CAST(LOGINPROPERTY(a.[name], 'PasswordLastSetTime') AS DATETIME),GETDATE())ASPwdDaysOld

, CASE

WHEN PWDCOMPARE('', a.password_hash) = 1 THEN 'BLANK PASSWORD'

WHEN PWDCOMPARE(a.name, a.password_hash) = 1 THEN 'Same As Login'

ELSE 'OK'

ENDAS PasswordEval

FROM sys.sql_logins a

LEFT JOIN MASTER..syslogins b ON a.sid = b.sid

WHERE a.name NOT LIKE '##%';

?
Of course, INSERT INTO is equally possible, I'm just trying to work out the flow of getting the data. I think one insert is preferred over an insert and a merge and 3 updates.
Or am I missing something?

Jim Youmans-439383 SSCommitted Points: 1657 More actions · Answer 8

Yes, you are correct but I wrote it this way to make it more understandable and easy to read. The way I have it may be a bit less efficient but I think it is easier to follow.

Thank you!

Jim