[Solved] Forgotten Password

[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.

[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"

[brodiemac]

It took me a minute to grasp what you were telling me but I got it and it worked perfectly. Thanks!

[aezakmi]

"Get file content.xml", could you please more specific to recover the document.

thank you

[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

[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.

[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...

[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).

[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.

[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.

[collinjn81]

Finally I able to open the spreadsheet, using mouse ->Right click-> Open With->


Thanks for quick reply..

[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.

[ajay3]

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 ?

[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.

[ajay3]

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 ?

[kingfisher]

If you saved with a password unintentionally, the password is probably blank. Try clicking the OK button when asked for the password.

[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

[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.

[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!

[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

[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.

[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.

[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?

[Villeroy]

http://www.microsoft.com/en-us/download ... .aspx?id=4

[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...

[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.

[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...

[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.

[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?

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="

[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.