tsql- strong passwords

Question

Hi, Using t-sql, how can i find if all SQL Logins have strong passwords on SQL 2000 and 2005 servers?

Any help, much appreciated.

Regards Manjot

Answer 1

You can't! It's would be a security issue if you could identify weak passwords. You might want to force all users to change their passwords and enforce a password policy.

The cracker:

// pseudocode:
foreach (var username in 
   exec(@"SELECT username FROM hypothetical.fn_get_users_with_weak_passwords()"))
    PerformDictionaryAttack(username);

Not so good... ain't it?

Answer 2

I can't imagine an auditor who knows his/her stuff would ask you to check the strength of passwords in a database, as stated above, you can't query for passwords on a Db server no matter what your rights are. It's more likely they'll ask you what your process is for passwords and password strength.

Logins should be tied to a domain account, and the domain controller should have a profile in place that forces strong passwords, at least in the windows world.

For non users, i.e. web apps, reports etc, a functional account should be created at the domain level for the application, or better yet, for the group using the database.

For audit purposes this is basically how we do it, from an admin point of view it's much easier letting the windows domain controllers do their thing and handle the login side of things.

Answer 3

Yes, it's possible, kind of.

SQL 2005 can link into the group policy password policy. This means you can have SQL login passwords have the same length, expiration etc as your NT logins. Of course, if you have no policy there why are they concerned about SQL logins?

For both versions, you can use NGS Squirrel to brute force the password hashes. If you crack the passwords quickly (day? week? month?) then they're not good enough.

If you want a pure T-SQL solution you could emulate Squirrel by using the pwdencrypt function and a loop to run through all permutations.

Otherwise: no. You can only check a password by cracking it...

As it happens, we use domain service/system accounts all over: we have no SQL logins at all.

Answer 4

I think your best bet is to try a brute force approach using a good dictionary, SQL Ping 3 seems like it would do the job.