Modify SQL Server password policy

Question

In a recent audit exercise, we were asked to enforce a password policy where all passwords must:

• have 25 chars
• have capital and small letters, numbers and special characters

This is much stricter than the default policy in MS SQL Server. I couldn't find any articles that talks about modifying the policy so I assume this has to be enforced manually.

Is there anyway where I could run a query to output all the accounts (Windows/SQL logins) where password is non-compliant?

If possible, I would also love to construct a query where I can perform rotation on them.

Answer 1

I doubt that is possible since SQL Server stores a hash of the password. I.e., nobody can get the password in clear-text not even SQL Server itself (without doing brute-force attempts).

I.e., the password in not stored in an encrypted manner so that it would have been possible to decrypt it (which would be required in order for your check to validate compliance).

Btw, the policy for the SQL-passwords are based on the domain where the SQL Server is (i.e., Windows).

I was hoping that a DDL trigger on CREATE and ALTER LOGIN could do it (i.e., you can do ROLLBACK if you don't like that password), but unfortunately the EVENTDATA() function doesn't expose the password. As seen by below repro:

DROP TRIGGER IF EXISTS CREATE_LOGIN 
ON ALL SERVER 
GO

CREATE TRIGGER CREATE_LOGIN 
ON ALL SERVER 
FOR CREATE_LOGIN, ALTER_LOGIN
AS
SELECT EVENTDATA()
GO

--Investigate the XML from EVENTDATA()
CREATE LOGIN myNewLogin WITH PASSWORD = 'myGloriousPassword'
GO

--Cleanup
DROP LOGIN myNewLogin
GO
DROP TRIGGER IF EXISTS CREATE_LOGIN 
ON ALL SERVER 
GO

Answer 2

By default, when you create an active directory user (or a group), you are implicitly using Windows password policy mechanisms, which is good.

On the other hand, when you create a SQL user on your instance, you have the choice of enabling the policy password or not.

By using the CIS SQL Server Benchmark, it gives us an interesting query to help check if password complexity for SQL logins is enabled.

Use master;
go
SELECT name, is_disabled 
FROM sys.sql_logins WHERE is_policy_checked = 0;

If the query returns a result, you must consider enabling the "enforce password policy" for that user in order to be compliant.

Answer 3

In a recent audit exercise, we were asked to enforce a password policy

Fair enough as far as it goes but the main problem with SQL Server Authentication is the password being sent from the Client to the Server. This is done by scambling, not encrypting the password, and then using TLS to encrypt the connection to the server. Without a CA certificate the TLS connection will be made with a self-signed certificate created when SQL Server starts. This means the self-signed certificate will need to be trusted so is subject to a man-in-the-middle (MITM) attack which can then obtain the password as it is not encrypted.

ie For SQL Server Authentication to be reasonably secure:

1. A CA Certificate should be used on the Server.
2. Only TLS 1.2 and TLS 1.3 should be enabled as anything lower has its own security problems.