Query to retrieve SQL Server login password length

Question

I've been asked by corporate security to review our Azure SQL Databases and find out if there are any SQL Logins which have "short" passwords. I am not aware of a way to achieve this - I know I could alter the login and force them to use the windows password policy - but wouldn't this have the side effect of disabling the logins and making our applications fail ?

Does anyone have a suggestion - TSQL or Powershell preferably

Answer 1

Unfortunately (or fortunately), I believe there's no such query and the reason is there's no information about the password stored other than the hash of the password. If you query sys.sql_logins you'll see the column password_hash and, according to the doc, it contains the

Hash of SQL login password. Beginning with SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

Therefore, to know the size of the password you would have to decrypt those hash to obtain the original password and if it was possible, it would be a security issue by itself.

I know I could alter the login and force them to use the windows password policy - but wouldn't this have the side effect of disabling the logins and making our applications fail?

The Policy Enforcement doc can help you better understand the behavior of that change.

Answer 2

I think, your corporate security meant by week passwords. One way of doing that (without any third-party tools) with help of in-built function PWDCOMPARE, but for this you need to have real week-passwords list available perhaps in database table from where every password looked-up and compared with SQL Login hashes one-by-one. following is the example:

Declare @pwText nvarchar(128);
Declare @WeekPassword_List table (pw nvarchar(128));
Declare @WeekPasswords_Active table (login_name sysname, pw nvarchar(128));

insert into @WeekPassword_List
values 
('password'),
('passwordpassword'),
('password123'),
('P@ssw0rd'),
('Pa55word'),
('Pa55w0rd'),
('Temp@password'),
('Temp123'),
('temp123'),
('temp@123'),
('Temp@123'),
('123'),
('1234'),
('123456'),
('12345678'),
('');

while exists (select 1 from @WeekPassword_List)
begin 
    SET @pwText = (select top 1 pw from @WeekPassword_List);

    INSERT INTO @WeekPasswords_Active
    SELECT name, @pwText
    FROM   sys.sql_logins
    WHERE  Pwdcompare(@pwText, password_hash) = 1;

    DELETE FROM @WeekPassword_List where pw = @pwText;
end 

select * from @WeekPasswords_Active;