News from October, 2007

The purpose of this blog post is to provide a step by step walk through for setting up Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies. This add-in allows for Reports to run within the context of SharePoint.  The Reporting Services Add-in provides the following functionality:

• A Report Viewer Web Part, which provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.
• Web application pages so that you can create subscriptions and schedules, set model item security, and manage reports, models, and data sources.
• Support for standard Windows SharePoint Services features including document management, collaboration, security, and deployment with report server content types. You can use alerts, versioning (check in/out), and Filter Web Parts with reports. You can add the Report Viewer Web Part to any page or dashboard on a SharePoint site and customize its appearance. You can use SharePoint permission levels and roles to control access to report server content. You can also use SharePoint forms authentication to support access over Internet connections.

  Note The add-in is for reporting on SQL data not SharePoint data.

  This walk through makes a few assumptions about your setup environment.
• Active Directory 2003 domain running in native mode
• The SharePoint server is on a separate box from the Reporting server
• The reporting server and the SQL server are on the same box
• The SPAdmin account is the SharePoint administration account and is local administrator on the SharePoint server and the SQL server
• SPSQL account runs the sql services and reporting services
• SPSites account runs the application pool for the SharePoint web site.
• Your SharePoint Server is set to use Kerberos authentication

SetSPN

SetSPN (set spin) is used to configure Active Directory user and computer accounts for Kerberos delegations. Kerberos delegation is necessary if you are running reporting services on a separate server than your SharePoint server. If user A hits a website on computer B, computer B can forward the authentication to computer C. There are two benefits to configuring Kerberos; one, Kerberos is a more secure protocol than NTLM, two, Kerberos is necessary to correctly configure Reporting Services. 

1. Login to domain controller
2. Download the setspn.exe from http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en
3. Run setspn_setup.exe and install tool, click next
4. Agree to the EULA
5. Accept the default path and click install now
6. Click start -> Run and enter cmd
7. From the command prompt navigate to C:\Program Files\Resource Kit. You will need to use the setspn for the following three accounts. The SharePoint Service Account (SPAdmin), the Default site application pool account (SPSites) and the SQL Service account (SPSQL). Issue the following commands:
   1. setspn -A http/llqawss01 qalbapad\spadmin
   2. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spadmin
   3. setspn -A http/llqawss01 qalbapad\spsites
   4. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spsites
   5. setspn -A http/llqawss01 qalbapad\spsql
   6. setspn -A http/llqawss01.qalbapad.qalocal qalbapad\spsql
   7. setspn -A http/llqasql01 qalbapad\spadmin
   8. setspn -A http/llqasql01.qalbapad.qalocal qalbapad\spadmin
   9. setspn -A http/FQDN of server (www.ll.com) qalbapad\spsites
   10. setspn -A http/FQDN of server (www.ll.com) qalbapad\spadmin
   11. setspn -A http/FQDN of server (www.ll.com) qalbapad\spql
       You notice that you will need to setspn on each name the computer may use, the netbios name, the internal FQDN, if this machine uses another FQDN you will need to add this as well.(To be honest this is probably over kill but this will cover all your bases)
8. On the domain controller open active directory users and computers, We need to trust the computer accounts and Service accounts for delegation
   1. Find the SQL server in Active Directory Users and Computers (ADUC) right Click and go to properties and click the Delegation tab, then select Trust this computer for delegation to any service (Kerberos only)
   2. Find the WSS server in ADUC right Click and go to properties and click the Delegation tab then select Trust this computer for dlegation to any service (Kerberos only)
   3. Find the SharePoint Service account in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
   4. Find the SharePoint Site (SPSites) account in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)
   5. Find the SQL Server Service (SPSQL) in ADUC go to properties and click the Delegation tab then select Trust this user for delegation to any service(Kerberos only)

On the SQL/Reporting Server

1. Make SPAdmin local administrator of the SQL server computer
2. Install Microsoft .NET Framework 2.0
3. Microsoft .NET Framework 3.0
4. Download the SharePoint install from Microsoft
5. Execute SharePoint.exe
6. Accept the licensing agreement, click continue
7. Choose the advanced installation option
8. For Server Type choose Web Front-End (WFE), click install now
9. Click close to run the SharePoint Technology Configuration wizard
10. Select Yes, I want to connect to an existing server farm, click next
11. Enter the name of the database server and then click Retrieve Database Names. This will bring back the SharePoint Configuration database name.
12. In the Specify Database Access account enter the SharePoint Service account (SPAdmin) and password information, click next
13. Click Next
14. Click Finish
15. Download the reporting services add-in http://www.microsoft.com/downloads/details.aspx?familyid=1E53F882-0C16-4847-B331-132274AE8C84&displaylang=en

On the SharePoint Server

Install the SharePoint add in for Reporting services.

1. SharePointRS.msi, click next
2. Accept the Licensing agreement, click next
3. Click next
4. Click Install
5. Click Finish when complete
6. Login to the SQL Server computer, click start -> All Programs -> Microsoft SQL Server 2005 -> Configurations Tools -> Reporting Services Configuration
7. Connect to the SQL Server
8. Click on Database Setup
9. Click on Change to change the server mode to SharePoint
10. Click yes to create a new Reporting Services database
11. Leave the defaults and enter a name for the new SharePoint integrated Reporting Services database, click OK.
12. Click Apply
13. Leave the defaults and click OK
14. Now we need to configure the Reporting Services application pool to run as SPAdmin. Open IIS Manager and navigate to the Application Pool -> Report Server
15. Right click on Report Server and click properties, click the Identity tab
16. Configure the identity to be SPAdmin. This will allow the reporting server to access the SharePoint server for the SharePoint integration to work properly.
17. In IIS manager under the Web Sites folder right click the default site (This is where reporting services web is located) and click properties
18. In the Web Sites tab change the port to 8080, Click OK to apply
19. Return to the Reporting Server configuration and refresh. In the Web Service Identity you will need to click apply to complete the change made to the application pool
20. Click on SharePoint Integration
21. Follow link to SharePoint Central Administration site
22. From the Application tab click on Manage integration settings
23. Enter the url for the report server plus the virtual directory for the report server. Most likely this will be http://machinename:port#/reportserver, Click OK
24. Click on Grant database access, this will default to the local server. Change to the reporting server. Click OK
25. You will be prompted to enter credentials for accessing the report server. Enter the SQL account (SPSQL), click ok
26. Click on Set Server Defaults
27. In Reporting Services Server Defaults accept the defaults and click OK
    

Have a need to edit a document that is stored within SharePoint, but provide the link within another application?

Using just the URL to the document will only give you read-only access to the document, but a little bit of digging on the javascript involved provides a fairly simple solution.

Note that this has only been tested with Word 2007 and Excel 2007.

The short answer is to reference the following scripts (I do this in the <HEAD> section of the page):

<script type="text/javascript" language="javascript" src="http://<sharepoint server>/_layouts/1033/init.js"></script>
<script type="text/javascript" language="javascript" src="http://<sharepoint server>/_layouts/1033/core.js" defer></script>

For example:

<script type="text/javascript" language="javascript" src="http://tw-lt-m90-001/_layouts/1033/init.js"   ></script>
<script type="text/javascript" language="javascript" src="http://tw-lt-m90-001/_layouts/1033/core.js"    defer></script>

Then have the following for the link:

<A onfocus="OnLink(this)" HREF="<document url>" onclick="return DispEx(this,event,'TRUE','FALSE','TRUE','','0','SharePoint.OpenDocuments','','','','21','0','0','0x7fffffffffffffff')">
 <document name>
</A>

For example:

<A onfocus="OnLink(this)" href="http://tw-lt-m90-001/Documents/Hello%20World.doc"  onclick="return DispEx(this,event,'TRUE','FALSE','TRUE','','0','SharePoint.OpenDocuments','','','','21','0','0','0x7fffffffffffffff')">

Hello World

</A>

Here is a full example that includes the icon for the file type as well.

<HTML>

<HEAD>

<title>Documents</title>

<script type="text/javascript" language="javascript" src="http://tw-lt-m90-001/_layouts/1033/init.js" ></script>

<script type="text/javascript" language="javascript" src="http://tw-lt-m90-001/_layouts/1033/core.js"  defer></script>

</HEAD>

<BODY>

<TABLE width="100%" cellspacing=0 cellpadding=0 border=0>

<TR>

<TD>

<A TABINDEX=-1 href="http://tw-lt-m90-001/Documents/Hello%20World.doc"  onclick="return DispEx(this,event,'TRUE','FALSE','TRUE','','0','SharePoint.OpenDocuments','','','','21','0','0','0x7fffffffffffffff')">

<IMG BORDER=0 ALT="Hello World.doc" title="Hello World.doc" src="http://tw-lt-m90-001/_layouts/images/icdoc.gif" >

</A>

</TD>

<TD>

<A onfocus="OnLink(this)" href="http://tw-lt-m90-001/Documents/Hello%20World.doc"  onclick="return DispEx(this,event,'TRUE','FALSE','TRUE','','0','SharePoint.OpenDocuments','','','','21','0','0','0x7fffffffffffffff')">

Hello World

</A>

</TD>

</TR>

</TABLE>

</BODY>

</HTML>

