Page 1 of 2

[Solved] Forgotten Password

PostPosted: Thu Jun 11, 2009 1:48 pm
by brodiemac
I have an end user who password protected a sheet in their workbook. They have somehow lost the password. Is there any way to circumvent the password on that sheet? I know it sounds counter-intuitive to post how to circumvent security in Calc but I figured I'd give it a try.

Re: Forgotten Password

PostPosted: Thu Jun 11, 2009 2:19 pm
by Villeroy
There is no document-security as soon as you load a document successfully into the RAM of a computer. Thus the passord for a whole document encrypts the document and you should never forget such a password. Within the document everything is just child-proof against casual mistakes.
The document is a zip-archive with XML. Get file content.xml within the archive and find the node where the table is defined and protected with an encrypted password. Simply set protected="false" and you will be able to edit the table and then re-protect it, preferably without password since it is rubbish anyway.

Example snippet from content.xml
Code: Select all   Expand viewCollapse view
<table:table table:name="Sheet1" table:style-name="ta1" table:protected="true" table:protection-key="K7O251GdsyeKg54HeoSQ5ak3goc="


 Edit: fixed typo "protected = false" 

Re: [Solved] Forgotten Password

PostPosted: Thu Jun 11, 2009 2:37 pm
by brodiemac
It took me a minute to grasp what you were telling me but I got it and it worked perfectly. Thanks!

Re: [Solved] Forgotten Password

PostPosted: Thu May 19, 2011 6:19 am
by aezakmi
"Get file content.xml", could you please more specific to recover the document.

thank you

Re: [Solved] Forgotten Password

PostPosted: Thu May 19, 2011 6:22 am
by aezakmi
"The document is a zip-archive with XML. Get file content.xml within the archive and find the node where the table is defined and protected with an encrypted password. Simply set "protected=false" and you will be able to edit the table and then re-protect it, preferably without password since it is rubbish anyway. "

cant able to understand

Re: [Solved] Forgotten Password

PostPosted: Thu May 19, 2011 2:22 pm
by FJCC
Get the file from which you want to remove the password. Let's say is is called MySpreadsheet.ods. Change the file extension to zip so that the file name is now MySpreadsheet.zip. Open the zip archive with whatever program your computer uses for zip files. There will be a file named content.xml which can be edited as Villeroy describes. I have never actually edited one, so my knowledge ends there.

Re: [Solved] Forgotten Password

PostPosted: Fri May 20, 2011 6:10 am
by collinjn81
I spend half an hour to tried to remove the password and give up. Can anyone give specific step to remove password.

What I did as below:
1. Create spreadsheet with password. File name is "Untitled1"
2. Change by rename extension to "Untitled1.zip" and the file changed to zip archive.
3. On archive there got lot of XML file, and I used notepad to open "content.xml ".

-I not able to see the contain because of the wording in Chinese character..
Not working...

Re: [Solved] Forgotten Password

PostPosted: Fri May 20, 2011 8:23 am
by Villeroy
You created a password protected document which is secure because it is also encrypted. That's why you can not decypher any content in content.xml. Whithout the correct password there is no way to load this document.
This topic was about sheet protection via menu:Tools>Protect>Sheet which is a weak protection of the locked cells on the selected sheet(s).

Re: [Solved] Forgotten Password

PostPosted: Fri May 20, 2011 8:54 am
by collinjn81
Sorry for misunderstanding. I already get what you mean, now I already tried to used protected sheet. After I rename the file name to "Untitled1.zip", the file will change to Zip folder and with the content inside. I open up "content.xml" and edit "protected=true" to "protected=false" and replaced back "content.xml" to the zip folder.

I not able to open up my spreadsheet now because the file now in ZIP format.

Re: [Solved] Forgotten Password

PostPosted: Fri May 20, 2011 9:21 am
by Villeroy
Your native ODF documents are always in zip format. Like most Windows user you have a wrong idea about file formats. It has nothing to do with the file name extension and you can open any file with any program even though WIndows tries very hard to keep you on a certain track. It insists on one (Microsoft) program for each file name extension. Althouth the file name extension plays a prominent role within the WIndows environment, WIndows hides file name extensions by default which causes incredible misconceptions since the days of WIndows 95.

Rename your Untitled1.zip to Untitled1.ods and after one heavy (but idiotic) warning Windows has no objections to load the file using OpenOffice.org. Regardless of the file name extension, which is nothing more than a tag or a hint, you can really open any file with any program.

Re: [Solved] Forgotten Password

PostPosted: Fri May 20, 2011 9:53 am
by collinjn81
Finally I able to open the spreadsheet, using mouse ->Right click-> Open With->


