October 8, 2014 at 6:11 am
Looking for a good way to track down SQL logins with weak passwords - that don’t meet sql password policy (complexity) requirements. SQL versions 2005-2014.
Any ideas would be appreciated. Thanks!
October 8, 2014 at 6:16 am
I'm not sure that you will be able to script this out (as you cannot see what users' passwords are).
I would ensure that users are specifying complex passwords by ticking the 'Enforce Password Policy' box.
October 8, 2014 at 6:17 am
DBA From The Cold (10/8/2014)
I'm not sure that you will be able to script this out (as you cannot see what users' passwords are).I would ensure that users are specifying complex passwords by ticking the 'Enforce Password Policy' box.
+1
This is only for new passwords.
It would be a huge security risk if you could somehow check existing passwords.
(and a reason for me to immediately terminate business with your company, sort of speak)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 8, 2014 at 6:30 am
Koen Verbeeck (10/8/2014)
DBA From The Cold (10/8/2014)
I'm not sure that you will be able to script this out (as you cannot see what users' passwords are).I would ensure that users are specifying complex passwords by ticking the 'Enforce Password Policy' box.
+1
This is only for new passwords.
It would be a huge security risk if you could somehow check existing passwords.
(and a reason for me to immediately terminate business with your company, sort of speak)
I'm not looking to view passwords, but instead query for passwords that don't meet the requirements so that I can get them corrected. I'm working with thousands of old logins before 'enforce password policy' was enabled.
FYI - using the pwdcompare function and a password file/table, you can pinpoint (view) weak and/or commonly used passwords. That's just one way.
October 8, 2014 at 6:31 am
SQL stores passwords hashed, so you can't reverse that and get the password back.
You can check the 'enforce policy' for logins and new passwords will have to match the domain policy, but for existing passwords, short of brute-force cracking those passwords, isn't possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 6:37 am
SkyBox (10/8/2014)
I'm not looking to view passwords, but instead query for passwords that don't meet the requirements so that I can get them corrected. I'm working with thousands of old logins before 'enforce password policy' was enabled.FYI - using the pwdcompare function and a password file/table, you can pinpoint (view) weak and/or commonly used passwords. That's just one way.
True. Using the hashes and common known weak passwords you can track those down. But this probably won't cover 100% all of the weak passwords unfortunately. Depends how big your dictionary is.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 8, 2014 at 6:47 am
Thanks for all the info - had to get some thoughts on this before I gave up. What about running a trace on setting up a new user with enforce password policy enabled? Wonder if it would pick up the logic for meeting the password requirements.
My only other option is to change all passwords, with pwd policy enabled. Difficult with several 3rd party applications.
October 8, 2014 at 6:57 am
SkyBox (10/8/2014)
What about running a trace on setting up a new user with enforce password policy enabled? Wonder if it would pick up the logic for meeting the password requirements.
Your trace would include a lot of events (recording the CREATE LOGIN statement) with text some variation of 'text omitted for security reasons'
Passwords are stored using an irreversible cryptographic hash. That doesn't mean you can't reverse the hash. It means the hash can't be reversed at all, by anyone or anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 7:51 am
Regarding enforcing password policy, is it possible to enable it (for checking password complexity) but disable for the following?
CHECK_EXPIRATION
password history initialization
Account lockout duration, account lockout threshold, and reset account lockout counter
October 8, 2014 at 8:10 am
you can run something like mssqlcrack (http://sourceforge.net/projects/mssqlcracker/) and if you get things like "password", "12345", you can change those pwds and/or contact users to ask for better ones.
October 8, 2014 at 8:21 am
Just be careful of any possible legal implications (what happens if someone uses the same password for your system and their online banking, you cracked their password as part of this exercise and at some later point their account gets cleaned out?)
Make sure, before you go and run hacking tools against the database, that you have full permission from management and IT security to do so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 9:17 am
GilaMonster (10/8/2014)
Just be careful of any possible legal implications (what happens if someone uses the same password for your system and their online banking, you cracked their password as part of this exercise and at some later point their account gets cleaned out?)Make sure, before you go and run hacking tools against the database, that you have full permission from management and IT security to do so.
Very good point. I did get support from IT Management and Security and will be working with the application folks to resolve, but I don't know how far I should take it since there doesn't seem to be a good way to identify them all.
Maybe I should correct the highly vulnerable passwords, enable pwd policy, and move on.
October 8, 2014 at 9:35 am
Well technically you could find all the user logins that don't have strong passwords enforced on them, enable that for all the logins then force them to change their password on their next login.
Your users should be relatively used to password expiring if that's also part of your policy for AD passwords and what not but make sure people are aware it's going to happen.
October 8, 2014 at 1:54 pm
ZZartin (10/8/2014)
Well technically you could find all the user logins that don't have strong passwords enforced on them, enable that for all the logins then force them to change their password on their next login.Your users should be relatively used to password expiring if that's also part of your policy for AD passwords and what not but make sure people are aware it's going to happen.
That's a great idea for users - What makes this tricky is the fact that the majority of the logins are for applications rather than users, so every change has to be coordinated and tested. Users logins are manageable.
AD auth is much better....
October 8, 2014 at 3:35 pm
Identify the SQL passwords that are not enforced by policies
SELECT NAME
,is_disabled
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY NAME;
All the logins listed in the output might have a weak password.
Enforce policy to those logins
ALTER LOGIN MyLogin
WITH CHECK_POLICY = ON
,CHECK_EXPIRATION = ON;
http://sqlism.blogspot.com/2014/10/protect-sql-server-from-brute-force.html
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply