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.

Thursday, September 20, 2012

How to recover user mailbox data from deleted mailboxes using Recovery Storage Group (RSG) in MS Exchange 2003 via

The Recovery Storage Group (RSG) is a feature of Exchange Server 2003. In certain situations it removes the requirement of building up an additional recovery server in order to recover mailbox data (to recover public folder data, the recovery server is still required). However, when we restore the backup into RSG, we are unable to use the ExMerge utility to extract data from the mailboxes that have been deleted from the original mailbox store.

Overview of the Recovery Storage Group
Prior to Exchange Server 2003, if you wanted to recover some mailbox data from the backup, you needed to set up a separate Active Directory (AD) forest on a recovery server and restore the backup into this recovery computer. With the RSG feature, this additional recovery server is not required to recover data from a mailbox store in scenarios where both the following conditions are true:

• The logical information in AD about the storage group and mailboxes remains intact and same as before the recovery. That's to say, the mailbox you want to recover must not be deleted or purged from the system, or moved to another mailbox store.
• You want to recover data from a single mailbox, a single database, or simultaneously a group of databases within a single storage group. The database added into RSG must be from a server within the same Administrative Group; furthermore, after we add a database into RSG, we can then only add databases from the same Storage Group.

After you create a RSG and add databases to it, you can either restore online backup sets or copy offline database files to the RSG (please note that enough disk space is necessary for the recovered database in RSG). Then you can use the Exchange Server 2003 version of the Microsoft Exchange Mailbox Merge Wizard (Exmerge.exe) utility or the Recover Mailbox Data Wizard to extract data from the recovered databases in the RSG to the mailbox in the original regular storage group.

How a Recovery Storage Group links back to the original regular storage group
The RSG uses the following two AD attributes to link back to the original regular storage group:

• The msExchMailboxGUID attribute (see Figure A). When a mailbox is created, this attribute is generated as a unique value that distinguishes it from all other mailboxes. The value remains the same for the lifetime of the mailbox. The ExMerge utility uses this attribute to match the mailbox in the RSG to the one in the original regular storage group. When you delete a mailbox, the mailbox attributes will be removed from the AD user account; when you purge a mailbox that has been deleted, the mailbox (and its GUID) is removed from the database. In these two scenarios, ExMerge will fail to extract data from this mailbox in the RSG.

Figure A: The msExchMailboxGUID attribute

• The msExchOrigMDB attribute (see Figure B). This attribute is set on a database in the RSG. It specifies the distinguished name of the original database where the RSG was created, and it will verify if the mailbox you want to recover is still located in the original mailbox store. If the mailbox has been moved to another mailbox store, ExMerge will fail to extract data from the mailbox.

Figure 2: The msExchOrigMDB attribute

So, what is the problem?
As described above, you will encounter problems when you try to use ExMerge to extract data from mailboxes in the RSG under the following situations:
• The mailbox has been moved to another mailbox store
• The mailbox has been deleted or purged from the system

You may encounter the following error message when you try to use ExMerge to retrieve a list of mailboxes from a RSG:
An error was encountered with one or more users when retrieving the list of mailboxes homed in the selected databases on server 'ServerName'. Please refer to the log file, 'ExMerge.log', for more information.
And from the ExMerge log, you may see the following error:
[14:13:08] Error! Cannot identify the user with the msExchMailboxGuid C\03\96f\5D\B1\3BD\81\99\1F\91C2\5B7. The legacyExchangeDN is /O=ORGNIZATION/OU=SITE/CN=RECIPIENTS/CN=USER.

Problem Resolution
Depending on the scenario, you can use different methods to recover the data:

Scenario 1: The mailbox has been moved to another database
In this scenario, we can use the following two methods to recover the data from the mailbox.
Resolution: Move the mailbox back to the original database or modify the msExchOrigMDB attribute
The most efficient method in this scenario is to move the mailbox back to the original database where the RSG was created. Then ExMerge should work fine to retrieve the mailbox list and to extract the data from RSG.
If you are unable to move the mailbox back for any reason, you can modify the msExchOrigMDB attribute on the RSG database and point it to the database that you moved the mailbox to.