Thanks for quick reply..

Re: [Solved] Forgotten Password

PostPosted: Wed Jul 13, 2011 3:36 pm
by Tramp
worked perfectly, thank you.

One note. After converting to .zip, I could not save content.xml directly into the zip file so I saved it with the same file name onto the desktop and simply dragged it into the zip file and chose "replace." easy enough.

Re: [Solved] Forgotten Password

PostPosted: Thu Mar 22, 2012 1:20 pm
by ajay3
collinjn81 wrote:I spend half an hour to tried to remove the password and give up. Can anyone give specific step to remove password.

What I did as below:
1. Create spreadsheet with password. File name is "Untitled1"
2. Change by rename extension to "Untitled1.zip" and the file changed to zip archive.
3. On archive there got lot of XML file, and I used notepad to open "content.xml ".

-I not able to see the contain because of the wording in Chinese character..
Not working...



MINE ALSO SAME.. WHEN I EDIT..IT DISPLAYS CHINESE CHARACTERS...
PLZ HELP ME WHAT IS NEXT...CONFUSED!!!
USING WINDOW 7
DO I NEED TO INSTALL SOME SOFTWARES IN MY PC ?

Re: [Solved] Forgotten Password

PostPosted: Thu Mar 22, 2012 2:41 pm
by FJCC
If you added a password to the document, then the document has been encrypted and only the password will allow you to read it. When you saved the document, did you select Save With Password? If so, your only hope is to remember the password.

Re: [Solved] Forgotten Password

PostPosted: Thu Mar 22, 2012 10:36 pm
by ajay3
Tramp wrote:worked perfectly, thank you.

One note. After converting to .zip, I could not save content.xml directly into the zip file so I saved it with the same file name onto the desktop and simply dragged it into the zip file and chose "replace." easy enough.


collinjn81 wrote:I spend half an hour to tried to remove the password and give up. Can anyone give specific step to remove password.

What I did as below:
1. Create spreadsheet with password. File name is "Untitled1"
2. Change by rename extension to "Untitled1.zip" and the file changed to zip archive.
3. On archive there got lot of XML file, and I used notepad to open "content.xml ".

-I not able to see the contain because of the wording in Chinese character..
Not working...




MINE ALSO SAME.. WHEN I EDIT..IT DISPLAYS CHINESE CHARACTERS...
PLZ HELP ME WHAT IS NEXT...CONFUSED!!!
USING WINDOW 7
DO I NEED TO INSTALL SOME SOFTWARES IN MY PC ?

Re: [Solved] Forgotten Password

PostPosted: Fri Mar 23, 2012 3:17 am
by kingfisher
If you saved with a password unintentionally, the password is probably blank. Try clicking the OK button when asked for the password.

Re: [Solved] Forgotten Password

PostPosted: Mon Aug 20, 2012 2:56 am
by mahogan
Sorry for posting on a Solved thread, but I am still struggling with this same issue. I believe I followed Villeroy's suggestion. I have a file where I created a password to protect the sheet only, the document is not protected and does not have a password either. I saved the file and renamed the file extension to ZIP, then opened the file with PeaZip, extracted the contents to a folder on my desktop. Then opened the contents.xml with notepad++ (I tried windows notepad with the same results). Then searched and changed "true" to "false" and saved the file. Then rezipped the folder with all the same files. Renamed it to .ods extension and attempted to open the file (I am using Libre Office, but it should not matter). Attached are the errors that I get, where it says it is corrupt and tries to repair the file, but cannot.

The working file is attached and also the non-working file as well.

Can someone please let me know if I am missing a step or am just not understanding something.

Thanks,
~Mahogan

Re: [Solved] Forgotten Password

PostPosted: Mon Aug 20, 2012 5:44 am
by FJCC
If I change the extension of your non-working document to zip and open it, I see a folder called sample-sheet-protected-password, within which are all the files and folders of the ods file. What you have to do is move all those files and folders to the top level of the zip archive and eliminate the sample-sheet-protected-password folder.

Re: [Solved] Forgotten Password

PostPosted: Mon Aug 20, 2012 6:36 am
by mahogan
Thanks, yes I see what I did wrong. I zipped the top level folder with all the folders and file under that folder, instead, as you suggested, I should have selected all the files and folders into a file, it perhaps is a PeaZip issue or a PeaBrain issue, LOL. Either way your advice was very helpful. Thank you!

Re: [Solved] Forgotten Password

PostPosted: Sat Mar 09, 2013 1:16 pm
by arnehe
I used following command to remove the protection in the content.xml file:

Code: Select all   Expand viewCollapse view
sed -i 's/protected=\"true\"/protected=\"false\"/g' content.xml

