SQL Login Password Audit

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

  • 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

  • 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

  • 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)

  • Here is where I found my list of common passwords.  
    Most security sites will post lists of most common passwords as well.

  • 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.

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

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

  • 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!


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

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