Password Protection of SSRS reports

andrewchaines · August 15, 2019, 9:52am

Hi,

I am looking at purchasing Aspose Cells for Reporting Services, mainly for the functionality to password protect Excel Sheets distributed by email. I have tried out the evaluation software which works fine for me.

I was wondering if it possible to define dynamic/multiple passwords. We will be distributing reports to multiple customers and ideally wouldn’t want to use the same password for all of them. Is this possible?

Thanks

amjad.sahi · August 16, 2019, 8:19am

@andrewchaines,

Thanks for your query.

Could you elaborate your requirements and provide more details. Also provide details if this feature (define multiple passwords) is supported in MS Excel and how? You may provide some sample reports and screenshots to demonstrate your needs, we will check it soon.

andrewchaines · August 16, 2019, 8:41am

Hi Amjad,

To clarify. From what I can see, a single password can be defined per instance of reporting services (in the Aspose configuration file). This means that all Excel spreadsheets generated by reporting services using the Aspose distribution types have the same password.

I may wish to define different passwords on different report types and/or reports to different customers. I am exploring whether it is possible to achieve this. A single Excel spreadsheet would still only have a single password but I may want different spreadsheets to have different passwords.

Please let me know if you need further clarification.

Thanks

amjad.sahi · August 16, 2019, 10:17am

@andrewchaines,

Thanks for providing further details.

We can add new feature (we will log a ticket for it) to support defining different passwords on different reports and different export formats, please confirm if it suit your needs? But we cannot support defining multiple passwords to different customers.

andrewchaines · August 16, 2019, 10:44am

Hi,

This would work for me. Thanks for your assistance.

amjad.sahi · August 16, 2019, 11:28am

@andrewchaines,

Thanks for your confiramtion.

I have logged a ticket for your demanded feature with an id “CELLSRS-563” for your issue. The ticket is logged as following:
CELLSRS-563 - Define different passwords on different reports and different export formats

We will look into it soon.

Once we have an update on it, we will let you know.

amjad.sahi · August 22, 2019, 7:54am

@andrewchaines,

We have supported he feature. Could you give us details about your running environment, so that we could provide relevant DLL file for your needs.

andrewchaines · August 22, 2019, 8:17am

Hi Amjad,

We are running Reporting Services 2016. Version 13.0.5026.0.

Are there further details that you require?

amjad.sahi · August 22, 2019, 9:28am

@andrewchaines,

Thanks for the environment details.

Please try our latest version/fix: Aspose.Cells for Reporting Services v19.5.3 (attached).
Copy Aspose.Cells.ReportingServices.dll into ${SQL Server Reporting Service}\ReportServer\bin folder.

Add ExportedType parameter for Encryption and Protect in Aspose.Cells.ReportingServices.xml.
ExportedType : xls,xlsx,xlsb.

  <Encryption value="ON">
    <Report name="" >
      <Password value="123456"/>
      <EncryptionType value="Microsoft Strong Cryptographic Provider"/>
      <KeyLength value="128"/>
      <ExportedType value="xls" />
    </Report>
    <Report name="CELLSRS561_1" >
      <Password value="12345"/>
      <EncryptionType value="Microsoft Strong Cryptographic Provider"/>
      <KeyLength value="128"/>
    </Report>
  </Encryption>
<ProtectWorkbook value="ON">
    <Report name="CELLSRS561_2" >
      <Password value="23456"/>
      <ProtectType value="ALL"/>
      <ExportedType value="xls" />
    </Report>
    <Report name="CELLSRS561_1" >
      <Password value="2345"/>
      <ProtectType value="ALL"/>
    </Report>
  </ProtectWorkbook>
<ProtectWorkSheet value="ON">
    <Report name="CELLSRS561_1" >
      <Password value="23456"/>
      <ProtectType value="ALL"/>
      <ExportedType value="xls" />
    </Report>
    <Report name="CELLSRS561_1" >
      <Password value="2345"/>
      <ProtectType value="ALL"/>
    </Report>
 </ProtectWorkSheet>