Re: [Solved] Forgotten Password

PostPosted: Tue Aug 19, 2014 3:19 am
by Luis Guerrero
Sorry for posting on a closed topic. It is very interesting to understand what the underlying architecture of the .ods archives is, but if you have a protected sheet in an unprotected book and you lost the password for the sheet, the easiest way to "unprotect" it is to add a new sheet, copy all the protected sheet and paste it to the new sheet.

Re: [Solved] Forgotten Password

PostPosted: Tue Aug 19, 2014 12:28 pm
by Lupp
The above suggested solution will only work for contents which may be protected against changes but are NOT HIDDEN. Otherwise the attempted 'Copy All' will fail. You will get copied only the unprotected part.

Re: [Solved] Forgotten Password

PostPosted: Sun Sep 21, 2014 9:54 pm
by horrified
I have several docs with this prob - key correct password bit OpenOffice says it's wrong.

Tried creating new doc with password - that didn't fix prob with original docs.
Tried reboot - same result.
Tried uninstalk then install 3.4.1 - same result.

I have hours of work in the docs and recently moved from an old version of ms office to latest OpenOffice.

I would be grateful if someone would provide a solution so I can open the docs?

Re: [Solved] Forgotten Password

PostPosted: Mon Sep 22, 2014 12:32 pm
by Villeroy

Re: [Solved] Forgotten Password

PostPosted: Mon Sep 22, 2014 10:09 pm
by horrified
Hi Villeroy

Thanks for the reply but I have a Mac per my signature.

This is MS software suitable for MS Win but not suitable for Mac OS X.

My problem persists...

Re: [Solved] Forgotten Password

PostPosted: Mon Sep 22, 2014 10:12 pm
by Villeroy
Get a Windows PC. They are everywhere.
Try some online converter. I don't know if http://zamzar.com supports passwords by any means.

Re: [Solved] Forgotten Password

PostPosted: Sat Oct 25, 2014 4:59 am
by R-Easter
Hi, I have a question for collinjn81;

I am having the same problem where I have a password protected document and don't know the password. I renamed it to a zip, and when I open the content.xml, it is encrypted. I saw that you had the same problem, but I didn't see HOW you were able to open and edit the content.xml, getting rid of the chinese characters (encrypted characters).

(From the post Friday May 20, 2011); the post after this one just said you were able to edit it. How were you able to open it with normal characters/unencrypted text, if you remember? Thanks!

I spend half an hour to tried to remove the password and give up. Can anyone give specific step to remove password.

What I did as below:
1. Create spreadsheet with password. File name is "Untitled1"
2. Change by rename extension to "Untitled1.zip" and the file changed to zip archive.
3. On archive there got lot of XML file, and I used notepad to open "content.xml ".

-I not able to see the contain because of the wording in Chinese character..
Not working...

Re: [Solved] Forgotten Password

PostPosted: Sat Oct 25, 2014 6:58 am
by FJCC
If you have a password protected document, the method of extracting the content.xml file will not work. The posts by collinjn81 are rather confusing but if he fixed his problem without the password, it must have been a password protected sheet, not the document.

Re: [Solved] Forgotten Password

PostPosted: Sat Apr 28, 2018 10:55 pm
by GiveMeMyDew
This post has been good reading so far but...

I have a password problem NOT the file but on a couple of internal sheets and I cannot find the proper text string in the content.xml ???
Did they change the file somehow over time with newer versions?

Villeroy wrote:There is no document-security as soon as you load a document successfully into the RAM of a computer. Thus the passord for a whole document encrypts the document and you should never forget such a password. Within the document everything is just child-proof against casual mistakes.
The document is a zip-archive with XML. Get file content.xml within the archive and find the node where the table is defined and protected with an encrypted password. Simply set "protected=false" and you will be able to edit the table and then re-protect it, preferably without password since it is rubbish anyway.

Example snippet from content.xml
Code: Select all   Expand viewCollapse view
<table:table table:name="Sheet1" table:style-name="ta1" table:protected="true" table:protection-key="K7O251GdsyeKg54HeoSQ5ak3goc="

Re: [Solved] Forgotten Password

PostPosted: Sun Apr 29, 2018 2:07 am
by robleyd
The structure is still the same in my 4.1.5. I just created a new calc file, protected sheet1 with password and saved. This is a snippet from content.xml:

Code: Select all   Expand viewCollapse view
<table:table table:name="Sheet1" table:style-name="ta1" table:protected="true" table:protection-key="ubNkMoDtAY9JIUqIuIalUOhiqfI=" table:print="false">


If you wish to make your file available, someone could try to remove the protection for you.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.