Enabling Reporting Based on Parameters in Reporting Services

Question

Suppose we have a report called SalesSummary for a large department. This department has many smaller teams for each product. People should be able to see information about their own product, and not about the products of other teams. We also have one domain group for each of these teams.

Copying a SalesSummary report for each team and setting permissions is not the best option, since we have many products. I thought to use code similar to below on RS, but it does not work. Apparently System.Security.Principal.WindowsPrincipal is disabled by RS by default.

Public Function isPermitted() As Boolean Dim Principal As New System.Security.Principal.WindowsPrincipal(System.Security.Principal.WindowsIdentity.GetCurrent()) If (Principal.IsInRole("group_prod")) Then Return true Else Return false End If End Function 

I also thought that I could send the user ID from RS to the SQL server, and inside my SP I can use the code similar to below to query the active directory. This also does not work due to security restrictions.

 SELECT * FROM OPENQUERY(ADSI,'SELECT cn, ADsPath FROM ''LDAP://DC=Fabricam,DC=com'' WHERE objectCategory=''group''') 

Is there an easier way to achieve this?

Thanks for the help!

Answer 1

The first option you proposed (using the built-in code to identify the executing user) will not be reliable. SSRS code is not necessarily executed as the user accessing the report, and may not have access to these user credentials, for example, when starting a subscription.

Your second approach will work, but to allow your SQL Server service account, you need the appropriate permissions to query Active Directory.

Another approach is to keep a copy of the group membership or user permissions in the SQL table. This table can be updated manually or using an automated process. Then you can easily include this in both the available options and the main data queries.

Answer 2

So, I ended up with this code:

  PrincipalContext domain = new PrincipalContext(ContextType.Domain, "AD"); UserPrincipal user = UserPrincipal.FindByIdentity(domain, identityName); //// if found - grab its groups if (user != null) { PrincipalSearchResult<Principal> _groups = null; int tries = 0; //We have this while because GetGroups sometimes fails! Specially if you don't // mention the domain in PrincipalContext while (true) { try { _groups = user.GetGroups(); break; } catch (Exception ex) { logger.Debug("get groups failed", ex); if (tries > 5) throw; tries++; } } // iterate over all groups, just gets groups related to this app foreach (Principal p in _groups) { // make sure to add only group principals if (p is GroupPrincipal) { if (p.Name.StartsWith(GROUP_IDENTIFIER)) { this.groups.Add((GroupPrincipal)p); this.groupNames.Add(p.Name); } } } } 

Now that you have a list of related groups, you can check the list to authorize the user!