How to copy data from an Excel Spreadsheet protected by Information Rights Management (IRM).

Note: This applies to cases where you have editing privileges but no copy privileges. I haven’t tested other cases.

The enterprise edition of Office 365 contains a feature called “Information Rights Management” (IRM). It allows you to designate
edit/copy/view privileges on Office files for specific users (or groups) in your company. I honestly had no idea this existed until it caused me a minor problem:

I recently received a spreadsheet protected by this new system. The owner had given me (and only me) read and edit privileges but nothing else. But i needed to copy the data on the spreadsheet to some other places, and since the owner is kinda slow with answering e-mails i decided to take the matter into my own hands.

First, here is a list of things i tried that didn’t work and why:

  • Copying the data using CTRL+C or Right-Clicking:
    Couldn’t do it because the file is protected. Duh.
  • Removing the protection from the file using Excel:
    This method of protection seems to be completely different from those password locks you can put on spreadsheets. Excel doesn’t allow you to remove the protection, no matter what. Only the owner can do it.
  • Opening the .xlsx file with 7-zip to remove the protection:
    Some old guides show you a trick where you can open a protected spreadsheet with a zipping application to see its contents, change some stuff and disable the protection. If you open a file protected with IRM all you will see is a bunch of encrypted files. So, no dice.
  • Dumping the process memory and looking for strings:
    The resulting file was 0.5GB in size and searching for strings was taking too long. Also, the results came out all garbled and messy.

Ok, now here’s what worked: Running a macro that copies the data and saves it to a file. 

Seems silly that a copy-protected file doesn’t allow the user to copy the data but allows macros to read the data and save it anywhere, but ok…

Here’s what you have to do:

  1. Open up the “Developer” tab in ExcelImage result for excel 2016 macro editor
  2. Insert a Button, then right-click it and choose “Assign a Macro”Image result for excel 2016 macro editor
  3. Create a new macro for the button and copy-paste the code below:

     

    Sub Exfiltrate()

    Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer

    myFile = “C:\(full path to any folder)\test.csv”

    Set rng = Selection

    Open myFile For Output As #1

     

    For i = 1 To rng.Rows.Count

        For j = 1 To rng.Columns.Count

            cellValue = rng.Cells(i, j).Value

            If j = rng.Columns.Count Then

                Write #1, cellValue

            Else

                Write #1, cellValue,

            End If

        Next j

    Next i

     

    Close #1

    End Sub

     

  4. Save everything
  5. Select the cells you want to exfiltrate and finally click the button.

If everything worked, you should see a file in your selected folder with all the data that you selected in a csv format.

Conclusion:

I hope this helps you. Thank you for reading.
Also, use LibreOffice if you can.

Leave a Reply