Hello people,
Good night!
In this post, I will demonstrate how to identify fragile passwords, empty or equal to the username in SQL Server. This is especially useful for database administrators to avoid attacks due to carelessness of users in choosing their passwords.
Introduction
To perform this check, we will use the PWDCOMPARE function, which has been present in SQL Server since 2008, but which is unfortunately marked deprecated and will be discontinued in some future version of SQL Server (although I have not identified any other alternative). This function is quite simple, where you enter the password you would like to test, the comparison hash, and the function returns a boolean value (0 or 1) if the password you tried matches the hash you entered.
Although this function is public, that is, any user of the instance can use it, it does not pose a threat to the security of the database, since the password_hash column of the sys.sql_logins view is only visible to users with CONTROL SERVER privilege in the instance, which I imagine very few users, all of them, DBA's. Otherwise, it would be very easy for a malicious user to perform brute force attacks to try to guess passwords, but this is not possible due to this permission restriction (although this is possible for a DBA user).
Another important point to make is that this technique only applies to users with SQL Server authentication. Active Directory Authentication (Windows AD) users have the password_hash column value NULL, even for sysadmin users.
Identifying the user password hash (password_hash)
An important step in identifying these fragile passwords is to figure out the hash of the user's password. We will need this hash to use in the PWDCOMPARE function and identify the user's current password.
To retrieve this user hash is quite simple, just use one of the 2 queries below:
| 1 2 3 4 5 6 7 8 9 | SELECT password_hash FROM sys.sql_logins WHERE [name] = 'Usuario' SELECT LoginProperty('Usuario', 'PasswordHash') |
Identifying Fragile Passwords
Now that we've identified how to recover password_hash, let's look at how fragile our users' passwords are in the bank. For this, I will create a table of weak passwords that I will try, and then I will test each password on each user to find which password has matched.
Password Table Creation Script:
With the script below, I will create a table with the passwords that I will use to try to identify the user's current password. Feel free to change this script and add your password attempts.
In this script you can easily create a list of all possibilities and make a brute force attack if you are a user with DBA privilege, have lost a user's password and really need to find out a certain password (because it's much easier simply change it, if applicable).
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | IF (OBJECT_ID('tempdb..#Senhas') IS NOT NULL) DROP TABLE #Senhas CREATE TABLE #Senhas ( Senha VARCHAR(100) ) -- Inserindo senhas mais comuns (pesquisei no google) INSERT INTO #Senhas VALUES ('teste'), ('TESTE'), ('password'), ('qwerty'), ('football'), ('baseball'), ('welcome'), ('abc123'), ('1qaz2wsx'), ('dragon'), ('master'), ('monkey'), ('letmein'), ('login'), ('princess'), ('qwertyuiop'), ('solo'), ('passw0rd'), ('starwars'), ('teste123'), ('TESTE123'), ('deuseamor'), ('jesuscristo'), ('iloveyou'), ('MARCELO'), ('jc2512'), ('maria'), ('jose'), ('batman'), ('123123'), ('123123123'), ('FaMiLia'), (''), (' '), ('sexy'), ('abel123'), ('freedom'), ('whatever'), ('qazwsx'), ('trustno1'), ('sucesso'), ('1q2w3e4r'), ('1qaz2wsx'), ('1qazxsw2'), ('zaq12wsx'), ('! qaz2wsx'), ('!qaz2wsx'), ('123mudar'), ('gabriel'), ('102030'), ('010203'), ('101010'), ('131313'), ('vitoria'), ('flamengo'), ('felipe'), ('brasil'), ('felicidade'), ('mariana'), ('101010') -- Números repetidos DECLARE @Contador INT = 1, @Total INT = 10, @Contador2 INT = 1, @Total2 INT = 10 WHILE(@Contador < @Total) BEGIN WHILE(@Contador2 < @Total2) BEGIN INSERT INTO #Senhas SELECT REPLICATE(CAST(@Contador AS VARCHAR(100)), @Contador2) SET @Contador2 += 1 END SET @Contador += 1 SET @Contador2 = 1 END SET @Contador = 12 SET @Contador2 = 1 -- Letras repetidos WHILE(@Contador <= 126) BEGIN WHILE(@Contador2 <= @Total2) BEGIN INSERT INTO #Senhas SELECT REPLICATE(CHAR(@Contador), @Contador2) SET @Contador2 += 1 END SET @Contador += 1 SET @Contador2 = 1 END -- Sequências DECLARE @Atual VARCHAR(100) = '' SET @Contador = 0 WHILE(@Contador <= @Total) BEGIN SET @Atual = @Atual + CAST((CASE WHEN @Contador = 10 THEN 0 ELSE @Contador END) AS VARCHAR(100)) INSERT INTO #Senhas SELECT @Atual SET @Contador = @Contador + 1 END SET @Contador = 1 SET @Atual = '' WHILE(@Contador <= @Total) BEGIN SET @Atual = @Atual + CAST((CASE WHEN @Contador = 10 THEN 0 ELSE @Contador END) AS VARCHAR(100)) INSERT INTO #Senhas SELECT @Atual SET @Contador = @Contador + 1 END -- Logins INSERT INTO #Senhas SELECT [name] FROM sys.sql_logins INSERT INTO #Senhas SELECT LOWER([name]) FROM sys.sql_logins INSERT INTO #Senhas SELECT UPPER([name]) FROM sys.sql_logins INSERT INTO #Senhas SELECT DISTINCT REVERSE(Senha) FROM #Senhas |
And now, we try to identify the fragile passwords in the instance:
| 1 2 3 4 5 6 7 8 | SELECT A.[name], B.Senha FROM sys.sql_logins A CROSS APPLY #Senhas B WHERE PWDCOMPARE(B.Senha, A.password_hash) = 1 |
In the report above, all users where the password could be identified are shown, with their respective passwords found.
Ideally, in the next password settings, you activate the option “Enforce password policy” (https://msdn.microsoft.com/en-us/library/ms161959.aspx) to ensure your passwords are strong and secure.
I hope you enjoyed the post and even more.
Slutty felipe being a weak password xD
Excellent post Dirceu !!!
Kkkkkkkkkk Sorry