SQL login passwords that don't meet complexity requirements

• Mike Hinds

  SSCarpal Tunnel

  Points: 4258

  More actions

  October 9, 2014 at 6:48 am

  #1751266

  I don't work for Idera. That said, three or four times a year I run the Idera Password Checker (part of SQL Admin Toolset) against all my SQL servers (individual server or various groupings). Most useful is the simple check that (1) password is not blank, (2) password is not equal to the login name. It also checks, selectable, against (1) top 10 common passwords, (2) top 50 common, (3) top 800, and (4) top 2400 common passwords. You may also type a password to test for (like the SA password I have in my install .INI, have I changed it yet?). If that's not enough, you can add a textfile of dictionary words - I use one I found somewhere called "dict.txt" that has around 30,000 words. Very flexible and accurate, and as quick and/or thorough as needed at the time.

  Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

• SkyBox

  SSCarpal Tunnel

  Points: 4288

  More actions

  October 9, 2014 at 9:19 am

  #1751351

  Mike Hinds (10/9/2014)

  ---

  I don't work for Idera. That said, three or four times a year I run the Idera Password Checker (part of SQL Admin Toolset) against all my SQL servers (individual server or various groupings). Most useful is the simple check that (1) password is not blank, (2) password is not equal to the login name. It also checks, selectable, against (1) top 10 common passwords, (2) top 50 common, (3) top 800, and (4) top 2400 common passwords. You may also type a password to test for (like the SA password I have in my install .INI, have I changed it yet?). If that's not enough, you can add a textfile of dictionary words - I use one I found somewhere called "dict.txt" that has around 30,000 words. Very flexible and accurate, and as quick and/or thorough as needed at the time.

  That's exactly what I'm doing via script - fairly simple. Too bad that it will only catch a small percentage of noncomplex passwords in my environment. Appreciate all the feedback.

• Nadrek

  SSC-Insane

  Points: 20039

  More actions

  October 9, 2014 at 10:02 am

  #1751369

  Ok, my post just got eaten, and I'm out of time, so here's the short short short version:

  First, is_policy_checked is meaningless except during the actual password change operation:

  select name, is_policy_checked, is_expiration_checked from sys.sql_logins WHERE name = 'BADPASSWORD'

  CREATE LOGIN [BADPASSWORD] WITH PASSWORD=N'885156813628709984402815015035931220186779279307997290920662644571022333688334453021757399361889039671598119775301307279966458', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

  select name, is_policy_checked, is_expiration_checked from sys.sql_logins WHERE name = 'BADPASSWORD'

  ALTER LOGIN [BADPASSWORD] WITH CHECK_POLICY=ON

  select name, is_policy_checked, is_expiration_checked from sys.sql_logins WHERE name = 'BADPASSWORD'

  ALTER LOGIN [BADPASSWORD] WITH CHECK_EXPIRATION=ON

  select name, is_policy_checked, is_expiration_checked from sys.sql_logins WHERE name = 'BADPASSWORD'

  DROP LOGIN [BADPASSWORD]

  select name, is_policy_checked, is_expiration_checked from sys.sql_logins WHERE name = 'BADPASSWORD'

  Second, I can think of four basic ways to do try and make sure SQL login passwords meet policy:

  1) Force everyone to change their passwords

  1a) Make sure policy checking is enabled at the time of the change

  2) Have a trusted party collect and test all passwords for all logins, forcing changes for weak ones

  2a) storing them in a safe place - strong encrypted .xlsx (NOT .xls), KeePass, etc.

  3) Run an audit with a real tool like oclHashcat[/url] and a modern video card, to brute force the weakest segments - 8 character passwords with only lower and numbers, upper and symbols, etc.

  4) Run an audit with a very poor tool like the T-SQL in What is the default sa password?/url]