Steve Jones - SSC Editor (7/19/2013)
djackson 22568 (7/18/2013)
Unmentioned in the article is an issue the industry needs to address, which is that a lot of vendors develop software that requires a specific password in order to function. Sometimes these are database accounts and passwords, sometimes they are internal application accounts, but I have seen vendors request network accounts that must have a particular name and password.Thanks for the mention here, Dave. This is a huge problem, and one I worry about. I always try System and Manager on Oracle instances, just to see. Those defaults are bad, but the back doors or "support" logins are horrible.
For identifying weak SQL Server accounts, I use the following.
-- There are several frequently used password lists posted on the web.
-- Here are a few, but perhaps 100 or more could be inserted here.
declare @PW table (pwtext varchar(180) not null primary key);
insert into @PW (pwtext)
values ('password'), ('123456'), ('12345678'), ('1234'), ('qwerty'), ('12345');
select name, type_desc, create_date, modify_date, password_hash
from sys.sql_logins l
join @PW pw on pwdcompare(pw.pwtext, l.password_hash) = 1;
-- Query accounts with empty password:
select name, type_desc, create_date, modify_date, password_hash
from sys.sql_logins
where pwdcompare('', password_hash) = 1;
-- Query accounts where password = account name:
select name, type_desc, create_date, modify_date, password_hash
from sys.sql_logins
where pwdcompare(name, password_hash) = 1;
As for 3rd party service accounts, we often times have to live with the fact that it has to exist, but we can still control what role membership and permissions it has. They may reccomend sysadmin, but you can grant them dbo membership on the application database, sqlagent, and perhaps view server state as needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho