How Excel Password Protection Works

Question

This code has been floating around the network for several years now - it can apparently provide a password for decrypting an Excel spreadsheet that you don't know the password for.

http://www.theofficeexperts.com/VBASamples/Excel02.htm

I am wondering how this works, but I seem to be unable to solve it. I assume that it is encrypted under a certain value, which can be obtained in several ways (several places on the network say that it will give you the original password or another one that will work) kind of like a public key - you can have 100 public keys that work with one private.

It seems to me that he creates integer variables and fills them with a certain number before turning this number into the corresponding characters. Wouldn't that always be the same? If so, is there a "master password" for Excel protection?

Thanks everyone!

EDIT: I noticed that For n = 32 to 126 in the above code example. Cross references with an ASCII table, which seems to be all characters from place to tilde. Is this a kind of dictionary attack that I don't understand due to the syntax?

Answer 1

This is a small world, because the code indicates that I posted this code on another forum about 10 years ago, seeing it somewhere else - I think John Walkenbach's old website

It is important to note that this code protection applies only to worksheet protection - not to opening an Excel file or VBA passwords.

• One example of this complete entry is here (screenshot below)
• googling excel sheet protection "test" and "zzyw" gives other links, such as from Tom Urtis

Answer 2

Fascinating - I used to know a code snippet, but not the explanation Brettj wrote. As others have explained, this is a search for brute force for hash collisions. In fact, it seems that this was done by trial and error, as it does a lot more work than necessary (194560 combinations were generated, but there are only 32768 hash values).

Short Excel hashing algorithm (as described in http://chicago.sourceforge.net/devel/docs/excel/encrypt.html ):

• Take the ascii code of each passwort character.
• Treat it like a 16-bit number. Move its bits to the left, depending on the position of the character (1 bit for the first character, 2 for the second, etc.).
• XOR all characters together, giving a 16-bit signed int> = 0.
• XORs that result in password length and magic number.

Knowing this, you can begin the search for brute force as follows:

• The most significant bit is always zero, so 15 bits are required for testing.
• Divide them into three counters, each of which covers 5 bits. Thus, each counter can represent a printed ascii char.
• Set the ascii representation of these counters to a password string so that they do not affect each other.

The easiest way is to use an 11-digit password and place the counters in positions 1, 6 and 11. Switching the bits in step 2 aligns the counter bit in the right direction: the first counter ("x") is shifted 1 bit, the second ("y") is 6 bits, the third ("z") is 11 bits. In the bitwise hash representation, counters affect the following bits:

 bit: 76543210 76543210 cnt: -zzzzyyy yyxxxxxz 

XOR operations can be ignored because the XOR argument is constantly constant. For the same reason, you can add a constant offset (for example, 64). It also does not matter which character is used for other password bytes (2-5, 7-10).

Repeating all possible combinations of x, y, z, you will eventually find a password that gives the same hash value as the original one.

 Public Sub demo() ' http://stackoverflow.com/questions/12852095/how-does-excels-worksheet-password-protection-work Dim x As Integer, y as Integer, z as Integer Dim part1 As String, part12 As String Dim sh As Worksheet Set sh = ThisWorkbook.Worksheets(1) sh.Protect "$ome_Insanely_Long_and_c0mplex_password! [(which i$ imp*ssible t0 re-member)]" For x = 64 To 95 ' pad with dots, so that x, y and z affect nonoverlapping bits of the hash. part1 = Chr(x) + "...." For y = 64 To 95 part12 = part1 + Chr(y) + "...." For z = 64 To 95 On Error Resume Next sh.Unprotect part12 + Chr(z) If Err.Number = 0 Then Debug.Print "Password: '" & part12 + Chr(z) & "'" Exit Sub End If On Error GoTo 0 Next Next Next End Sub 

Answer 3

Just guessing, but it seems to me that Excel tests are checked if the password is valid by running it through some kind of hash function that produces a fairly small range of results and compares it with the stored hashed value.

Presumably, this function tests all of these values ​​until it finds one that works. Judging by the values ​​used, the hash function produces 2 ^ 11 * (126-31) different values, all of which can be obtained by the values ​​generated in this code.

My analysis suggests that this procedure works. I have not tested it.

Answer 4

The code searches for brute force using the AAAAAAAAAAAA (SPACE) encryption passwords through BBBBBBBBBBBB (~), where (SPACE) is the space character (CHR (32)) and (~), of course, is the character 126. When the password that it displays the password in message box.

Of course, this means that it only checks passwords with a length of 12 characters and consists only of the capital letters A (ASCII 65) and B (ASCII 66), followed by one of the printable ASCII characters. @mkingston is correct that he tests 2 ^ 11 * (126-31) different values. But there is no hash function. I do not think that this will lead to hacking many spreadsheets. You would be better off using One of these programs from AccessData .

For more information about ActiveSheet.Protect and ActiveSheet.Unprotect, see http://msdn.microsoft.com/en-us/library/office/aa191957(v=office.10).aspx .

Answer 5

 Sub FindPassword() 'Breaks worksheet password protection. Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If ActiveSheet.ProtectContents = False Then MsgBox "One usable password is " & Chr(i) & Chr(j) & _ Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End Sub