SQL SERVER – How to Find Weak Passwords Using T-SQL?

Recently one of my blog reader emailed me below question.

Hi Pinal,
Need your urgent help. In recent past, we have been attacked by the hacker who was able to get in to our SQL Server via sysadmin account and made big damage to our data. To make sure it doesn’t happen in future, I have taken task to find out SQL Server password which are weak.

Do you have any suggestions for me?

This is one of the area which is always haunting all SQL DBAs. There are recommendations to use Windows Authentication to connect to SQL Server and that would save from all such problem. But it is not always feasible to use Windows Authentication. Now, if we decided to choose SQL Authentication, there is a setting which is “Enforce Password Policy” which would ensure that you are choosing a strong password.

If recommendations are not followed, you might end up in situation where SQL Logins have weak and basic passwords. SQL Server has provided a function PWDCOMPARE which can become very useful to find known password. Below are few example use of this out of box funtion:

SELECT NAME,
NAME 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare(NAME, password_hash) = 1
UNION
SELECT
NAME,
'<blank>' AS 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare('', password_hash) = 1
UNION
SELECT
NAME,
'password123' AS 'password'
FROM   sys.sql_logins
WHERE  Pwdcompare('password123', password_hash) = 1

In above query, we are trying to find:

  • Password same as user name – first query
  • Blank password – second query
  • Password = password123 – third query

These are one of the most common password used in the industry. I am sure you can extend this further by modifying it and adding more weak passwords.

Here is the sample output for the above:

SQL SERVER – How to Find Weak Passwords Using T-SQL? weak-password-01

Hope this blog would help you in finding weak passwords and make it more complex. Have you ever had a need to use such passwords in your environments? Do let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
Interview Question of the Week #003 – How to Write Script for Database Cursor?
Next Post
SQL SERVER – How DBAs Have to be Proactive in Identifying and Fixing Problems Before They Happen

Related Posts

1 Comment. Leave new

  • Word List Cracking :
    ————————-

    Create table PasswordList (WordList nvarchar(max))
    insert into PasswordList (WordList) values (‘Rama’), (‘Koti’), (‘RamaKoti’), (‘Ramkoti’), (‘ramkoti’)

    select name, WordList as ‘password’
    from sys.sql_logins, PasswordList
    Where PWDCOMPARE(WordList,password_hash) = 1

    Reply

Leave a Reply

Menu