Archive for the ‘LINQ 2 Sharepoint’ Category

Scenario: SSRS, in SharePoint integrated mode, is calling a custom web service located in the SharePoint ISAPI folder access through _vti_bin.  This service is using LINQ 2 SharePoint to query multiple lists in the SharePoint WFE.

A layout of the set up can be seen below. Okay, network diagrams is not my strong point! 


Because the authentication is being passed over more that 2 boundaries it looses the user and passes null and the only way to fix this is to use Kerberos.

So here’s how I went about setting it up:

Service Principal Names (SPN) for Service Accounts   

In order pass the Kerberos token you need to set up SPN’s. 

Note: Although I’ve not found confirmation of this SPN’s appear to be case sensitive

setspn.exe -A HTTP/<SSRS_FQDN> SSRSService
setspn.exe –A HTTP/<SSRS_NetBIOSName> SSRSService

Set the <SSRS_FQDN> to the FQDN of the server hosting the SharePoint Integrated SSRS and the <SSRS_NetBIOSName> as the Server name.

These entries can be confirmed by running

setspn.exe –L SSRSService

This should give an output similar to below.


The onto the WFE’s

setspn.exe -A HTTP/<SP_WFE_FQDN> SPService
setspn.exe -A HTTP/<SP_WFE_NetBIOSName> SPService

Set the <SP_WFE_FQDN> to either: the name of the server hosting the Sharepoint WFE or if this is an NLB cluster use the cluster name and the same goes for the <SP_WFE_NetBIOSName>

setspn.exe -A MSSQLSvc/<SQL_FQDN>:1433 SqlDbService
setspn.exe -A MSSQLSvc/<SQL_NetBIOSName>:1433 SqlDbService

Set the <SQL_FQDN> to either: the name of the server hosting SQL or if this is a cluster use the cluster name and the same goes for the <SQL_NetBIOSName>

Active Directory Users and Computers

Next, Open Active Directory Users and Computers and change each of the 3 accounts, selecting the Trust this user for delegation to any service (Kerberos Only) option on the delegation tab. 


SSRS App Server Changes

On the SharePoint Application Server which is hosting SSRS Open the Local Security Policy and Go to User Management Rights. Change to  “Act as a part of Operating System and “Impersonate a client after authentication” to include the users for both the WFE’s App Pool and SSRS Service Account


Report Server Configuration Changes

Open the RsReportServer.config file and locate the <AuthenticationTypes> section. Add <RSWindowsNegotiate/> as the first entry in this section.

Central Admin Changes for Web Application

Next, open Central Admin and Navigate to Application Management –> Manage Web Application


Then, select the application, then the authentication providers


In the pop-up click on the “Default” link in the Edit Authentication window scroll down to IIS Authentication Settings and choose Negotiate.


Scroll Down and Save.

Give this a minute to propagate to the other Servers in the farm and you should now be able to access the Reports which call the web service. 


A fairly obvious one this, but I thought I would just blog it anyway. When trying to generate some data model classes using SPMetal I received the error “Invalid File Name”.

Invalid File Name SPMetal

I opened up the SharePoint logs and searched for the SPMetal from the bottom up.  First hit was:

Now terminating ULS (SPMETAL.EXE, onetnative.dll) .

Slightly above that was:

“Failed to retrieve the list schema for feature {69187EFB-2495-475A-BBFD-24A6B9B7A21B}, list template 10036;

After searching my solution for the list definition 10036. It was a list which was no longer in use. Browsing to the list gave the same error.


After deleting the list using SPD  I re-ran the SPMetal command successfully.

Today, while trying generate a model, using SPMetal, I found out that SPMetal does  not like spaces in the web URL. After trying the URL in quotes with the space and again with %20 in the string for the URL encoding seen below. Neither of them were able to find the application. 

SPMetal /web: ”http://sharepoint/services/Rota Management” /namespace: etc
SPMetal /web: ”http://sharepoint/services/Rota%20Management” /namespace: etc

Resulting in the following errors respectively

Error: Web at http://sharepoint/services/Rota Management could not be found.
Error: Web at http://sharepoint/services/Rota0Management could not be found.

So in order to get the model definition I used the following PowerShell command.

Start-SPAssignment –Global
$web = Get-SPWeb "http://sharepoint/services/Rota Management"
$web.ServerRelativeUrl = "/services/RotaManagement"
Stop-SPAssignment –Global

Then, I ran the SPMetal command again and it worked no problem.

After generating the class just reversed the above to restore the URL

When you use SPMetal.exe to generate strongly typed entity classes to represent your SharePoint site, all lists which contain choice fields are created as enumerators. However, the references to the enumerator created in the class are pointing to a System.Nullable of the enum.

This is not a big deal to convert, but it does cause a bit of a problem when using anonymous types from LINQ 2 SharePoint.  As I’m sure you are aware, any assignment within the select new of the anonymous type has to be either; assigned directly or by the return value of a method – you cannot manipulate the string within the select.

So, to ease my pain, I created a generic method, which i have added to my SPHelper static class, to provide this functionality so I didn’t have to write a different method for every enumerator type and I thought I would just share it.

Get string from nullable enum
  1.   /// <summary>
  2.         /// Gets the nullable enumerator string.
  3.         /// </summary>
  4.         /// <typeparam name="T">A Nullable enumerator</typeparam>
  5.         /// <param name="enumerator">The enumerator.</param>
  6.         /// <param name="nullValue">The value to be returned if the enumerator doesnt have a value.</param>
  7.         /// <returns>A string representation of the enum value </returns>
  8.         public static string GetNullableEnumeratorString<T>(T? enumerator, string nullValue) where T : struct, IConvertible
  9.         {
  10.             if (!typeof(T).IsEnum)
  11.             {
  12.                 throw new ArgumentException("T must be an enumerated type");
  13.             }
  15.             return enumerator.HasValue ? enumerator.Value.ToString() : nullValue;
  16.         }

Seen below is and example of how to call this method from the LINQ select statement.

Assigmment by Method – Anon T
  1. var employees = from si in sensitiveInformation.ToList().Distinct()
  2.                                where si.Employee.Created >= lastRunTime
  3.                                select
  4.                                    new
  5.                                    {
  6.                                        FirstName = si.Employee.FullName,
  7.                                        LastName = si.Employee.LastName,
  8.                                        Gender =
  9.                                            SPHelper.GetNullableEnumeratorString(si.Employee.Gender, "Unassigned"),
  10.                                    };