Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Protection & LInking (2000)

    My director wants to send a interactive pricing workbook to a customer. Originally he hid the sheets and password protected them, but when I demonstrated how easy it was to "crack" the password we had to revisit the problem.
    Creating a new data workbook with a file open password, then linking this to the data sheet was the next trial, but the data sheet then prompted for the file open password for the links.
    I am now stumped, is there a way we can safely provide our customer with an Excel workbooks but only allow them to see the input sheet? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Protection & LInking (2000)

    Not if they have anywhere the skills shown by the contributors to the discussion that starts with <!post=this post,228399>this post<!/post>, or know someone with those skills.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Protection & LInking (2000)

    Depressing, thank goodness my director asked me to check if the model was secure before he sent it to our customer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Wonder if there are third party password products for Excel?
    Thanks for your response John, I can see that we need to put this into an application - more work <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protection & LInking (2000)

    Hi Paul,

    One way to defeat most 'clever' users is to use a timer-driven macro that re-protects the worksheet/workbook every second. Then, even if they run their 'unprotect' macro, it won't do much good ... Oh, and protect the VBA project too.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Protection & LInking (2000)

    Now that's a good idea. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    As a VBA tyro do you have an example of this?
    I am raising a separate post to ask if there is a third party password add-in that can be used, but your suggestion could be what I am initially looking for. Thank you
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protection & LInking (2000)

    <hr>VBA tyro<hr>

    Hey, don't get carried away - my VBA skills are fairly basic. Anyway, here's a workbook that updates it's protection every second. So, even if someone cracks the password (there isn't one in this case) and unprotects the workbook, it'll automatically re-set within a second. You'll need to press Alt-F11 to get into the code, because the modules are hidden too - though not protected.

    You could make things even more difficult for workbook hackers by having the password momentarily unprotect the workbook then re-protect it with a random number (which it'd have to remember for the next update). Then, even if they managed to find out the current password, it'd be different within a second.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Protection & LInking (2000)

    Cheers macropod, I have downloaded the file and will look at this later in the morning.
    Thank you <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    Paul Coyle
    Approach love and cooking with reckless abandon

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protection & LInking (2000)

    One thing to be aware of with this approach: (this might NOT matter in a final application)

    Many of the spreadsheet functionality will be inhibited since a macro is "always" running (no copy/pasting, some formating, etc)

    Steve

  9. #9
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Protection & LInking (2000)

    Further to this topic, I came across this in a Help file - Damn and Blast <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>
    If you have a document with password-protected VBA project, but for some reason the password cannot be recovered, or the password shown by AO2000PR cannot be entered (e.g. it contains non-English characters that cannot be entered using your keyboard), or AO2000PR only allows to change or remove that password (but you would not like to do that), you can use the VBA backdoor feature. It works for all applications which can create VBA projects in their documents, not only Microsoft Office (for example, Corel WordPerfect Office).

    With that feature, the password is not being recovered at all. However, you
    Paul Coyle
    Approach love and cooking with reckless abandon

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protection & LInking (2000)

    <P ID="edit" class=small>(Edited by macropod on 03-Mar-03 08:25. See PS)</P>Hi Steve,

    Actually, copying/pasting etc are still available, to the same extent as with any protected sheet, PROVIDED you do them within the one second between password updates.

    One thing I noticed when developing an enahnced version for using random number passwords (attached) was that inserting a new sheet or changing sheets broke my code, because of 'stored' password mis-matches. Whilst I could have overcome that by forcing all sheets to have the same password, or by tying the the code to a particular sheet, what I found in resolving that issue was an appallingly simple way of breaking worksheet protection.

    All one needs to unprotect a password-protected worksheet is a three-line macro:

    Sub KillPwd()
    ActiveSheet.Protect Password:=""
    End Sub

    Excel (I'm using 2K on two machines - one with SR1 and another with SR3) doesn't even bother to check whether the worksheet is protected before executing this!!!

    Cheers

    PS: The attached workbook has a module named "ClearPassword" that I've left in for development purposes. It demonstrates the password breaking method I described, and switches off the random password generator in the workbook. Obviously, you wouldn't want to distribute your final workbook with that module attached.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection & LInking (2000)

    This thread is getting scarier by the second <img src=/w3timages/censored.gif alt=censored border=0> <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> <img src=/w3timages/censored.gif alt=censored border=0> .

    I tried your empty password trick with Excel XP (SR-1) and Excel 2000 (SR-3) and could not get it to unprotect the worksheets. Maybe MS finally plugged this hole (years too late) but with all the other ways to crack a workbook I wonder why I bother to come up with long, random passwords, spend $ on digital certs, etc.

    And to be totally off subject, I've started learning .Net and there are free disassemblers to view your code quite easily. I saw an an e-book by Dan Appelman (sp?) about how to do something to your .Net files so they are hidden and can't be so easily viewed. Now isn't MS the same one who goes fiercely after everyone who unlawfully copies their s/w? Something is pretty screwy here <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Deb [bash[

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protection & LInking (2000)

    <P ID="edit" class=small>(Edited by macropod on 04-Mar-03 17:27. Second sub deleted from original post due to inconsistent behaviour)</P>Hi Deb,

    Strange. It isn't working for me now either, but one that does working is:

    Sub ClearPassword()
    ActiveSheet.Protect "", , , , True
    ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
    End Sub

    which does clear the password.

    Cheers???
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection & LInking (2000)

    Hi all,
    I was cruisin' Excel 2002 help files and see that there's something new about password protection. It lets you set encryption type and other stuff. Here's the sample code:
    <pre>Sub SetPasswordOptions()
    ActiveWorkbook.SetPasswordEncryptionOptions _
    PasswordEncryptionProvider:="Microsoft RSA SChannel Cryptographic Provider", _
    PasswordEncryptionAlgorithm:="RC4", _
    PasswordEncryptionKeyLength:=56, _
    PasswordEncryptionFileProperties:=True
    End Sub</pre>

    Interesting, eh? So I what you wanna bet that this hasn't already been broken by any of the hordes of password cracking code? I finally have my boss trained that he now knows not to put any confidential data in Excel that will be in a workbook he sends out to anyone he can't trust to not hand over a copy of the file. I told him I can re-write some of these Excel tools I've done in C/C++ but then of course it'd take a lot longer to develop (which he doesn't like). <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Deb <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15> <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  14. #14
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection & LInking (2000)

    Yup, that's a doozy. It works even on my typical 12 character passwords

    BUT this assumes you have access to the workbook's VBE which should always be protected. Now if we think that the project password is harder to crack (which I heard differing opinions on) then the only other way to crack a given worksheet is by using this code in another workbook (one you created just to crack the protected one), right?

    I tried it and yes I was able to crack another workbook. I ran this code from Book2 to crack Book1.
    <pre>Sub CrackOtherWorkbook()
    Application.Workbooks("book1").Worksheets("sheet1" ).Protect "", , , , True
    ' written on two lines to keep The Lounge happy
    Application.Workbooks("book1").Worksheets("sheet1" ).Range("a1").Copy _
    Application.Workbooks("book1").Worksheets("sheet1" ).Range("a1")
    End Sub</pre>

    Since you probably don't know the worksheet names you can just use sheets(1) instead of the actual name.

    I'm really depressed now. Why bother locking the barn door when the cows have the keys? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>
    At least it fails nicely in Excel 2002 (but I'm sure there's some other hole that someone has discovered to clear the password).

    Deb <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Protection & LInking (2000)

    Hi Deb,

    One way to address security/IP concerns might be with a spreadsheet compiler - one such product that's been around for some years is 'Visual Baler', One of the claimed attributes of the compiler (which I've never used) is that, as such, it turns spreadsheets into executables that can't easily be cracked. It looks like it's still available - see http://www.the-ciba.com/

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •