FIM Data Processing Extension for SSRS

User Guide

 

Prepared For:

FIM Community

Revision:

1.0

Completed:

11-3-2011

image002

image

 

Publication Note 

The FIM Data Processing Extension (DPE) and this guide are published under the Microsoft Public License (MS-PL), http://www.opensource.org/licenses/MS-PL.

Acknowledgments

Craig Martin is happy to have the time to work on this stuff thanks to his employer, Edgile.

Joe Zamora would like to thank his employer, Ensynch. He would also like to give special thanks to his former mentor, Brad Turner, without whose guidance and vision this latest software version would not exist.

Document History

Version

Date

Updated By

Comments

Status

1.0

11/1/2010

Craig Martin, Edgile

1.1

1/14/11

Joe Zamora, Ensynch

1.2

11/3/2011

Joe Zamora, Ensynch

 

Table of Contents

1 Installing & Configuring the Custom Data Processing Extension in Reporting Services

1.1 Prerequisites

1.2 Preparing the server

1.3 Deploying the software

1.4 Configuration file modifications

1.4.1 RSReportServer.config

1.4.2 RSSrvPolicy.config

1.4.3 log4net.config

1.5 Restart SQL Server Reporting Services

2 Installing & Configuring the Custom Data Processing Extension in Visual Studio

2.1 Prerequisites

2.2 Preparing the server

2.3 Deploying the software

2.4 Configuration file modifications

2.4.1 RSReportDesigner.config

2.4.2 log4net.config

3 Troubleshooting

3.1 Review the SSRS logs

3.2 Review the log4net logs

4 Connection strings

5 Queries

Introduction

FIM 2010 lacks reporting functionality such as report builders, storage and distribution.

Strong partner offerings are available for purchase that supply in depth solutions to this feature gap.

This project provides a free-ish solution based on SQL Server Reporting Services (SSRS). Think of this as a stepping stone to more mature offerings. If you are willing to learn a bit about SSRS then this project will enable you to connect SSRS to the FIM 2010 web service without any intermediary data loading. SSRS can use the assembly produced by this project to connect to FIM directly, making the FIM web service look like just another SQL database table for reporting purposes.

1  Installing & Configuring the Custom Data Processing Extension in Reporting Services

The FIM Data Processing Extension must be installed on the SSRS server that will host the reports for FIM.

The following sections describe how to deploy the FIM DPE to SSRS. Here are the high-level steps:

  1. Copy the FIM DPE to the SSRS ReportServer\bin folder
  2. Copy the FIM Client to the SSRS ReportServer\bin folder
  3. Copy reference libraries to the SSRS ReportServer\bin folder
  4. Copy the log4net.config file to the SSRS ReportServer folder
  5. Modify the RSReportServer.config file
  6. Modify the RSSrvPolicy.config file
  7. Restart the SSRS service

SSRS looks in this bin folder for the DPE assembly, and any assembly references. This is a configuration choice, as SSRS is able to locate assemblies elsewhere but for the purposes of this guide we drop them to the SSRS bin folder.

Perform these steps on each server in the scale-out deployment.

1.1  Prerequisites

Please have available the following items/information:

  • Software, packaged into the ZIP file
  • SSRS Install location, e.g.

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services

1.2  Preparing the server

  1. Create backups of the following files:
  • <SSRS install path>\ReportServer\RSReportServer.config
  • <SSRS install path>\ReportServer\RSSrvPolicy.config

1.3  Deploying the software

1. Unzip the ZIP file to a memorable location.

2. Edit the “deployDPE - SSRS.bat” file with the following changes:

a. Locate the path to Reporting Services, and update it to match your path. For example:

set RS=C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services

b. Locate the instance name, and update it to match yours. For example:

net stop "SQL Server Reporting Services (MSSQLSERVER)"

net start "SQL Server Reporting Services (MSSQLSERVER)"

Note: If you redeploy the software, consider commenting the following line that overwrites the logging config file.

 

REM xcopy "%DPE%\log4net.config" "%RS%\ReportServer\" /i /r /y /k /c /f

3. Save and close the BAT file.

4. Open an Administrator Command Prompt.

image003

 

5. Run the BAT file from the Administrator Command Prompt.

a. Pay close attention to any errors from the command line window.

b. If you see “Access denied” errors for the copy operations, you must grant the installer account “Full control” permissions to <SSRS install path>.

If you see the following errors at the end of the command line, you’ll have to restart the SQL Server Reporting Service (<InstanceName>) manually.

System error 5 has occurred.

Access is denied.

You can restart the SQL Server Reporting Service (<InstanceName>) with Administrative Tools à Services:

image004

1.4  Configuration file modifications

After the software is deployed to Reporting Services, you need to make some modifications to the configuration files.

Note: You can find sample config files in the “Deploy\Sample Config” folder. 

 

1.4.1  RSReportServer.config

1. Open the <SSRS install path>\ReportServer\RSReportServer.config file with Visual Studio or a simple text editor such as Notepad.

2. Locate the <Extensions><Data> element and paste the following line before the closing </Data> tag:

<Extension Name="FIMSERVICE" Type="FimDataProcessingExtension.FimConnection,FimDataProcessingExtension"/>

1.4.2  RSSrvPolicy.config

You will need to add a code group for the various DLLs that comprise the custom security extension, to grant FullTrust permission for the extension. You do this by adding the code group to the RSSrvPolicy.config file.

ALWAYS BACK THIS FILE UP BEFORE MAKING CHANGES!!! In my experience this is the most difficult file to troubleshoot because Code Access Security makes me want to kick my cat (and I really do like my cat).

1. Open the <SSRS install path>\ReportServer\RSSrvPolicy.config file.

2. Add the following <CodeGroup> element immediately after the existing code group that has a membership condition with Url="$CodeGen$/*" (shown below).

<CodeGroup

 class="UnionCodeGroup"

 version="1"

 PermissionSetName="FullTrust">

 <IMembershipCondition

  class="UrlMembershipCondition"

 version="1"

 Url="$CodeGen$/*"

 />

</CodeGroup>

<CodeGroup

 class="UnionCodeGroup"

 version="1"

 PermissionSetName="FullTrust"

 Name="Ensynch_Strong_Name"

 Description="This code group grants Ensynch code full trust. ">

<IMembershipCondition

 class="StrongNameMembershipCondition"

 version="1"

 PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100A339C664ACC02EA7AEFBA7455A24B21A11AB2B4275AA597A02AFBDB3EB397050DB9E728A735DEFF1F45982B697D75E9593B50E6FC10CECD390B20A4C9FAC3AD614193E92DAD1B9CD25C0E0043C39DCCBEA6474B59B27DA829E46332C65234CC96A91CBCC48513F5BA28599DC147B8C84136B8D1D81D7F1062D711F3CAFDAB2AA"

 />

</CodeGroup> 

1.4.3  log4net.config

There is an extra configuration file for logging:

<SSRS install path>\ReportServer\log4net.config

Consider the following changes for this file.

1. Set the threshold to a value appropriate for your deployment for each appender.

<!-- Options are ALL, DEBUG, INFO, WARN, ERROR, FATAL, OFF. -->

<threshold value="INFO" />

For example, “ALL” or “DEBUG” may be appropriate for the Dev log file appender, but it should be reduced at least to “INFO” for Production.

2. Change the paths to the log files. Note that the very last part of the file value is actually the prefix of the log file. So, for the settings below, the log file names will look like this:

log_2010_01_01.txt

log_2010_01_02.txt

<file value="C:\Logs\FimDataProcessingExtension\log" />

<appendToFile value="true" />

<!--RollingLogFileAppender-->

<rollingStyle value="Composite" />

<datePattern value="_yyyy-MM-dd&quot;.txt&quot;" />

<maxSizeRollBackups value="1" />

<maximumFileSize value="1MB" />

<staticLogFileName value="false" /> 

3. Make sure to give the Reporting Services service account permissions to the log file folder.

4. Finally, remove the console appender from the <root> element, since it’s not needed.

<appender-ref ref="ConsoleAppender" />

1.5  Restart SQL Server Reporting Services

Restart the SQL Server Reporting Service (<InstanceName>).

2  Installing & Configuring the Custom Data Processing Extension in Visual Studio

In order to build reports with the DPE in SQL Server Business Intelligence Development Studio (BIDS, aka Visual Studio), you must install the DPE in the Visual Studio path.

The following sections describe how to deploy the FIM DPE to Visual Studio (VS). Here are the high-level steps:

  1. Copy the FIM DPE to the VS Common7\IDE\bin folder
  2. Copy the FIM Client to the VS Common7\IDE\bin folder
  3. Copy the log4net.config file to the Common7\IDE folder.
  4. Modify the RSReportDesigner.config file

BIDS looks in the VS Common7\IDE\bin folder for the DPE assembly, and any assembly references. This is a configuration choice, as SSRS is able to locate assemblies elsewhere but for the purposes of this guide we drop them to this folder.

2.1  Prerequisites

Please have available the following items/information:

  • Software, packaged into the ZIP file
  • Visual Studio location, e.g.

C:\Microsoft Visual Studio 9.0\Common7\IDE

2.2  Preparing the server

1. Create a backup of the following file:

o <VS install path>\PrivateAssemblies\RSReportDesigner.config

2.3  Deploying the software

1. Unzip the ZIP file to a memorable location.

2. Edit the “deployDPE - VS.bat” file with the following changes:

a. Locate the path to Visual Studio, and update it to match your path. For example:

set VS=G:\Microsoft Visual Studio 9.0\Common7\IDE

Note: If you redeploy the software, consider commenting the following line that overwrites the logging config file.

 

REM xcopy "%DPE%\log4net.config" "%VS%" /i /r /y /k /c /f

3. Save and close the BAT file.

4. Double-click the BAT file to run it.

a. Pay close attention to any errors from the command line window.

2.4  Configuration file modifications

After the software is deployed to Visual Studio, you need to make some modifications to the configuration files.

Note: You can find sample config files in the “Deploy\Sample Config” folder. 

 

2.4.1  RSReportDesigner.config

1. Open the <VS install path>\PrivateAssemblies\RSReportDesigner.config file with Visual Studio or a simple text editor such as Notepad.

2. Locate the <Extensions><Data> element and paste the following line before the closing </Data> tag:

<Extension Name="FIMSERVICE" Type="FimDataProcessingExtension.FimConnection,FimDataProcessingExtension" />

3. Locate the <Extensions><Designer> element and paste the following line before the closing </Designer> tag:

<Extension Name="FIM" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/>

2.4.2  log4net.config

There is an extra configuration file for logging:

<VS install path>\log4net.config

Consider the following changes for this file.

1. Set the threshold to a value appropriate for your deployment for each appender.

<!-- Options are ALL, DEBUG, INFO, WARN, ERROR, FATAL, OFF. -->

<threshold value="INFO" />

For example, “ALL” or “DEBUG” may be appropriate for the Dev log file appender, but it should be reduced at least to “INFO” for Production.

2. Change the paths to the log files. Note that the very last part of the file value is actually the prefix of the log file. So, for the settings below, the log file names will look like this:

log_2010_01_01.txt

log_2010_01_02.txt

<file value="C:\Logs\FimDataProcessingExtension\log" />

<appendToFile value="true" />

<!--RollingLogFileAppender-->

<rollingStyle value="Composite" />

<datePattern value="_yyyy-MM-dd&quot;.txt&quot;" />

<maxSizeRollBackups value="1" />

<maximumFileSize value="1MB" />

<staticLogFileName value="false" /> 

3  Troubleshooting

