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).

9 comments:

. said...

酒店經紀PRETTY GIRL 台北酒店經紀人 ,禮服店 酒店兼差PRETTY GIRL酒店公關 酒店小姐 彩色爆米花酒店兼職,酒店工作 彩色爆米花酒店經紀, 酒店上班,酒店工作 PRETTY GIRL酒店喝酒酒店上班 彩色爆米花台北酒店酒店小姐 PRETTY GIRL酒店上班酒店打工PRETTY GIRL酒店打工酒店經紀 彩色爆米花

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.

Bruce said...

Today,we are proud to announce the launch of the new wedding support service sell ffxi gil,packed with features sure to sell ffxi gils delight adventurers across Vana'diel looking to exchange eternal vows with their beloved!Responding to player demands for greater customization,the new service will grant brides and grooms freedom in choosing location,timing,dialogue,and sell Final Fantasy XI Gil more for their ceremony,allowing them to create a truly memorable event all their own.Information on all the features,including in-game sell ffxi gil item vendors and wedding certificates,can be found on the new wedding support site,so head on over sell ffxi gils and get started planning the wedding of your dreams sell Final Fantasy XIGil!

Curt 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"

Hieu Nguyen Ngoc said...

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

Đào Quân said...

hướng dẫn sử dụng hàm vlookup
thủ tục hoàn thuế thu nhập cá nhân
chi phí không được khấu trừ khi tính thuế tndn
chi phí không được khấu trừ khi tính thuế tndn
một số lỗi liên quan đến phần mềm htkk
cách kê khai bổ sung thế gtgt
cách lập bảng cân đối kế toán
cách lập báo cáo kết quả hoạt động kinh doanh

cách tính thuế gtgt theo phương pháp khấu trừ
xử lý hóa đơn sai
cách viết hóa đơn gtgt khi bán hàng
xử lý khi viết sai hóa đơn gtgt
lập tờ khai thuế gtgt mẫu 01
cách kê khai thuế gtgt theo tháng quý
cách tính thuế gtgt theo phương pháp trực tiếp
câu hỏi thường gặp về hóa đơn chứng từ
kê khai thuế tndn tạm tính quý
cách tính thuế gtgtg mới nhất
cách lập tờ khai thuế gtgt trên doanh thu 04
cách lập tờ khai thuế tndn 01b
ghi sổ kế toán theo hình thức nhật ký chung
xử lý khi phát hiện thừa thiếu hàng hóa
cách lập tờ khai thuế tndn 01a
xử lý khi phát hiện thừa thiếu hàng hóa