Let us know your feedback.
ACRS2016.v19.5.3.zip (4.2 MB)

andrewchaines · August 23, 2019, 1:13pm

Hi,

I copies over the dll and updated the xml file as directed. When I went in to configure a subscription all of the Aspose document types had disappeared. Any idea?

Thanks

amjad.sahi · August 23, 2019, 2:36pm

@andrewchaines,

Please provide RDL, Aspose.Cells.ReportingServices.xml file and some screenshots to demonstrate the issue, we will check it soon.

amjad.sahi · August 27, 2019, 8:10am

@andrewchaines,

Also, please post SQL Server Reporting Services system log and reporting services configuration files (rsreportserver.config and rssrvpolicy.config) here. We need to check Aspose.Cells.ReportingServices.DLL whether it was loaded or not.

andrewchaines · August 27, 2019, 12:08pm

files.zip (209.9 KB)

I have attached everything asked for (I think). Please let me know if you require anything else.

ahsaniqbalsidiqui · August 27, 2019, 2:28pm

@andrewchaines,
Thank you for providing the required files. We will use them while analysing the issue.

amjad.sahi · September 6, 2019, 9:03am

@andrewchaines,

We checked the log file and found the error information is System.Security.Policy.PolicyException: Execution permission cannot be acquired.
Please check Aspose.Cells.ReportingServices.dll to run it rightly.
Please save aspose.cells.reportingservices.xml file and use ACRS.MSI.x64.19.5.4.zip to re-install Aspose.Cells for Reporting Services.

Let us know your feedback.

andrewchaines · September 6, 2019, 10:50am

So 19.5.4 works. I am just trying to configure the passwords and getting a bit stuck.

Using the XML provided earlier, the distribution method XLS - Excel Workboox via Aspose works fine and applies the password 123456. This only applies to this distribution method, Excel2007Xslx is the one I would use most often. How do I configure all reports of this type to apply a password. Also, I am unclear how to apply a password rule to a report. I have a report on SSRS - “Clinic Outstanding Sales Orders”. Do I enter this in the XML?

Would it be possible to get some more detailed instructions please?

amjad.sahi · September 6, 2019, 11:24am

@andrewchaines,

We will get back to you soon to provide more details/instructions for your needs.

amjad.sahi · September 11, 2019, 9:26am

@andrewchaines,

See the config/settings on how to apply a password for all reports of XLSX type when the value of report name is blank and the value of exported type is XLSX.

     <Report name="" >
      <Password value="23456"/>
      <ProtectType value="ALL"/>
      <ExportedType value="xlsx" />
    </Report>

See the sample settings on how to apply a password to all reports of supported protection type (XLS, XLSX, XLSB … ) when the value of report name is blank and the value of exported type is null.

    <Report name="" >
      <Password value="23456"/>
      <ProtectType value="ALL"/>
    </Report>

See the match rule when there are more reports configuration parameters:

Match priority is highest when the value of report name and exported type are not blank.
Match priority is 2nd when the value of report name is not blank and exported type is blank.
Match priority is 3rd when the value of report name is blank and exported type is not blank.
Match priority is 4th when the value of report name is blank and exported type is blank.

e.g.

    <Report name="" >
      <Password value="23456"/>
      <ProtectType value="ALL"/>
      <ExportedType value="xlsx" />
    </Report>
    <Report name="report1" >
      <Password value="12345"/>
      <ProtectType value="ALL"/>
      <ExportedType value="xlsx" />
    </Report>

Result:    
report1  of xlsx type to apply a password.    
report1  of xls type to apply no password.    
other reports of xlsx type to apply a password.    
other reports of other types to apply no password.

Hope, this helps a bit.

aspose.notifier · September 27, 2019, 11:19am

The issues you have found earlier (filed as CELLSRS-563) have been fixed in Aspose.Cells for Reporting Services v19.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi