Summary
When deploying an SSRS report to a report server, the developer chooses what folder the report should reside in and what security should be assigned to it. Access (and visibility) to the report can be controlled by giving individual users or Active Directory groups various types of roles, such as Browser, Content Manager, View Reports, etc.
It would seem logical, then, for the developer to expect to be able to filter the report's data according to rules defined at the group level. However, though SSRS will provide the user's login ID, that ID's group membership is not provided by SSRS, and further, it is actively discouraged by making code calls to query Active Directory fail. (See Detail section for further information.) One workaround is through the use of linked servers, but this is also not available in some organizations.
Solution
A Report Processes SSIS package is created, which contains two separate and distinct child packages. The first builds a list of available reports by querying the department's SharePoint site, which is now defined as the "source of truth" for the inventory of reports available. The second package queries one of two intermediary tables for all distinct user groups that may have filtered access to any report. For each of those groups, a table is filled with user IDs of people who are members of the group. The parent package runs each morning.
In the design of the report, the developer can use the Report ID from the SharePoint list table to link to rows in an intermediary table that tells the report what groups have access to what data. That table is then used to filter the resulting report.
Detail
See the accompanying pages for a detailed flow chart and description of how each process works, and how the process may be used and maintained by developers.
Each morning, two SSIS packages execute, one rebuilding the list of TRM Reports from the SharePoint site (our "source of truth"), the other populating a table for each Active Directory Group that has any report filtering, with all the User IDs that are members.
When developing a new report, the developer or the SharePoint site administrator will create a New Item in the TRM Reporting list, and the developer will need to note the Report ID
For each group that will have filtered access to the report, create a row in the SSRS_Report_Group table, using the Report ID and the Group Name
For each row created in step 3, create a row (or rows) in the SSRS_Parameters table with the value for the column filtered on; if a group has multiple values, create one row for each, repeating the same Report Group ID
If any group in step 3 has not previously had reports which were filtered, the Group Members table will need to be updated with group members, either by waiting for the next daily SSIS package execution, or if circumstances warrant more immediate response, by manually executing the SSIS package
The developer will need to construct the report similar to the example shown below:
First, create an internal report parameter called UserID, defined by the following expression: =Mid(User!UserID,4,Len(User!UserID)-3) (this removes the "MS\" from the beginning of the User ID as returned from SSRS' Built-in Fields)
The Segment name is the filtered column, so a report dataset called Segment is created with the following SQL and sample results:
Then, the report's main dataset includes this in the WHERE clause of its query:
If the report banner will display the user's filter elements, use the JOIN as in this example: ="User ID " + Parameters!UserID.Value + " viewing Segments " + Join(Parameters!Segment.Label, ", ") The resulting report banner looks like this:
SSIS Package Notes – Transfer SharePoint list of reports to SQL Server: Current releases of SQL Server and Visual Studio include SharePoint List Source and Destination objects. For 2008, these can be added from the website http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 Here are the properties for the SharePoint List source, with the key values circled in red:
SSIS Package Notes – Get Members of Groups Listed in Report Filtering Table (SSRS_Report_Group): The important processing is done in a script task in which each group's members are gathered into a table, and a stored procedure invoked which accepts a table parameter.
The script task code:
public void Main()
{
Variables vars = null;
try
{
Dts.VariableDispenser.LockForRead("User::GroupName");
Dts.VariableDispenser.LockForRead("User::ADOConnectionString");
Dts.VariableDispenser.LockForWrite("User::RowsAdded");
Dts.VariableDispenser.GetVariables(ref vars);
String GrpName = vars["User::GroupName"].Value.ToString();
GrpName = GrpName.TrimEnd();
String ConnectionString = vars["User::ADOConnectionString"].Value.ToString();
DataTable MyTempTable = new DataTable();
MyTempTable.Columns.Add("UserID", typeof(string));
MyTempTable.Columns.Add("UserGroup", typeof(string));
SearchResult result;
DirectorySearcher search = new DirectorySearcher();
search.Filter = String.Format("(cn={0})", GrpName);
search.PropertiesToLoad.Add("member");
result = search.FindOne();
if (result != null)
{
for (int counter = 0; counter <
result.Properties["member"].Count; counter++)
{
string user = (string)result.Properties["member"][counter];
user = user.Substring(3, user.IndexOf(",") - 3);
MyTempTable.Rows.Add(user, GrpName);
Console.Write(counter);
}
}
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.usp_InsertSSRSGroupMembers";
SqlParameter inParam = cmd.Parameters.AddWithValue("@TableVariable", MyTempTable);
inParam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
conn.Close();
vars.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}The stored procedure:
/****** Object: StoredProcedure [dbo].[usp_InsertSSRSGroupMembers] Script Date: 5/2/2013 11:20:46 AM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_InsertSSRSGroupMembers]
(
@TableVariable SSRS_GroupMembersTableType READONLY
)
AS
BEGIN
INSERT INTO SSRS_GroupMembers
(
UserID, UserGroup
)
SELECT
UserID, UserGroup
FROM
@TableVariable
END
GOThe User-Defined Table Type:
/****** Object: UserDefinedTableType [dbo].[SSRS_GroupMembersTableType] Script Date: 5/2/2013 11:22:32 AM ******/CREATE TYPE [dbo].[SSRS_GroupMembersTableType] AS TABLE(
[UserID] [varchar](50) NOT NULL,
[UserGroup] [varchar](50) NOT NULL
)
GOThe SSRS Error I received when trying to add Custom Code to query Active Directory:
[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox23.Paragraphs[0].TextRuns[0]’ contains an error: Request for the permission of type 'System.DirectoryServices.DirectoryServicesPermission, System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' failed.
This explanation was found in the SQL Server Forums of Microsoft's MSDN:
After reproduced the case, the root cause is found that the Report Service use the execution account to access the domain. To solve the issue, we can set the "Execution Account" in the page "Execution Account" in Reporting Service Configuration Manager to be a domain user. For more information about Execution Account, please see Execution Account (Reporting Services Configuration) in SQL Server Books Online: http://msdn.microsoft.com/en-us/library/ms181156.aspx
Changing the Reporting Services Configuration in a large organization is not practical, and more than likely is contrary to IT security policies.
If you have any more questions, please feel free to ask.
Thanks,
Jin
Jin Chen - MSFT
If you have questions or comments, please add them to the discussion for this article.
