4

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

1
  • Turning off password policy enforcement is not allowed in Azure SQL Database. If you try you'll get Msg 40517, Level 16, State 1, Line 9 Keyword or statement option 'check_policy' is not supported in this version of SQL Server. – David Browne - Microsoft Nov 7 '20 at 15:37
3

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.

2
  • One more thing I could add is that security is not composed of one measure (like turn on password policy to grant a long password and you're safe), it's rather a bunch of actions that together compose a secure environment. So, even if you can't make use of password policy right now, you could grant that any attempt to brute force any password will be detected. Check how to detect attacks with XE. Notice the Error 18456 mention. – Ronaldo Nov 6 '20 at 0:29
  • 1
    Shekar Kola's answer provided another measure that could help improve the security and that's another thing you could check while you're figuring out how to turn on password policy in your environment without breaking any application. – Ronaldo Nov 6 '20 at 0:35
4

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;
2
  • 1
    the list is a bit on the short site maybe this could help weakpass.com/wordlist – nbk Nov 5 '20 at 10:44
  • Hi - that´s a useful script but it doesn´t really answer the problem I have been set by corporate security - they want to know if any of the passwords are shorter than 12 characters (not necessarily 12 mind) – Stephen Morris - Mo64 Nov 5 '20 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.