locked
How to get control on reporting sevices security ? RRS feed

  • Question

  • Hi! I'm SQL Server sysadmin and local admin.

    In reporting services I neither see reports nor able to manage RS security trougt the web browser.

    How to get control on reporting sevices security ?

    The following querie :

    SELECT Users.UserName, Roles.RoleName, Roles.Description FROM Users INNER JOIN PolicyUserRole ON Users.UserID = PolicyUserRole.UserID INNER JOIN Roles ON PolicyUserRole.RoleID = Roles.RoleID

    Returns :

    <domain><domain><domain><Domain>\RTCR2ReportReaders </domain></domain></domain><domain><domain><domain>OCS Reports Browser </domain></domain></domain>

    <domain><domain><domain>BUILTIN\Administrators Content Manager </domain></domain></domain>

    <domain><domain><domain>NOVABASE\RTCUniversalServerAdmins OCS Reports Content Manager </domain></domain></domain>

    <domain><domain><domain><domain>\»Agile Operações Browser </domain></domain></domain>

    <domain><domain><domain>BUILTIN\Administrators System Administrator </domain></domain></domain>

    <domain><domain><domain>BUILTIN\Administrators Content Manager </domain></domain></domain>

    <domain><domain><domain>Everyone System User </domain></domain></domain>

    <domain><domain><domain>Best regards,</domain></domain></domain>

    <domain><domain><domain>Miguel</domain></domain></domain>

    
    Thursday, May 31, 2012 1:56 PM

All replies

  • Hi! I'm SQL Server sysadmin and local admin.

    In reporting services I neither see reports nor able to manage RS security trougt the web browser.

    What challenges you face to view yours reports in browser? As per my understanding you can manage report security through report manager(web application provided by reporting services)

    Try to avoid playing with report security with database queries.


    Thanks,
    Sandip Shinde(Blog:bi-bigdata.com|Twitter:@CloudBI_Sandip)

    Thursday, May 31, 2012 2:06 PM
  • What I see is :

    Thursday, May 31, 2012 3:04 PM
  • Hi Lumiga,

    Reporting Services uses role-based security to grant user access to a report server. For a report server that is configured for native mode, there are two types of roles:

    • Item-level roles are used to view, add, and manage report server content, subscriptions, report processing, and report history. Item-level role assignments are defined on the root node (the Home folder) or on specific folders or items farther down the hierarchy.
    • System-level roles grant access to site-wide operations that are not bound to any specific item. Examples include using Report Builder and using shared schedules. We assign System-level roles by clicking the “Site Settings” -> “Security” from the report manager.
      The two types of roles complement each other and should be used together.

    In this issue, I suggest that you refer to the troubleshooting steps below:

    1. Change the Service Account in Reporting Services Configuration Manager. If the current service account is Network Service, you can change it to a domain\user account and vice versa. This will automatically create a login for the service account in Database Engine.
    2. If the issue persists, please re-assign the System-level role and Item-level role on the report manager referring to the articles below:
      Setting System-Level Permissions on a Report Server
      Setting Item-Level Permissions on a Report Server

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Wednesday, June 6, 2012 2:50 AM
  • Hi! Mike,

    Thanks, your answer was great.

    Unfortunaly someting is missing in my side:

    what I did was :

    - Restore reporting services to a rs database before problem has happen.

    - Create a local account with admin previlegies named abc.

    - In reporting service configuration console I changed the domain account the local account abc (sucessfully).

    - Login in server with local account abc (sucessfully).

    - In SQL server managment studio I connected to the reporting services (sucessfully).

    - But the server properties are grey and didn't changed what I see in the browser, I also tried to connect with administartor account and I got the same grey ..

    - In reporting services configuration console only thing in red is the "web service identity"

    Any help whould be great,

    Regards,

    Lumiga.

    Friday, June 8, 2012 3:50 PM
  • Hi Lumiga,

    Thanks for your posting.

    From your description, the red X indicator next to the Web Service Identity indicates that there is a discrepancy between the actual Web service identity and the Web service identity information that is stored by the Reporting Services WMI provider. This discrepancy can occur if you modify the Web service identity settings in the configuration files. The Web Service Identity page shows the actual Web service identity in ASP.NET Service Account. In some cases, you can synchronize settings by clicking Apply.

    If that does not resolve the issue, choose a different application pool or click New to create a new application pool for the report server. You must click Apply after you specify the new application pool to save your changes.

    Regards,
    Mike Yin

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Sunday, June 10, 2012 9:06 AM
  • Hi! Mike

    I feel that we are closed to the solution... when I create new application I receive the following error:

    
    
    
    

    ReportServicesConfigUI.WMIProvider.WMIProviderException: A virtual directory must first be created before performing this operation. at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)

    Regards,

    lumiga

    Monday, June 11, 2012 2:06 PM
  • Hi! Mike,

    I'm so excited, we are very near from the problem solution.

    If I give local administrator previleges to the acccut IUSR_SRV109 I can see the reports but is not asked autentication, but is asked the password for the data source.

    If I remove the local admiminitrator previleges to the account IUSR_SRV109 I get the following error message on SSMS/report/security :

    
    

    Regards,

    Lumiga

    Thursday, June 14, 2012 4:39 PM
  • Hi! Mike

    Good news it's up and running.

    What I did was :

    - Give local administrator previliges to the account IUSR_SRV109.

    - Changed the account for service reporting services back to the original account

    - Restore the database.

    It's true that now is running, but ie account has high previliges, how to make this working without administrator previliges ?

    

    regards

    Lumiga

    Friday, June 15, 2012 11:14 AM
  • Hi Lumiga,

    Sorry for the delay and thank you for your feedback.

    From your description, it seems that you add the IUSR account to the Windows administrator group, right? If so, I suggest that you edit the user permission on the Web Site that hosts the report server and report manager virtual directories instead. Please refer to the steps below:

    1. Remove the IUSR account from the administrator group.
    2. Open IIS manager, right click on the Web Site that hosts the report manager virtual directory and click “Edit Permissions…”
    3. Click “Security” tab, click “Edit”, select the “IIS_IUSRS” and check the “Full Control” box under “Allow” column.
    4. Click “OK”.

    Meanwhile, you can also refer to the following thread which has a similar topic:
    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/757f4ed3-1372-4f78-8eea-9d4a863c1c8e

    Regards,
    Mike Yin

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Friday, June 15, 2012 12:26 PM
  • Hi! Mike

    1. OK

    2. I have "Permissions.." not "Edit permissions.."

    3. I don't have the account "IIS_IUSRS"

    Regards

    lumiga.

    Friday, June 15, 2012 4:40 PM
  • Hi Lumiga,

    Thanks for your posting.

    Which version of IIS are you using? The steps above are based on IIS7. If you click the “Permission…” option, does it open the Properties window of the website’s virtual directory? If so, is there a user group or user name related to “IUSR” when editing the permissions? If there is, please assign “Full Control” permission to “IUSR” and check the result.

    Regards,
    Mike Yin

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Wednesday, June 20, 2012 1:36 AM