So after yesterday’s post, “Password encryption in SQL Server, how to tell if a user is using a weak password“, I was curious so I prodded a bit further. I wanted to know just how secure (or not) passwords in SQL Server are. Now remember that you have to have access to the sysxlogins to be able to do this, so the very first rule is: NO ONE should have access to sysxlogins! The second one is: Protect your administrator id’s and passwords! The third rule is: Consider migrating to integrated authentication if you’re serious about security, that way the passwords aren’t stored in SQL Server in the first place.
If you’ve not heard of it, there are quite a few sites that list the ‘top worst passwords to use‘. Using a list like this you can easily populate a SQL server database, if you really want to get all the bad passwords add all the words in the dictionary as well (though it will significantly slow down the query you’ll run later).
Your table should be one column, call it ‘password’. Once you have this table you can run this sql: (change the bold text to match your table name)
select l.name, bp.password from master.dbo.sysxlogins as l, master.dbo.badPasswords as bp where pwdcompare(bp.password,l.password)=1
Depending on how many passwords there are in your password database will depend on how long this will take to run. Also it could be more efficient. For example this query is running pwdcompare for every column in the password database, even if it’s already found a match. You could have it exit as soon as a match is found to make it much faster. In any case it will likely give you a scary list showing you just how many people are using ‘bad passwords’.
For completeness you can check how many people have passwords the same as their id by running this:
select l.name from master.dbo.sysxlogins as l where pwdcompare(l.name,l.password)=1 or pwdcompare(lower(l.name),l.password)=1
That one doesn’t require a table be set up, you can run it right now!
So now that I have you sufficiently paranoid, I hope you consider migrating to integrated authentication!
This query is much faster for determining the bad passwords (note: it only saves time for users that are using a bad password, users that can’t be found still have to be checked vs every pwd in the table). On my machine the query above takes 28 seconds, this new one takes 17.
select name, pwd from ( select sysx.name, ( select top 1 bp.password from master.dbo.badPasswords as bp where pwdcompare(bp.password,sysx.password)=1 ) as pwd from master.dbo.sysxlogins as sysx ) as results where pwd is not null