Tuesday, August 10, 2010

IFD, WindowsIdentity, CrmImpersonator and Double Hop Deployment


In my previous post, I mentioned how do we authenticate between external database and CRM databases. But, last Friday, I realized that this is not 100% correct for all deployment scenarios.

Last week, one of our clients requested us to deploy CRM application in different boxes, i.e. they want us to install AD, SQL Server and CRM application in THREE separate boxes. After installation and configuration, we found that CRM is working properly. But, we noticed that all our custom aspx pages cannot retrieve data from external database through stored procedures using EXECUTE AS command. There is no error in impersonation process. This happened because there is a double hop issue in our deployment.

For other projects, we can resolve this issue by configuring delegate trust for SQL server in Active Directory. Unluckily, we cannot use this approach in this project because there is a security restriction in this organization. So, I have to figure it out how to resolve this issue by programming way.

After searching through Google, I found a very helpful post describing about how to resolve double hop issue in CRM. Thanks to Joe Ladas for his valuable contribution. He did mentioned that we should use SET CONTEXT_INFO command in our stored procedure to retrieve data. I tested it by hard coding USERID in our stored procedure and it is working properly.

So, I changed our code to retrieve UserId from CRM using WhoAmI request. Then, another issue is coming out. I cannot retrieve correct user id from WhoAmI request. Every time I received was a SYSTEM USER account id. This happened because we are using CrmImpersonator class in our aspx pages. I have to used CrmImpersonator class because our deployment also need to support for Internet Facing Deployment (IFD).

From Joe Ladas's post, I noticed that he was using WindowsIdentity Impersonation to retrieve UserId through WhoAmI request for AD Authentication. I followed the same way. I used calledId from CrmAuthenticationToken for IFD (CrmPostAuthentication) and WhoAmI request for AD authentication. It is working perfectly.

Below is the code sample I used for testing application. Hope it can help you all in someway :) Either leave comment or email me if you want to know further information or give suggestion to me. Thanks!

Retrieving Current User Id for IFD & AD Authentication
public Guid GetCurrentUserId(HttpContext context, string orgName)
{
    Guid _userId = Guid.Empty;

    // For IFD
    if (context != null && context.User.Identity.AuthenticationType == "CrmPostAuthentication")
    {
        CrmAuthenticationToken token = CrmAuthenticationToken.ExtractCrmAuthenticationToken(context, orgName);
 _userId = token.CallerId; // Get Current User Id
    }

    // For AD
    if (_userId == null || _userId == Guid.Empty) _userId = WhoAmI().UserId;
    return _userId;
}

public WhoAmIResponse WhoAmI()
{
    System.Security.Principal.WindowsIdentity identity = (System.Security.Principal.WindowsIdentity)System.Threading.Thread.CurrentPrincipal.Identity;
    System.Security.Principal.WindowsImpersonationContext impersonationContext = identity.Impersonate();

    WhoAmIRequest request = new WhoAmIRequest();
    WhoAmIResponse response = null;

    if (this.useInterface)
        response = this.iservice.Execute(request) as WhoAmIResponse;
    else
        response = this.service.Execute(request) as WhoAmIResponse;

    if (response == null) throw new UnauthorizedAccessException("System cannot retrieve WhoAmIResponse.");
    impersonationContext.Undo();

    return response;
}

Retrieving Data from CRM through External Database
public DataTable GetAllList()
{
    SqlClientHelper oSqlHelper = new SqlClientHelper(connString);
    SqlParamCollection oSqlParam = new SqlParamCollection();
    oSqlParam.AddParam("@userID", _userId);
    DataTable dt = oSqlHelper.ExecuteQuery("sp_GET_AllContacts", CommandType.StoredProcedure, oSqlParam);
    oSqlHelper.Dispose();

    return dt;
}

Stored Procedure I used in External Database
CREATE PROCEDURE [dbo].[sp_GET_AllContacts] 
-- Add the parameters for the stored procedure here
    @userID uniqueidentifier
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
    DECLARE @original uniqueidentifier
    SET @original = CONTEXT_INFO()
    
    -- Setting Current User Identity
    SET CONTEXT_INFO  @userID

    -- I am using Synonym Name in my External Database
    SELECT ContactID, LastName FROM FilteredContact

    -- Restoring Original Identity  
    IF @original IS NULL
        SET CONTEXT_INFO 0x
    ELSE
        SET CONTEXT_INFO @original
    END

Reference:
Understanding Kerberos Double Hop
Querying a Filtered View in a Custom Application with a Double Hop
Authentication and Impersonation between CRM database and External databases
Authentication from a custom ASPX page and token AuthenticationType – AD/IFD
MSSQL Synonym Generator