To do this, please follow the steps below:
1. Start the ADSI Edit utility. This is included in the Windows 2000 and 2003
Support Tools: click Start -> Run, type adsiedit.msc and press Enter.
2. Locate the mailbox store that you moved the mailbox to. To do this, expand the
following the containers:
Configuration Container [YourServerName.YourDomainName.YourTopLevelDomain]
CN=Microsoft Exchange
CN=Administrative Groups
and then click CN=YourStorageGroup.
3. In the right pane, right-click the database object and click Properties.
4. Locate the distinguishedName attribute.
5. Right-click the value that is in the "Value(s)" box and click Copy.
6. Click Cancel.
7. Locate and click the RSG database object in the
CN=Configuration,DC=YourDomainName,DC=YourTopLevelDomain container.
8. In the right pane, right-click the RSG database object and click Properties.
9. Locate the msExchOrigMDB attribute.
10. Click Clear.
11. Right-click an empty area of the Edit Attributes box and click Paste.
12. Click Set and click OK.
13. Quit ADSI Edit.

For more information, refer to the following Microsoft TechNet article:
How to Change the msExchOrigMDB Attribute Using ADSI Edit

Scenario 2: The mailbox has been deleted from the database, but not yet purged
Resolution: Reconnect the deleted mailbox to the original or a new user account
When a mailbox is deleted but not yet purged, it becomes an orphaned mailbox in the Exchange server database and is available in the Deleted Mailbox Dumpster (before the retention time expires). Under this situation, you can reconnect the deleted mailbox to the original user account and then recover the data.
If you cannot reconnect the mailbox to the original user (for example, you have created another mailbox and associated it with the original user account), you can just create a new user account (with no mailbox enabled) and then reconnect the orphaned mailbox to this newly-created user. This will work because the msExchOrigMDB attribute does not change on the mailbox even if we associate it with a new user account.

Scenario 3: The mailbox has been purged (permanently deleted) from the database
Resolution: Create a new storage group, or modify the msExchMailboxGuid attribute on a user to point to the mailbox we want to recover data from

If you are running Exchange Server 2003 Enterprise Edition, you can create a new regular storage group and copy the recovered database from RSG to this new storage group. You can then directly extract the mailbox data from the new storage group. For more detailed information see the following Microsoft Knowledge Base article:
You receive an error message when you use the Exmerge tool

However, you cannot use this method on an Exchange Server 2003 Standard Edition because you are limited to one storage group in this edition. In this situation, you can directly modify the msExchMailboxGuid attribute on another mailbox to represent the one you want to recover (according to the principle of the msExchMailboxGuid attribute introduced above).

To do this, please follow the steps below:
1. The GUID shown in ExMerge log is in a different format than the actual
msExchMailboxGuid attribute. So first of all, you need to convert this GUID in
the ExMerge log into a format that contains 16 units with each unit having 2
hexadecimal numerals. Let's take C\03\96f\5D\B1\3BD\81\99\1F\91C2\5B7 as an

i. For units like C\ and 03\, only 1 or 2 characters, leave it as is.
ii. For units like 96f\, 3 characters, split into 2 units: 96 and f.
iii.For units like 91C2\, 4 characters, split into 3 units: 91, C and 2.
iv. Following the rule described above, convert the original GUID into:
C 03 96 f 5D B1 3B D 81 99 1F 91 C 2 5B 7.
v. Then, for each single character, replace it with its ASCII code. So the GUID
is: 43 03 96 66 5D B1 3B 44 81 99 1F 91 43 32 5B 37. (You can visit to check the ASCII code.)

2. Start the AD Users and Computers snap-in, and create a new user with the same name and same organizational unit (OU) as referred to in the ExMerge log.
3. Start the ADSI Edit snap-in: click Start menu -> Run, type adsiedit.msc and
press Enter
4. Expand the Domain container, and expand the OU container which stores the new user (for example, CN=Users).
5. Highlight the newly-created user account in step 2 and open its Properties.
6. Locate the msExchMailboxGuid attribute.
7. Click Clear.
8. Copy the converted GUID: 43 03 96 66 5D B1 3B 44 81 99 1F 91 43 32 5B 37.
9. Right-click an empty area of the Edit Attributes box and click Paste to override with "43 03 96 66 5D B1 3B 44 81 99 1F 91 43 32 5B 37" (without quotation marks).
10. Click Set and click OK.
11. Quit ADSI Edit.
12. Run ExMerge again and connect to the RSG. This time we should see the mailbox
we want to recover. Follow the ExMerge wizard to extract the data.

In Exchange Server 2003, there is a feature called Recovery Storage Group (RSG) which helps you to easily recover data (including messages, mailboxes or an entire database) without building up an extra recovery computer. However, due to the working mechanism of RSG, you cannot directly recover a deleted or purged mailbox. The above is some ways to workaround this limitation