In a previous post we’ve looked at offline methods of cracking SQL Server login passwords as a means of auditing the password strength. But what if, due to security concerns or other limitations, you can’t take the hashes off of a SQL Server instance? This is where online cracking comes in.
About online password cracking
Online password cracking is fairly similar to the previously discussed process, but instead of taking the hashes to another system and do the cracking there, this process uses the original system itself for testing password candidates until a matching one is found.
This has the advantage of not needing to copy login names and password hashes to another system, but it’s limited by the instance’s resources and does not benefit from the added performance that specialized tools like JTR and hashcat bring to the table.
So this is a good fit for smaller password lists – between 200k and 2 million password candidates.
How SQL Server checks passwords
In SQL Server passwords can be checked against existing hashes by using the PWDCOMPARE function.
Building on the T-SQL from the previous post, I’ll add a column that uses PWDCOMPARE to validate the clear text password against the generated hash.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @ClearTextPassword NVARCHAR(128) = N'Sup3rS3cr3tP@$$'; DECLARE @Salt VARBINARY(4) = CRYPT_GEN_RANDOM(4); DECLARE @HashedPassword VARBINARY(150); /*Generate the password hash*/ SET @HashedPassword = 0x0200 + @salt + HASHBYTES(N'SHA2_512', CAST(@ClearTextPassword AS VARBINARY(128)) + @Salt); /*List the password, salt and hash, and validate password against the hash */ SELECT @ClearTextPassword AS [ClearTextPassword], @Salt AS [Salt], @HashedPassword AS [HashedPassword], CASE WHEN PWDCOMPARE(@ClearTextPassword, @HashedPassword) = 1 THEN 'Yes' ELSE 'No' END AS [PassMatchesHash]; |
PWDCOMPARE can also be used to compare a password against the password_hash column of the sys.sql_logins system catalog view.
Here’s a quick demo:
|
1 2 3 4 5 6 |
CREATE LOGIN [Test1] WITH PASSWORD=N'Sup3rS3cr3tP@$$', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO SELECT [name], PWDCOMPARE(N'Sup3rS3cr3tP@$$', [password_hash]) AS [IsPassword] FROM sys.sql_logins WHERE [name] NOT LIKE N'##%'; |
Generating a large number of password candidates
I have written this script for generating and testing password candidates based on:
- the comma-separated terms provided for the @BaseWordsList parameter
- instance information (database names, instance name, login names) if @UseInstInfo is set to 1
- 17 common words (season names, words and keyboard walks) – hard-coded in the script
These are combined with symbols and years (a 12 year range generated automatically based on the current date), and case variations are also applied.
This generates password candidates like “Contoso2017!”, “contoso22”, “<<Contoso>>”, “Fall2022##”, etc.
Note that the number of terms provided via @BaseWordsList, and, if @UseInstInfo = 1, the number of databases and SQL logins on the instance, directly impacts the number of generated password candidates.
As a quick example, running the script with @BaseWordsList = N'Contoso, administrator, fakeproject, VladDBA' and @UseInstInfo = 0 generates 195615 password candidates.
Granted the resulting list of candidates is not an exhaustive one, but it’s based on patterns I have seen being used by users, developers and vendors alike.
Cracking SQL login passwords
The second part of the script uses PWDCOMPARE and checks the generated passwords against the hashes found in sys.sql_logins’ password_hash column. It also checks if identified logins are members of dangerous fixed server roles.
For reference, this is how that part of the script looks:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [SL].[name] AS [LoginName], [P].[Word] AS [Password], IS_SRVROLEMEMBER(N'sysadmin', [SL].[name]) AS [IsSysAdmin], IS_SRVROLEMEMBER(N'serveradmin', [SL].[name]) AS [IsServerAdmin], IS_SRVROLEMEMBER(N'securityadmin', [SL].[name]) AS [IsSecurityAdmin], IS_SRVROLEMEMBER(N'dbcreator', [SL].[name]) AS [IsDBCreator] FROM sys.sql_logins AS [SL] INNER JOIN #WordList AS [P] ON PWDCOMPARE([P].[Word], [SL].[password_hash]) = 1 WHERE [SL].[name] NOT LIKE N'##%'; |
For testing purposes, I’ve created the following logins:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE LOGIN [sa1] WITH PASSWORD = N'Welcome123??', CHECK_POLICY = OFF; GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [sa1]; GO /*Login with a database name + special character as password*/ CREATE LOGIN [AppAdmin] WITH PASSWORD = N'AdventureWorks2017*', CHECK_POLICY = OFF; GO ALTER SERVER ROLE [serveradmin] ADD MEMBER [AppAdmin]; GO CREATE LOGIN [Dev1] WITH PASSWORD = N'(Contoso22)', CHECK_POLICY = OFF; GO ALTER SERVER ROLE [dbcreator] ADD MEMBER [Dev1]; GO CREATE LOGIN [Dev2] WITH PASSWORD = N'SUMMER20!', CHECK_POLICY = OFF; GO ALTER SERVER ROLE [dbcreator] ADD MEMBER [Dev2]; GO CREATE LOGIN [Test1] WITH PASSWORD = N'', CHECK_POLICY = OFF; GO |
And then I’m executing the script with only two custom terms – contoso and fakeproject.
Note that the QuickSQLPassAudit.sql script relies on the STRING_SPLIT function, so it only works on SQL Server 2016 and newer.
Conclusion
If you are constrained by either your environment or internal policies, yet still want to conduct an audit to identify weak passwords and update them, QuickSQLPassAudit.sql can help identify some potentially vulnerable passwords using the SQL Server instance itself to do all the work.
Leave a Reply