Most of the troubleshooting occurs during the initial deployment. Once the FIM DPE is deployed and simple reports are working, then I find I spend most of my time refining the XPath queries and the report layouts.

3.1  Review the SSRS logs

The SSRS logs are located in a folder named “Reporting Services\LogFiles”. The log files in that folder contain details of SSRS loading the FIM DPE. The get the detail of the assembly load, you must first use the SSRS report manager to attempt to create a data source using the FIM DPE.

Report Manager à Home à New Data Source à BANG!!!

Until you click on “New Data Source” you will not see details of the DPE loading in the SSRS logs.

3.2  Review the log4net logs

You’ll find the log4net log files in the location that you configured above (default location is C:\Logs\FimDataProcessingExtension). The log files may be configured to include verbose (aka DEBUG) messages, which can help you debug any issues you may experience.

[BEGIN LOG]

2011-01-14 08:54:37,790 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

 [parseConnectionString] Connection string:

 Data Source=http://fim:5725;initial catalog=ResourceManagementService;fimServicePrincipalName=FIMTEST\svc_fimws;integrated Security=true;

 

 

2011-01-14 08:54:37,790 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

 [parseConnectionString] result:

 Data Source             = http://fim:5725

 initial catalog         = ResourceManagementService

 fimServicePrincipalName = FIMTEST\svc_fimws

 integrated Security     = true

 

 

 

2011-01-14 08:54:37,791 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

 [set_IDbConnectionExtension.IntegratedSecurity] Set integratedSecurity = True

 

 

2011-01-14 08:54:37,792 --Rendering-- DEBUG [Ensynch.FIM.FimTools]

 

 

 [Microsoft.ReportingServices.DataProcessing.IDbConnection.Open] Current user info:

 Name               : FIMTEST\joe.zamora

 IsAuthenticated    : True

 AuthenticationType : Kerberos

 ImpersonationLevel : None

 SID                : S-1-5-21-3123059310-1379428633-3534431446-14588

 

 

2011-01-14 08:54:37,792 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

 [IDbConnection.Open] Opening client connection

 fimServiceUrl = http://fim:5725/ResourceManagementService

 fimServicePrincipalName = FIMTEST\svc_fimws

 

 

2011-01-14 08:54:37,961 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

 [IDbConnection.Open] Using Windows integrated security.

 

 

2011-01-14 08:54:37,961 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

 [IDbConnection.Open] Successfully opened connection.

 Total elapsed time: 00:00:00.1700170

 

 

 

2011-01-14 08:54:37,963 --Rendering-- DEBUG [Ensynch.FIM.FimTools]

 

 

[Microsoft.ReportingServices.DataProcessing.IDbConnection.Open] Current user info:

Name               : FIMTEST\joe.zamora

IsAuthenticated    : True

AuthenticationType : Kerberos

ImpersonationLevel : None

SID                : S-1-5-21-3123059310-1379428633-3534431446-14588

 

 

2011-01-14 08:54:37,973 --Rendering-- DEBUG [Ensynch.FIM.FimQuery]

 

 

[parseAttributeString] Successfully completed.

Total elapsed time: 00:00:00.0090009

 

 

 

2011-01-14 08:54:37,973 --Rendering-- DEBUG [Ensynch.FIM.FimQuery]

 

 

[parseQueryFast] Successfully completed.

Total elapsed time: 00:00:00.0090009

 

 

 

2011-01-14 08:54:37,973 --Rendering-- DEBUG [Ensynch.FIM.FimQuery]

 

 

[parseQueryFast]

SELECT

stID AS [Team ID]

,DisplayName AS [Display Name]

,ObjectID AS [Resource ID]

,Description AS [Description]

,Creator

,CreatedTime AS [Created Time]

,acAssignedAltList

,fcAssignedAltList

,acAssigned

,ecAssignedList

,fcAssigned

