SQL logins with blank passwords or policy turned off

What this check looks for

Enabled SQL logins in sys.sql_logins that have a password hash, excluding the internal ## certificate-based principals, where any of the following is true:

• The password is blank, tested with PWDCOMPARE('', password_hash).
• The password is the login name, tested with PWDCOMPARE(name, password_hash).
• is_policy_checked is off, meaning CHECK_POLICY was disabled for that login.

PWDCOMPARE is the supported way to test a candidate password against a stored hash without knowing the password. It is doing here exactly what an attacker’s first two guesses would do.

Why it matters

A blank password, or a password that is the same as the login name, is the first and second thing tried by every automated attack against SQL Server. Not the tenth. The first two. Any scanner that finds port 1433 open tries them within seconds.

CHECK_POLICY disabled is the quieter of the three and often the worse one. With it off:

• The password never had to meet the Windows complexity rules, so it can be anything.
• The password never expires. A credential set during an installation in 2015 is still valid.
• The account does not lock out after repeated failed attempts, so an attacker can keep guessing indefinitely with no rate limit and, if login auditing is also off, no record.

That last combination is the one to worry about. Policy off plus auditing off is an account that can be attacked forever, silently.

These logins are usually created by vendor installation scripts rather than by people, which is why nobody remembers them and why they survive every security review. The script needed a SQL login, disabled the policy because the installer could not handle an expiry prompt, and moved on.

How to confirm it yourself

SELECT l.[name],
       l.[is_disabled],
       l.[is_policy_checked],
       l.[is_expiration_checked],
       l.[create_date],
       l.[modify_date],
       CASE WHEN PWDCOMPARE('', l.[password_hash]) = 1 THEN 'blank password'
            WHEN PWDCOMPARE(l.[name], l.[password_hash]) = 1 THEN 'password matches login name'
            WHEN l.[is_policy_checked] = 0 THEN 'CHECK_POLICY is off'
            ELSE '' END AS [finding]
  FROM sys.sql_logins AS l WITH (NOLOCK)
 WHERE l.[is_disabled] = 0
   AND l.[password_hash] IS NOT NULL
   AND l.[name] NOT LIKE '##%'
   AND (    PWDCOMPARE('', l.[password_hash]) = 1
         OR PWDCOMPARE(l.[name], l.[password_hash]) = 1
         OR l.[is_policy_checked] = 0 )
 ORDER BY l.[name];

Before changing anything, find out what each one can reach:

SELECT sp.[name] AS [login_name],
       r.[name]  AS [server_role]
  FROM sys.server_principals AS sp WITH (NOLOCK)
  LEFT JOIN sys.server_role_members AS srm WITH (NOLOCK)
         ON srm.[member_principal_id] = sp.[principal_id]
  LEFT JOIN sys.server_principals AS r WITH (NOLOCK)
         ON r.[principal_id] = srm.[role_principal_id]
 WHERE sp.[type] = 'S'
 ORDER BY sp.[name];

A weak login that is also a sysadmin is the one to deal with first.

How to fix it

Do not start by changing passwords. A login created by a vendor installer has its password in a configuration file somewhere, and changing it without finding that file takes the application down.

In order:

1. Work out whether the login is used at all. Check sys.dm_exec_sessions over a few days, or the failed and successful login records if auditing is on. Many of these are left over from software that was uninstalled years ago.
2. Disable the unused ones rather than dropping them, so the change is reversible: ALTER LOGIN [name] DISABLE;
3. For the ones in use, find the application configuration first, then change both together:

ALTER LOGIN [TheLogin]
  WITH PASSWORD = 'a genuinely strong password here',
       CHECK_POLICY = ON;

4. Turn CHECK_POLICY on even where you cannot change the password immediately. It enables lockout and complexity from that point forward. Note that switching it on also turns on expiry checking unless you say otherwise, so be explicit:

ALTER LOGIN [TheLogin] WITH CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

Setting CHECK_POLICY = ON does not retroactively validate the existing password. Change the password too, or the weak one stays.

5. Reduce what the account can do. A vendor login that is sysadmin because the installer asked for it is worth revisiting regardless of its password.

How long it takes

About an hour and a half, most of which is establishing what uses each login before touching it.

Related reports

Related checks

Frequently asked questions

Does this check learn our passwords? No. PWDCOMPARE tests one candidate value against the stored hash and returns 1 or 0. The only candidates tried are the empty string and the login name.

Why are disabled logins not reported? A disabled login cannot authenticate, so a weak password on it is not currently a way in. It is still worth cleaning up, and it will be reported the moment somebody enables it.

We cannot enable CHECK_POLICY, the vendor forbids it. Then the compensating control is network level: restrict which hosts can reach the instance, and make sure failed login auditing is on so the attempts are at least recorded.

What about the ## logins? Those are internal principals backed by certificates rather than passwords. They are excluded deliberately.