Writing CRM callouts with filtered views - Microsoft Dynamics 365 Blog

Aaron Elder, a valued Microsoft MVP and CRM solutions provider provides this guest blog today.

A common problem that we often face when writing CRM callouts is how to call Filtered Views from within the callout. We might want to do this, for a variety of reasons such as performing T-SQL operations, JOINs and the like. The problem of course is that the CRM Filtered Views “filter” / provide row-level security based on the AD user that is making the request. This is why you normally cannot use SQL Authentication to access CRM Filtered Views and must use Integrated Authentication. Since callouts typically run in the context of NT AUTHORITY\NETWORK SERVICE, calls made to a Filtered View from within a callout will always return zero results. This happens because NETWORK SERVICE is not a CRM user and cannot own or have shared access rights to any records.

What is the fix? Well previously, we had been using .NET impersonation to impersonate a CRM user prior to making the call to SQL. While this got around the issue, it required use to store the username and password of the user to be impersonated on the server. While we would store the password in an encrypted fashion, this is still not very security and it made management of the account difficult. We have recently discovered a much cleaner way that does not require storing the AD credentials on the machine and also enable the use of SQL Authentication.

The trick is to use SQL context switching to impersonate the AD user prior to calling the Filtered View SELECT statement. The two methods for doing this are SETUSER and EXECUTE AS. SETUSER is legacy and works in SQL 2000 as well as SQL 2005 (although it may be deprecated in the future) and EXECUTE AS is new in SQL 2005. If you are using SQL 2005, I definitely recommend EXECUTE AS, as it requires fewer privileges from the calling user and as such, I would consider it more secure. You can read about the differences between these two methods here:

http://msdn2.microsoft.com/en-us/library/ms188315.aspx

Here is a sample callout that shows the two methods:

public override PreCalloutReturnValue PreCreate(CalloutUserContext userContext,
CalloutEntityContext entityContext,

                                                ref string entityXml, ref string errorMessage)

{

      // Store the result

      bool rows = false;

      // Callout is running as NETWORK SERVICE, connect using Integrated Auth

      using (SqlConnection conn = new SqlConnection(“Data Source=localhost; Initial Catalog=Invoke_Systems_Dev_MSCRM;Integrated Security=SSPI”))

      {

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;

            cmd.CommandType = CommandType.Text;

            conn.Open();

            // SQL 2000 Method – Impersonate the CRM Administrator

            // cmd.CommandText = @”SETUSER ‘invokecrm\administrator’

            //                        SELECT * FROM dbo.FilteredAccount”;

            // SQL 2005 Method – Impersonate the CRM Administrator

            cmd.CommandText = @”EXECUTE AS USER = ‘invokecrm\administrator’

                                    SELECT * FROM dbo.FilteredAccount

                                    REVERT”;

            SqlDataReader reader = cmd.ExecuteReader();

            // This will always be false, unless we use SQL impersonation to impersonate a CRM user

            rows = reader.HasRows;

      }

      return PreCalloutReturnValue.Continue;

}

This posting is provided “AS IS” with no warranties, and confers no rights.

Aaron Elder
Director, Software Development
invokesystems

Tags