How do I create securely password protected .xls files in Excel 2010 from VBA

Question

I found that in Excel 2010 (and 2007), when you try and choose one of the more secure types of encryption, it seems to silently ignore your settings, leaving you with a file that can be cracked in seconds at decryptum for example.

This does not apply to the new file formats that are AES encrypted by default, only when you are saving down to the 97-2003 format.

This is the macro I create in the workbook:

Sub enc()
  Me.SetPasswordEncryptionOptions _
    PasswordEncryptionProvider:= "Microsoft Strong Cryptographic Provider", _
    PasswordEncryptionAlgorithm:= "RC4", _
    PasswordEncryptionKeyLength:=128, _
    PasswordEncryptionFileProperties:=True
  Me.SaveAs _
    Filename:="encryption_test.xls", _
    FileFormat:=xlExcel8, _
    Password:="password"
End Sub

It is quite easy to get Excel 2003 to (correctly) output with these encryption settings by omitting the FileFormat parameter:

Sub enc()
  Me.SetPasswordEncryptionOptions _
    PasswordEncryptionProvider:= "Microsoft Strong Cryptographic Provider", _
    PasswordEncryptionAlgorithm:= "RC4", _
    PasswordEncryptionKeyLength:=128, _
    PasswordEncryptionFileProperties:=True
  Me.SaveAs _
    Filename:="encryption_test.xls", _
    Password:="password"
End Sub

decryptum then fails to decrypt as I would expect.

Am I doing something wrong or is this a bug? If it is a bug, how do I work around it?

I've added a solution of sorts for Excel 2010 below, but I'm hoping for something simpler, and preferably something that works for 2007 as well.

Baddie

Answer 1

A workaround for Office 2010:

1. add the office 2010 administrative template (office14.adm)
2. edit "User Configuration\Administrative Templates\Microsoft Office 2010\Security Settings\Encryption type for password protected Office 97-2003 files" to "Microsoft Strong Cryptographic Provider,RC4,128" (without the quotes)

Baddie

Answer 2

It's because RC4 isn't enabled with Office 2007/2010 - you'll have to manually do exactly what you are doing with the admin template.

To prove this point, in Excel 2010, do your routine above and then open your "encryption_test.xls" file and press Alt + F11 to go to the VBE. Then run the following (press Ctrl + G to open the Immediate window first):

Sub CheckEncryption()
    Dim aw As Workbook: Set aw = ActiveWorkbook
    With aw
        Debug.Print .PasswordEncryptionAlgorithm
        Debug.Print .PasswordEncryptionFileProperties
        Debug.Print .PasswordEncryptionKeyLength
        Debug.Print .PasswordEncryptionProvider
    End With
End Sub

What you'll probably see are the values of 1) Office Standard, 2) False, 3) 40, and 4) Office. What this means is that as RC4 isn't being used; encryption defaults to standard - and crummy/old - Excel encryption. Obviously RC4 is thought of as pretty crummy too by MSFT, so use of it has been a bit hobbled in the newer Office clients.

See these two articles:

• Plan cryptography and encryption settings for Office 2010

• Plan document protection settings in the 2007 Office system

Footnote: The FileFormat portion of the SaveAs routine isn't culprit here. It's only doing what the CryptoAPI is telling it to do. In fact, from Excel 2010 VBA, you could use Excel 2003's SaveAs, which is slightly different and it wouldn't make a difference (from Excel 2010, it's a hidden routine that can be accessed like ActiveWorkbook.[_SaveAs] Filename:="encryption_test.xls", Password:="password".)

Edmiston

"you'll have to manually do exactly what you are doing with the admin template" - how do I do that? Is there a way of doing it 'manually' from VBA? – Baddie

@JackPDouglas: There is no way to set this manually from VBA. It's a Group Policy setting, as you mentioned in your workaround answer above. That is the actual way to do it in Office 2010, not a workaround. You can apply this policy to either the local machine or to all machines via a domain controller. See: technet.microsoft.com/en-us/library/ff400327.aspx. – Edmiston

+1 because your comments are informative - but I am still hoping for an answer that is simpler to implement than mine. For example, a solution just involving HKLM registry settings would be much easier to implement than installing this admin template on every PC in our office (and I'm not sure I can do it on the DC because it is a Windows 2000 domain) – Baddie

PS, don't you agree that this is an glaringly bad decision on MS part? VBA code that produced pretty secure encrypted files in Office 2003 suddenly produces easily crackable files in 2007/2010, without warning you? – Baddie

@JackPDouglas: Sorry to be the bearer of bad news on this one. I totally agree with your statement that security from 2007/2010 to 2003 has kind of taken a complete step backwards given that it defaults to really poor security without any warning. But unfortunately you're not going to find any other kind of workaround like a registry setting on this one - it is what it is. For a DC GPO deployment you'll need Windows Server 2003 or later. – Edmiston

@JackPDouglas: You just need the Office 2007 admin template, which is part of microsoft.com/downloads/en/…. Install the office12.adm template and then under Security Settings | Encryption type for password protected Office 97-2003 files, set as you have with Office 2010 template. – Edmiston

that didn't work for me - it silently fails and the fials can be cracked on decryptum. I'll try again on a freshly installed VM – Baddie

I can't comment on what decryptum uses to crack Excel files - I have no idea what they can/can't do. I'm just talking about general Excel 2003 security for RC4 from Office 2007/2010. – Edmiston

decryptum does not even try to decrypt if "Microsoft Strong Cryptographic Provider" is used. I just use it to test whether Office has really used the settings or not... – Baddie

I just tested it from Office 2007. First I installed the Office 12 admin template, enabled the Encryption type for password protected Office 97-2003 files and set as Microsoft Strong Cryptographic Provider,RC4,12. Then I opened a workbook, saved it as test.xls, opened the VBE and put in your code above (except your Me. was replaced with Dim aw As Workbook: Set aw = ActiveWorkbook and aw. was used in place of Me.). Then I ran the code, confirmed settings with my CheckEncryption routine. Then I did the trial of decryptum and it could not decrypt...see next comment – Edmiston

The error received from decryptum was Error: [Sorry, the file you uploaded is encrypted by Microsoft Strong Cryptographic Provider and is not supported.] Note that the routine of yours I used from Excel 2007 was the second one (the one without FileFormat:=xlExcel8) – Edmiston

Thinking about why this may have failed for you Jack, I think it is because your workbook is still in xslx format when you set the encryption. First, get it in xsl format with a SaveAs, then set the encryption, then set the password, then save it again. That should work well. – Edmiston