Querying a Filtered View in a Custom Application with a Double Hop

Have you ever needed to directly query a filtered view in CRM directly from a custom web page? On occasion it may be necessary for performance reasons. One good example is in creating a hierarchical view of accounts. To do this, recursion is needed and doing so using the CRM Service is just not performant. Simply because of all the round trips through the CRM Service. A good solution is to use a SQL Server Common Table Expression (CTE) and ADO.NET. This way all the heavy lifting is done on SQL Server and only a single round trip to the database is needed.

This won't be demonstrated in this blog, but instead a solution for getting around the double-hop rule in a multi-server deployment where CRM and SQL Server are installed on separate machines. Two additional solutions are available; first a domain account with a CRM license can be used as the Application Pool account in IIS. The web application can use this application pool with impersonation disabled, and as long as the account has permission to the data that is queried, things will work fine. However, data row-level security is thrown out the door for the calling user, as the Application Pool account is used. A second solution is to use Kerberos with trusted delegation configured in Active Directory. This is a good solution and maintains data row-level security for the calling user, but it is no easy task to set up.

Another option is to use SQL Server's context_info. More information can be found here (http://msdn.microsoft.com/en-us/library/ms180125.aspx). CRM uses the context_info to set the calling user's systemuserid in double hop scenarios and every filtered view checks the context_info for the systemcuserid of the calling user in the function [fn_FindUserGuid] located in the organization database.

When setting up the custom application in IIS, the application pool can use the Network Service account or whichever service account was used to install CRM. Impersonation needs to be disabled as the custom application will use the Network Service account to login to SQL Server. This is possible as the account is added to the SQL Access Group when CRM is installed. With this basic configuration the custom application can access SQL Server, however, since the account is not a CRM user and not occupying a license it will not have access to any data. This is where the context_info comes into play.

The query below demonstrates how to use the context_info when querying a filtered view.















Pretty simple, except how do we get the calling user's systemuserid? This is where impersonation is used but in code. The authenticated user can be impersonated prior to calling the WhoAmIRequest using the CRM Service. The WhoAmIRequest will return the userid(guid) for the calling user. Impersonation can then be undone in code and prior to the database call.

Below is a webform that demonstrates how to do this.



ASPX:

 

  
  

  
  

     runat="server" ID="ResultsGrid" 
   AutoGenerateColumns="true" 
   DataSourceID="CrmDataSource" 
   HeaderStyle-Font-Bold="true">
   
    ConnectionString="Data Source=dev-sql02;Initial Catalog=Neudesic_MSCRM;Trusted_Connection=True;">
 

Code Behind:

namespace PureCRM.Samples.FilteredView
{
 public partial class FilteredViewExample : System.Web.UI.Page
 {
  protected void Page_Load(object sender, EventArgs e)
  {
  }

  protected void ExecuteButton_Click(object sender, EventArgs e)
  {
   CrmDataSource.SelectCommand = SetContextInfo(QueryText.Value);
   ResultsGrid.DataBind();
  }

  private string SetContextInfo(string sqlQuery)
  {
   string contextInfoFormat = "declare @userid uniqueidentifier = '{0}' \rset context_info @userid\r{1}";
   string systemUserId = GetCrmSystemUserId();
   string sql = string.Format(contextInfoFormat, systemUserId, sqlQuery);

   return sql;
  }

  private string GetCrmSystemUserId()
  {
   WindowsIdentity identity = (WindowsIdentity)Thread.CurrentPrincipal.Identity;
   WindowsImpersonationContext impersonationContext = identity.Impersonate();

   CrmAuthenticationToken token = new CrmAuthenticationToken()
   {
    AuthenticationType = AuthenticationType.AD,
    OrganizationName = "Neudesic"
   };

   CrmService service = new CrmService()
   {
    Url = "http://dev-crm01/MSCRMServices/2007/CrmService.asmx",
    UseDefaultCredentials = true,
    CrmAuthenticationTokenValue = token
   };

   WhoAmIRequest request = new WhoAmIRequest();   
   WhoAmIResponse response = (WhoAmIResponse)service.Execute(request);

   impersonationContext.Undo();

   return response.UserId.ToString();
  }
 }
}