Recently one of my blog reader emailed me below question.
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:
WHERE Pwdcompare(NAME, password_hash) = 1
'<blank>' AS 'password'
WHERE Pwdcompare('', password_hash) = 1
'password123' AS 'password'
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:
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)