How to ensure SHA-2 class, SHA512 algorithm, to calculate the hash values for sheet passwords

[DavCode]

I am led to believe (from internet research) that perpetual Office licenses 2013 and 2016 (and possibly later perpetual license versions?) do NOT use the Office 365 (2013 and onwards) sheet encryption algorithm (SHA-2 class, SHA512), to calculate the hash values for it's sheet passwords.

I have also learned that a file that is opened in any version of Excel that does not use the updated encryption method will revert to the earlier (and much less secure) encryption method, employed by Microsoft prior to 2013, if the file is unprotected and then re-protected in that earlier version.

Is there a way to prevent a user from opening the file in any version of Excel that does not use the later encryption method, e.g. check the version at Workbook.Open event?

Thanks!

 

[rlv01]

1. I did not think it was possible to encrypt a worksheet. Are you sure you do not mean workbook?

I have also learned that a file that is opened in any version of Excel that does not use the updated encryption method will revert to the earlier (and much less secure) encryption method, employed by Microsoft prior to 2013, if the file is unprotected and then re-protected in that earlier version.

2. An interesting question. I think it would be unlikely that a file saved by Excel 365 in the open doc format (xlsx) and encrypted with AES-256 , and the password hashed with SHA-2 that Excel 2010 (AES128, SHA1) would be able to open it. 2010 does not contain AES256 or SHA2; but it is certainly something you could test. However if the file was opened and the password removed (by the later version) then certainly if you saved with Excel 2010 as password protected it, the level of encryption would not be as strong.

The one exception might be if your organization's IT department had modified your registry to dumb-down the default at-rest encryption for compatibility with older versions.

The only thing you can really do via macro is to test for the Excel version and take some kind of action if the file is too old.

 

[DavCode]

Thanks rlv01,

So, by 'encrypted worksheet' i mean password-protected sheets.

I have created a vba encoded 'login box' (password-protected) for users to access the workbook, rather than using the Excel file-open password. Users could still employ a workbook password, on top of that, if desired, but it should not be necessary.

The problem i foresee is that a genuine user, who has access to the workbook, would trigger the 'reduced hash' sheet password creation when using the workbook in an 'older' version of Excel. There are numerous events in my coding that require various sheets to be unprotected and re-protected 'behind the scenes'.

The file should be able to be opened by older versions of Excel, but the completeness and aesthetics of the opened file would come down to the 'compatibility of features' between the version the file was created in and the version in which it is being opened. This is not really my problem because I can 'recommend' users to have a version equal to or later than a selected version, e.g. Office 2013.

Regarding your suggestion to use vba to check the Excel version, are you able to point me in the direction of some suitable coding for that?

Regarding your point about a potentially modified device registry, is that something that could pose a threat to the stronger hash protection, even if a successful check was made for the minimum version of Excel, i.e. File opened in the latest version of Excel (with strong encryption) could have its (sheet) encryption purposely reduced?

 

[rlv01]

You may have a password protected worksheet, but the worksheet itself is not encrypted, and while the sheet password may be hashed, if the file is unencrypted (no file open password), there is a trivially easy way to defeat it floating around the nets. I won't go into details as I am sure that it would break a forum rule. My point in mentioning it is that by relying on worksheet passwords against a serious threat model you may be giving yourself a false sense of security.

Also , VBA implemented worksheet security is just not that secure. Unless you have implemented it as an add-in, your project password can probably be cracked and then a coder can probably untangle your password scheme fairly quickly (an interesting vba project protection scheme here: Unviewable+ VBA for Excel PowerPoint Word AutoCAD Visio)

A post I read somewhere or another put it like this:

Password protection on worksheets is to prevent your friends from changing things by mistake.​

Password protection on workbooks (file open password) is to prevent robbers breaking into your house and stealing everything you own.​

In other words, for serious IP protection, worksheet password protection should not be the first line of defense.

File opened in the latest version of Excel (with strong encryption) could have its (sheet) encryption purposely reduced?

If you really wanted to get into it, you could research the MS docs to find the registry settings that change the defaults and then use VBA to read the registry keys to check.

Regarding your suggestion to use vba to check the Excel version, are you able to point me in the direction of some suitable coding for that?

Application.Version

e.g.

Dim V As Double
V = Val(Application.Version)
If V < 16 Then
  'do something
End

If

 

[DavCode]

1. What about my proposed vba-generated 'login box'? Do you think that would be equally as effective as using an Excel 'file open password', i.e will it stop the robbers?

Interesting you mention Unviewable+ as I am intending to use that to secure the VB Project - it would seem to be able to prevent the majority of potential 'attacks' where people might try to crack-in but soon give up.

2. The issue remains that I wish to secure the sheet formulae as far as possible (definitely not 'as a first line of defense', but more to prevent 'sticky-beaks') by firstly using very strong sheet passwords and secondly, by preventing the file from being opened in a less secure version of Excel where the sheets can easily and unknowingly be 're-password-protected' with a very 'crack-able' password. I think checking the version at workbook.open will work (and perhaps also the registry check) except....

3. I have read that some perpetual Office licenses, e.g. 2013, 2016 and possibly later versions, DO NOT have the SHA-2 class, SHA512 algorithm. Are you perhaps aware of this?
I will research Microsoft directly if I can't get further confirmation here.

Thanks for your time so far.