FROM /superTeamAssignment[stID = #foobar]

 

 

 

2011-01-14 08:54:37,974 --Rendering-- DEBUG [FimDataProcessingExtension.FimCommand]

 

 

[IDbCommand.ExecuteReader] Successfully completed.

Total elapsed time: 00:00:00.0110011

 

 

 

2011-01-14 08:54:37,974 --Rendering-- DEBUG [Ensynch.FIM.FimQuery]

 

 

[getObjectType] Successfully determined object type = superTeamAssignment

 

 

2011-01-14 08:54:38,055 --Rendering-- DEBUG [Ensynch.FIM.FimQueryResult]

 

 

[createTable]

Team ID : System.Int32

Display Name : System.String

Resource ID : System.String

Description : System.String

Creator : System.String

Created Time : System.DateTime

acAssignedAltList: System.String

fcAssignedAltList: System.String

acAssigned : System.String

ecAssignedList : System.String

fcAssigned : System.String

 

 

2011-01-14 08:54:38,056 --Rendering-- DEBUG [Ensynch.FIM.FimQueryResult]

 

[get_Enumerator] Calling DefaultClient.Enumerate:

/superTeamAssignment[stID = #foobar]

 

 

2011-01-14 08:54:38,073 --Rendering-- ERROR [FimDataProcessingExtension.FimDataReader]

 

 

[IDataReader.Read]

 

Microsoft.ResourceManagement.Client.Faults.CannotProcessFilterException: Client call failed; check your XPath syntax: cannot filter as requested

/superTeamAssignment[stID = #foobar] ---> System.ServiceModel.FaultException: cannot filter as requested

at Microsoft.ResourceManagement.Client.ClientHelper.HandleFault(Message message)

at Microsoft.ResourceManagement.Client.WsEnumeration.WsEnumerationClient.Enumerate(EnumerationRequest request)

--- End of inner exception stack trace ---

at Microsoft.ResourceManagement.Client.WsEnumeration.WsEnumerationClient.Enumerate(EnumerationRequest request)

at Microsoft.ResourceManagement.Client.EnumerationResultEnumerator.MoveNext()

at Ensynch.FIM.FimQueryResult.Read()

at FimDataProcessingExtension.FimDataReader.Microsoft.ReportingServices.DataProcessing.IDataReader.Read()

 

2011-01-14 08:54:38,076 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

[IDbConnection.Close] called from [Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.Process]

 

 

2011-01-14 08:54:38,076 --Rendering-- DEBUG [FimDataProcessingExtension.FimConnection]

 

 

[IDbConnection.Close] Disposing defaultClient.

 

[END LOG]

4 Connection strings

The FIM DPE uses a connection string to find the FIM web service, and to determine how to authenticate. In its simplest form, credentials can be embedded in the connection string. The table below shows several sample connection strings. For example, the first connection string works from a non-domain joined machine (verifies we are not relying on the logged on user).

Stored credentials

Data Source=http://fim:5725;initial catalog=ResourceManagementService;fimServicePrincipalName=FIMTEST\svc_fimws;integrated Security=false;user=FIMTEST\joe.zamora;password=Pass@word1

Stored Windows credentials

Data Source=http://fim:5725;initial catalog=ResourceManagementService;fimServicePrincipalName=FIMTEST\svc_fimws;integrated Security=true;user= FIMTEST\guinea.pig;password=Pass@word1

Windows integrated

Data Source=http://fim:5725;initial catalog=ResourceManagementService;fimServicePrincipalName=FIMTEST\svc_fimws;integrated Security=true;

5 Queries

The FIM DPE uses a pseudo-SQL syntax for queries, where the table name is replaced by an XPath query.

Note: In the near future, a new version of the FIM Query Tool will be released. This version will have the same query engine as the FIM DPE, and will thus help you build some of the syntax for FIM DPE queries.

The following table shows examples of valid queries, in order of increasing complexity.

Description

Query sample

All users

Note: By default, all attributes are returned for XPath-only queries.

Note: XPath queries must use the case-sensitive system name of the FIM resource type.

/Person

All failed requests

Note: XPath predicates must use the case-sensitive system name of the FIM attribute.

/Request[not(RequestStatus = 'Completed')]

All requests whose status is determined at runtime with a report parameter

Note: Report parameter is highlighted here.

/Request[not(RequestStatus = '%status')]

Users with non-empty Info attribute

Note: Wildcards must be escaped with a backslash. This convention is specific to the FIM DPE, and is not valid FIM XPath dialect.

/Person[starts-with(Info, '\%')]

All attributes bound to the Person object

/BindingDescription[BoundObjectType = /ObjectTypeDescription[Name = 'Person']]/BoundAttributeType

All users

SELECT

*

FROM /Person

First ten users

SELECT TOP 10

*

FROM /Person

First ten users in order of display name

Note: The ORDER BY clause must use the case-sensitive name of the FIM attribute.

SELECT TOP 10

*

FROM /Person

ORDER BY DisplayName

First ten users in order of display name (same as above)

SELECT TOP 10

*

FROM /Person

ORDER BY DisplayName ASC

Last ten users in order of display name

SELECT TOP 10

*

FROM /Person

ORDER BY DisplayName DESC

Only certain attributes for all users

SELECT

AccountName

,DisplayName

,Domain

,ObjectID

FROM /Person

Same as above, but with human-readable column names

SELECT

AccountName AS [Account Name]

,DisplayName AS [Display Name]

,Domain AS [Domain]

, ObjectID AS [Resource ID]

FROM /Person

Certain details for MPRs

Note: By default, reference-type attributes (like ResourceFinalSet shown here) are returned as GUIDs.

SELECT

DisplayName AS [Display Name]

,ManagementPolicyRuleType AS [Management Policy Rule Type]

,Disabled AS [Disabled]

,ActionType AS [Action Type]

,GrantRight AS [Grant Right]

,ResourceFinalSet AS [Resource Final Set]

,ActionWorkflowDefinition AS [Action Workflows]

FROM /ManagementPolicyRule

Same as above, but reference-type attributes are dereferenced, i.e. their display names are returned.

SELECT

DisplayName AS [Display Name]

,ManagementPolicyRuleType AS [Management Policy Rule Type]

,Disabled AS [Disabled]

,ActionType AS [Action Type]

,GrantRight AS [Grant Right]

,ResourceFinalSet.DisplayName AS [Resource Final Set]

,ActionWorkflowDefinition.DisplayName AS [Action Workflows]

FROM /ManagementPolicyRule

Top 20 ‘Create Person’ requests, in descending order of created time, including certain attributes of the Target object

Note: The FIM DPE doesn’t support a JOIN clause, but you can use the syntax shown here to ‘look through’ reference-type attributes and get their child attributes.

SELECT TOP 20

CreatedTime AS [Created Time]

,DisplayName AS [Display Name]

,Target.DisplayName AS [Target Display Name]

,Target.AccountName AS [Target Account Name]

,Target.EmployeeType AS [Target Employee Type]

,RequestStatus AS [Request Status]

,RequestParameter AS [Request Parameters]

FROM /Request[starts-with(DisplayName, 'Create Person')]

ORDER BY CreatedTime DESC

EREs for a certain outbound sync rule (for groups in this case), including the group membership of the synchronized object

Note: This example could allow you to pick out groups that have members and have been synchronized to AD.

SELECT

DisplayName AS [Display Name]

,ResourceParent.DisplayName AS [Resource Parent]

,ResourceParent.ExplicitMember

,SynchronizationRuleID.DisplayName AS [Synchronization Rule ID]

,StatusError AS [Status Error]

,ExpectedRuleEntryAction AS [Expected Rule Entry Action]

,SynchronizationRuleStatus AS [Synchronization Rule Status]

FROM /ExpectedRuleEntry[SynchronizationRuleID = 'b6a27a7c-4918-4e8e-a5f1-d0e0ca559594']

Last edited Nov 4, 2011 at 12:35 AM by zamojo, version 12

Comments

No comments yet.