How to know if db accounts have blank passwords?

Question

Post reply

How to know if db accounts have blank passwords?

jporgy

Old Hand

Points: 344
More actions
September 21, 2012 at 9:47 am

#282027

Hi my auditor is asking me if there is a way to know if any of the user accounts granted access to our production SQL database are using a blank or weak password. I told him sa is disabled by default and it cannot accept blank password by default either. But I'm not sure if users can change their password to blank.
Also, what are the generic usernames and generic passwords in SQL 2008 by default? scott/tiger stuff. Like sa and probe with known default passwords in 2000. But is there in 2008?
Are there any other basic SQL security activities or evidence I should pile for auditors to go away? Or for me to be secure in basic terms?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

This will help you with blank sa passwords and can easily be extended to check all logins for a blank password.

Using PowerShell to Audit for Blank SA Passwords By K. Brian Kelley[/url]

As for common weak passwords you could look into getting a dictionary of common passwords and hooking your PowerShell script to that to check each one against each login. A better option would be to 'Enforce password policy' for all logins as well as 'must change password at next login' for all interactive user logins, then go through and reset passeords for all non-interactive logins while resetting the services that use them.

Note there is no way to audit password complexity from only the password hash.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323547 More actions · Answer 2

i have this saved in my snippets; it's checking a short dictionary of weak passwords, and also where the password is the users name.

you might want to expand the dictionary;

for example if you worked at IBM you might add "ibmadmin" to the list.

DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))

INSERT INTO @WeakPwdList(WeakPwd)

SELECT ''

UNION ALL SELECT 'asdf'

UNION ALL SELECT 'asdasd'

UNION ALL SELECT '123'

UNION ALL SELECT '1234'

UNION ALL SELECT '12345'

UNION ALL SELECT 'abc'

UNION ALL SELECT 'default'

UNION ALL SELECT 'guest'

UNION ALL SELECT '123456'

UNION ALL SELECT '@@Name123'

UNION ALL SELECT '@@Name'

UNION ALL SELECT '@@Name@@Name'

UNION ALL SELECT 'admin'

UNION ALL SELECT 'Administrator'

UNION ALL SELECT 'admin123'

UNION ALL SELECT 'asdf'

UNION ALL SELECT 'asdfasdf'

UNION ALL SELECT 'sa'

UNION ALL SELECT 'biteme'

UNION ALL SELECT 'passw'

UNION ALL SELECT 'sasa'

UNION ALL SELECT 'password'

UNION ALL SELECT 'pass'

SELECT t1.*, REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]

FROM sys.sql_logins t1

INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1

OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

WHERE t1.is_policy_checked = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jporgy Old Hand Points: 344 More actions · Answer 3

Thanks,

I just read online the following:

With SQL Server 2008, password policy enforcement is built into the server. Using the NetValidatePasswordPolicy() API, which is part of the NetAPI32 library on Windows Server 2003, SQL Server validates a password during authentication and during password set and reset, in accordance with Windows policies for password strength, expiration, and account lockout.

If you are not running Windows Server 2003 or above, SQL Server still enforces password strength by using simple checks, preventing passwords that are:

•Null or empty

•The same as the name of computer or login

•Any of "password", "admin", "administrator", "sa", "sysadmin"

The same complexity standard is applied to all passwords you create and use in SQL Server, including passwords for the sa login, application roles, database master keys for encryption, and symmetric encryption keys.

So Give this, is it fair to say that the auditors simply need to look at the OS level security policy because it should take effect on the SQL account password rules?

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

jporgy (9/21/2012)
So Give this, is it fair to say that the auditors simply need to look at the OS level security policy because it should take effect on the SQL account password rules?

Only if you had enabled it. That's what this is saying:

opc.three (9/21/2012)
A better option would be to 'Enforce password policy' for all logins as well as 'must change password at next login' for all interactive user logins, then go through and reset passeords for all non-interactive logins while resetting the services that use them.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323547 More actions · Answer 5

for domain users, that's all true, but for SQLLogins, unless you use the options, or set up a policy to enforce the options, you can still create users with weak passwords;

--works in SQL2012 Dev:

create LOGIN ClarkKent WITh Password='ClarkKent'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jporgy Old Hand Points: 344 More actions · Answer 6

Will PWDCOMPARE help then?

A. logins that have no passwords

1SELECT name FROM sys.sql_logins

2WHERE PWDCOMPARE('', password_hash) = 1 ;

B. for passwords which are same as login names

1SELECT name FROM sys.sql_logins

2WHERE PWDCOMPARE(name, password_hash) = 1 ;

C for "password" passwords

1SELECT name FROM sys.sql_logins

2WHERE PWDCOMPARE('password', password_hash) = 1 ;

jporgy Old Hand Points: 344 More actions · Answer 7

Will this also work in 2008?

USE Master

GO

SELECT [name], sid, create_date, modify_date

FROM sys.sql_logins

WHERE [name] = 'sa'

GO

Another way to know whether change password setting was enforced or turned off. Right?