06 September 2008

FilteredView and CrmImpersonator?!

I was curious about how to use FilteredView in the CrmImpersnator class for a IFD(Internet-Facing Deployment) solution? Thanks to David Jennaway who gave me a clue.

Ok, the reason for that is because I'm building a CRM dashboard for an On-Premise/IFD CRM deployment. In order to use the Web services from an APSX page, I have to use the Microsoft.Crm.Sdk.CrmImpersonator class, however by doing that it's not possible to get relevant data from the FilteredView, because the CrmImpersnator() will authenticate as [NT AUTHORITY\SYSTEM] account(which is a system account in CRM).

The solution is to use SQL Execute As statement, see the sample code below:


using (new CrmImpersonator())
{
........
string username = "domain\\Guest";
string sqlQuery = "SELECT name FROM FilteredAccount";
string queryString = "GRANT IMPERSONATE ON USER::[NT AUTHORITY\\SYSTEM] TO [" + username + "] EXECUTE AS USER='" + username + "' " + sqlQuery + " REVERT";

........
}

In this example, you have to give user the Impersonate permission first, then use Execute As statement to impersonate the user.
After running the code, if you have a look the CRM database, under the Security\Users folder, the impersonated user account is added in the list, and it grant the 'Impersonate' permission to user [NT AUTHORITY\SYSTEM], see picture below:



By using this technic, you also need to map the user [NT AUTHORITY\SYSTEM] to the CRM database(e.g: Contoso_MSCRM).

6 comments:

sgundeti said...

This Tip was very helpfull..

In my case the SQL Database had the user "NT AUTHORITY\Network Service" . So Changing that string made all the trick.

Thanks

Srinivas Reddy Gundeti

vishal swami said...

Jim,

In my case I am trying to connect the CRM DB using the same impersonator in a custom page (ours is an IFD deployment) while after impersonating I am able to successfully use the CRMService but the system is not retrieving any result from DB although no error is returned. when i try to test the sql in DB it is working and returning results, but not from the custom page. do i have to made some adjustments in order to get the results ?

vishal swami said...

also when i tried to use the execute as i got this error "Cannot find the user 'NT AUTHORITY\SYSTEM', because it does not exist or you do not have permission."

vishal swami said...

hi buddy,

REPLACED NT AUTHORITY\SYSTEM TO NT AUTHORITY\NETWORK SERVICE AND IT STARTED WORKING LIKE A CHARM .

Thanks a ton for this beautiful post.

Unknown said...

Hi Jim,

We have used this and it is working, however we are seeing some permissions issues with AD. Do you have any recommendations about what the AD setup should be to allow impersonation? By default we are getting a SQL error "could not obtain information about windows nt group/user"

Sun Flower said...

Any one knows how to get relevant data from a filteredview in a IFD solution(CrmImpersonator)?Play Bingommtangkas