To reference an icon it is simply "ic<ext>.gif" (http://<sharepoint url>/_layouts/images/ic<ext>.gif, e.g., http://tw-lt-m90-001/_layouts/images/icdoc.gif). For a full listing of icon files see all "ic*.gif" files in the TEMPLATE\IMAGES directory under the 12 hive.

This example will require using PowerShell but you could just as easily use C# or some other programming language.

You can run this from the PowerShell command line or bundle up as part of a PowerShell Script.

First load your handy dandy SharePoint Assembly:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

Next, create an object of the site that you want the application pool identity of:

$site = New-Object Microsoft.SharePoint.SPSite("http://www.threewill.com")

Now create a variable to hold the web application information:

$webapp= $site.WebApplication

One more variable to get the Application pool information:

$AppPool= $webapp.ApplicationPool | Out-String (I use the Out-String so I can see the information if I decide to use a script)

Lastly, let's take a look at the application pool information:

write-host $AppPool

It should look something like this:

CurrentIdentityType : SpecificUser

Username : DomanName\SPAccount

Password : YourPassword

SecurePassword : System.Security.SecureString

IsCredentialUpdateEnabled : True

IsCredentialDeploymentEnabled : True

Name : SharePoint - 2222

TypeName : Microsoft.SharePoint.Administration.SPApplicationPool

DisplayName : SharePoint - 2222

Id : a808448d-bbee-417c-9936-12bfac9738de

Status : Online

Parent : SPWebService Parent=SPFarm Name=SharePoint_Config

Version : 9013

Properties : {}

Farm : SPFarm Name=SharePoint_Config

UpgradedPersistedProperties : {}

Background

When WSS and MOSS crawl content and store that content to an index they can also store authorization information (ACL) to the data. This makes it easy for a search query to only provide results to which the search user has access. WSS search is limited to SharePoint sites, but MOSS search can go beyond that to web sites, file shares, exchange public folders, the BDC, and others. While some content such as SharePoint sites, file shares, and exchange public folders contain ACLs, others such as web sites and BDC do not.

The solution to trimming MOSS search results that do not contain ACLs is to use a security trimmer. A security trimmer is very simple; it takes a list of URLs and returns a BitArray indicating if the current user has access to each URL. A security trimmer runs at query time so there is a performance cost, but I've found that the story here isn't too bad since the security trimmer gets called in batches based on the number of search results shown to the user on a page. Basically if the ratio of allowed access to total possible results is high, the number of items to check for security trimming at a time should be kept to a minimum. In addition there is a way to specify a limit on the number of crawl URLs to check.

There is a BDC Security Trimmer or you can write your own Custom Security Trimmer. That last link has a good overview and walkthrough of how to write, deploy, and register a custom security trimmer. I recommend it for further reading. However, the walkthrough only shows how to register a security trimmer using stsadm. It does not show how to do it via code. In fact, on the stsadm command you provide the crawl rule path indicating that security trimmer references the craw rule, which is not the case (it is the other way around).

I needed to do this via code as part of a custom shared service provider administration screen. Since I had a little bit of trouble figuring this out and couldn't find anyone else that did it, I wanted to blog about it here once I found the solution.

Show Me Some Code!

OK, enough background, let's see some code on how to do this.

1. First, you're code will need to reference Microsoft.Office.Server.Search.dll which can be found in the ISAPI folder under the 12 Hive for a MOSS install. In addition, all of my code below uses the following using statement.

   using SearchAdmin = Microsoft.Office.Server.Search.Administration;
   

2. Now you can register your security trimmer. You will need the fully qualified type name for your security trimmer or access to it via code (as I have done below). In addition you need to specify the security trimmer id (an Int32 of any value of your choice assuming another security trimmer is not already registered with that value). If you don't have the context of the shared service provider you'll have to do a little more work.

   // Get the security trimmer manager
   
   // Note: no need to call SetSearchContextToUse as it is determined implicitly through HttpContext
   
   SearchAdmin.Security.PluggableSecurityTrimmerManager manager = SearchAdmin.Security.PluggableSecurityTrimmerManager.Instance;
   
   // Register the security trimmer
   
   // No need to provide any custom properties (must provide an empty named value collection)
   
   string fullyQualifiedTypeName = typeof(MyCustomSecurityTrimmer).AssemblyQualifiedName;
   
   manager.RegisterPluggableSecurityTrimmer(securityTrimmerId, fullyQualifiedTypeName, new NameValueCollection());
   

3. Then you will need to create or update your crawl rule to give it the security trimmer Id. The code below shows creating a crawl rule. If you don't have the context of the shared service provider, you'll have to do a little more work.

   // This page is in the context of the shared service provider, so this call should get our search context
   
   // otherwise we would need to use the ServerContext object instead and call SearchContext.GetContext(serverContext);
   
   // Note that ServerContext is in the Microsoft.Office.Server namespace (Microsoft.Office.Server.dll)
   
   SearchAdmin.SearchContext searchContext = SearchAdmin.SearchContext.Current;
   
   // Get the content object which is needed for access to content sources and crawl rules
   
   SearchAdmin.Content content = new SearchAdmin.Content(searchContext);
   
   // Create crawl rule
   
   SearchAdmin.CrawlRule crawlRule = content.CrawlRules.Create(SearchAdmin.CrawlRuleType.InclusionRule, rulePath);
   
   // Set other crawl rule properties here...
   
   // Set the security trimmer id and save the changes
   
   crawlRule.PluggableSecurityTrimmerId = securityTrimmerId;
   
   crawlRule.Update();
   

That's it. Fairly simple, especially if you already have the appropriate context as my code does since it runs within the context of the shared service provider.

As you can see, the crawl rule references the security trimmer Id and the security trimmer does not reference the crawl rule.

Note that your security trimmer will not be in effect unless you crawl (probably a full crawl) after you register your security trimmer even though the security trimmer runs as query time.