Filtered View in IFD deployment RRS feed

  • Question

  • Basically I've got is a custom aspx web page perfroming a custom search functionality using filtered view. The implementation is through direct sql access to MS CRM database using SSPI and get results directly from the filtered view.

    It is a big advantage to use the filtered view and the query contain mutiple entity and mutiple search criteria across related entities. The connection string looks like "Data Source=MY_SQLSERVER_NAME;Initial Catalog=MY_DB_NAME; Integrated Security=SSPI". When the custom aspx page loads, it gets the connection and execute the sql query. I am not using any Web Service code to authenticate or perform any web service call. It works perfectly fine when the custom aspx page was deployed under on-premise.

    But when it needs to be deployed in the IFD way I got two critical issues.

    * First, of course is the windows authentication SSPI under IFD enviroment and still need to confirm that it is working, theoretically it should though.
    * Second is to do with the fact that the CRM Host Company is using different boxes for performance reason, that is they got web server (which my custom page will sit on), database server, data import server and reporting server ect.. on different boxes.

    I got bit of confusion now with the windows authentication i.e. SSPI of my custom page. What is it actually going to authenticate.
    1. Is it the crm web server "NT AUTHORITY\\NETWORK SERVICE" account
    2. or is is the "NT AUTHORITY\\System" account
    3. or is it the CRM calling user's windows account for those who invoke the page.
    4. or it could be any account that set to run the crm web site?
    My understanding is it is not option 3. If I add the first two account to the MSCRM database login will it work for my case?

    I am facing the situation of replace all the sql queries with the web service call.

    It will be quite a large amount of rework. So please help...
    Tuesday, May 19, 2009 9:56 AM


  • Hi Ken,

    Your assumptions are basically right. If you use integrated security on the connection string the account being used will be the same as the application pool that is running you code, if your code is under the CRM Web App (ISV folder) it will use the CRM Web App Pool credentials which by default are the Network Service.

    Anyway, when you do the CRM setup the logins on the SQL Server are created even if you have a separate SQL Server Box. Check on you SQL Server box if that is the case.

    Another thing that you should account for when working with IFD, is that the sql query is going to run under the account mentioned and not under the user account so the CRM security will not work properly with the Filtered Views. You should use impersonation on the queries by adding before the query a statement like  "Execute as Login=..." more info here http://blogs.msdn.com/crm/archive/2008/07/08/accessing-a-sql-database-from-a-microsoft-dynamics-crm-plug-in.aspx

    The web service approach will work as well.

    Hope it helps

    Marco Amoedo - http://marcoamoedo.com
    • Marked as answer by Ken1986 Monday, July 6, 2009 10:10 PM
    Wednesday, June 3, 2009 12:50 PM