Search code examples
sql-serverreporting-servicesreportpassword-protection

Is there a way to make a SSRS report password protected?

I have a report set up for a department. The data is accessed on a replicated server on the backend with a fixed password.

What I normally do is add a new role for the user accessing the report as a browser, then they can see the report and work from there.

However, the machine that is being used to access a new report is logged on under a general user account and neets to remain this way, the report that is being accessed is confidential and can only be accessed by certain staff members.

I have played around with the security settings to see if windows authentication would work but i struggled.

What I was hopping to achieve was the following: let a user access the report on the machine via a password to run the report (preferably their user log on detais even though the machine will be logged on under a different user).

If this is not possible, would it be possible to create a password protected report using parameters?

apologies If i am not clear, I am happy to explain further if needed.

Solution

  • I have come up with a work around.

    I have added the users I wanted to have access to the Security tab.

    Using powershell I have created a script to ask for credentials to open "internet explorer" as the user I want to have access.

    Start-Process -FilePath "C:\Program Files\internet explorer\iexplore.exe" http://PATHTOMYREPORT -Credential (Get-Credential)

    From here I then created a batch file that would execute this powershell script for me

    @ECHO OFF
PowerShell.exe -NoProfile -ExecutionPolicy Bypass -Command "& 'C:\powershellscript.ps1'"

    I then created a shortcut to the batch file on the desktop, in the shortcut options I set it to run as minimized so the batch file output wouldnt appear, i renamed the shorcut after my report. Gave it an inconspicuos icon.

    So what happens when someone double clicks the icon?

    they get asked for their organisation log on details in a neat window.

    enter image description here when they enter their details the report should open successfully in internet explorer provided they have been added as a report browser to the security element of the report as outlined by @B.Serbele.

    If a non intended user opens it, enters their details, it just crashes out.

    No doubt you may find issues with this, i.e. someone can access the C drive and delete the powershell script or shortcut etc etc. But its the best I could do with the set up we have at the moment.

    Hope it may help someone.