Thursday, October 4, 2012

How to un-protect Excel 2007 spreadsheets without knowing the password

When Microsoft introduced Excel 2007, they introduced new file types – we all know them by now: xlsx, xlsm, xltx, etc. These file types are often referred to as Open XML. That’s because the new file types are essentially packages that contain XML files. If you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file.

The new Open XML file types come with lots of benefits. One of the major benefits is that you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.

Well, while playing with the Open XML files, I discovered that you can remove spreadsheet protection simply by applying a simple edit to the xml within the Excel file.

Having said that, people do protect their excel worksheet for a variety of reasons, So what can we do if we need to amend something on this worksheet?
I decide that I want to un-protect this sheet, but I don’t know the password. Because this is Excel 2007, teh spreadsheet protection can be removed from within the XML.
The procedures below will show you have to go about doing this.

Step 1: Make a backup of your file in case you really monkey it up.
Step 2: Change the file extension to from filename.xlsx to
Step 3: Extract the contents of the zip file.
Step 4: Go to the extracted files and navigate to the xml for the target sheet (found in the ‘xl\worksheets’ directory)

Step 5: Open the target sheet’s xml document using an XML editor (You can use notepad and search for the field "sheetProtection") 
Step 6: Find the ‘sheetProtection’ tag and remove the entire line.

Step 7: Save the edited xml document and replace the old xml document found in the original zip file.
Step 8: Change the extension back to xlsx.
Step 9: Viola! The sheet is no longer protected :)

Wednesday, October 3, 2012

SCOM Agent for Workgroup not showing up on SCOM Console

In order to monitor a server that is in another domain/workgroup, a certificate will have to be imported to the server which the SCOM agent has been deployed. 

At times, you may wonder how is that with the certificate being imported into the server via MOMcertimport tool, the agent still do not show up under the pending management list of server on the SCOM console.
This usual has gotta do with the certificate.
To verify if the correct certificate is being used you may go to the below to check
  1. Log on to the computer with an account that is a member of the Administrators group.
  2. On the Windows desktop, click Start, click Run, type regedit, and then click OK.
  3. On the Registry Editor page, expand HKEY_LOCAL_MACHINE, expand SOFTWARE, expand Microsoft, expand Microsoft Operations Manager, expand 3.0, and then click Machine Settings.
  4. In the results pane, right-click ChannelCertificateSerialNumber
    (The value in this key should match the serial number of teh certificate in the reverse order)

    In any event that the value is wrong, you may choose to enter it manually else you can run the momcertimport tool again.