[Solved] Forgotten Password

Discuss the spreadsheet application
User avatar
brodiemac
Posts: 24
Joined: Tue Feb 24, 2009 4:05 pm

[Solved] Forgotten Password

Post 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.
Last edited by brodiemac on Thu Jun 11, 2009 2:36 pm, edited 1 time in total.
Great minds discuss ideas;
Average minds discuss events;
Small minds discuss people.
-- Eleanor Roosevelt
OOo 3.3 on M$ Windows 7 SP1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Forgotten Password

Post 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

<table:table table:name="Sheet1" table:style-name="ta1" table:protected="true" table:protection-key="K7O251GdsyeKg54HeoSQ5ak3goc=" 
 Edit: fixed typo "protected = false" 
Last edited by Villeroy on Sun Apr 29, 2018 4:02 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
brodiemac
Posts: 24
Joined: Tue Feb 24, 2009 4:05 pm

Re: [Solved] Forgotten Password

Post by brodiemac »

It took me a minute to grasp what you were telling me but I got it and it worked perfectly. Thanks!
Great minds discuss ideas;
Average minds discuss events;
Small minds discuss people.
-- Eleanor Roosevelt
OOo 3.3 on M$ Windows 7 SP1
aezakmi
Posts: 2
Joined: Thu May 19, 2011 6:11 am

Re: [Solved] Forgotten Password

Post by aezakmi »

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

thank you
OpenOffice 2.4 on Windows XP
aezakmi
Posts: 2
Joined: Thu May 19, 2011 6:11 am

Re: [Solved] Forgotten Password

Post 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
OpenOffice 2.4 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Forgotten Password

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
collinjn81
Posts: 28
Joined: Thu May 19, 2011 9:38 am

Re: [Solved] Forgotten Password

Post 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...
Open office 4.0.1
Operating system: Window Vista Business
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Forgotten Password

Post 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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
collinjn81
Posts: 28
Joined: Thu May 19, 2011 9:38 am

Re: [Solved] Forgotten Password

Post 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.
Open office 4.0.1
Operating system: Window Vista Business
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Forgotten Password

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
collinjn81
Posts: 28
Joined: Thu May 19, 2011 9:38 am

Re: [Solved] Forgotten Password

Post by collinjn81 »

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


Thanks for quick reply..
Open office 4.0.1
Operating system: Window Vista Business
Tramp
Posts: 1
Joined: Wed Jul 13, 2011 3:29 pm

Re: [Solved] Forgotten Password

Post 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.
Open Office 3.1 on windows XP & Windows 7
ajay3
Posts: 2
Joined: Thu Mar 22, 2012 1:17 pm

Re: [Solved] Forgotten Password

Post 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 ?
OpenOffice 3.1
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Forgotten Password

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
ajay3
Posts: 2
Joined: Thu Mar 22, 2012 1:17 pm

Re: [Solved] Forgotten Password

Post 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 ?
OpenOffice 3.1
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: [Solved] Forgotten Password

Post by kingfisher »

If you saved with a password unintentionally, the password is probably blank. Try clicking the OK button when asked for the password.
Apache OpenOffice 4.1.9 on Linux
User avatar
mahogan
Posts: 29
Joined: Wed Aug 18, 2010 11:59 pm

Re: [Solved] Forgotten Password

Post 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
Attachments
sample-sheet-protected-password-NON-working.ods
(10.5 KiB) Downloaded 692 times
sample-sheet-protected-password.ods
(8.9 KiB) Downloaded 760 times
error.jpg
"To err is human... by failing, we learn to create solutions, find better alternatives, and meet the challenges for the next err!"
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Forgotten Password

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
mahogan
Posts: 29
Joined: Wed Aug 18, 2010 11:59 pm

Re: [Solved] Forgotten Password

Post 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!
"To err is human... by failing, we learn to create solutions, find better alternatives, and meet the challenges for the next err!"
arnehe
Posts: 1
Joined: Sat Mar 09, 2013 1:13 pm

Re: [Solved] Forgotten Password

Post by arnehe »

I used following command to remove the protection in the content.xml file:

Code: Select all

sed -i 's/protected=\"true\"/protected=\"false\"/g' content.xml
LibreOffice 3.6.2.2, Ubuntu
Luis Guerrero
Posts: 1
Joined: Tue Aug 19, 2014 3:01 am

Re: [Solved] Forgotten Password

Post 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.
LibreOffice 4.2.3.3 under Linux Mint 17
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Forgotten Password

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
horrified
Posts: 2
Joined: Sat Sep 20, 2014 5:45 pm

Re: [Solved] Forgotten Password

Post 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?
OpenOffice 4
MAC 10.8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Forgotten Password

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
horrified
Posts: 2
Joined: Sat Sep 20, 2014 5:45 pm

Re: [Solved] Forgotten Password

Post 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...
OpenOffice 4
MAC 10.8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Forgotten Password

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
R-Easter
Posts: 1
Joined: Sat Oct 25, 2014 4:50 am

Re: [Solved] Forgotten Password

Post 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...
OpenOffice 3 on Windows 8
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Forgotten Password

Post 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
GiveMeMyDew
Posts: 2
Joined: Sat Apr 28, 2018 10:44 pm

Re: [Solved] Forgotten Password

Post 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

<table:table table:name="Sheet1" table:style-name="ta1" table:protected="true" table:protection-key="K7O251GdsyeKg54HeoSQ5ak3goc=" 
Apache Open Office 4.1.2
Windows 7

GiveMeMyDew
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Forgotten Password

Post 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

<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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply