locked
IFD and Filtered View RRS feed

  • Question

  • How can we use Filtered View in IFD enviroment?
    Is it possible that the Filtered View work with IFD. I am connecting sql filtered view using integrated authentication and read data from filtered view.
    I realise that for IFD we can not use Windows authentication then how could we use filtered view then?

    Thanks

    Thursday, May 14, 2009 10:23 PM

Answers

  • Hi Ken,

    The guest account is just an example, you should change it to dynamicly pickup the logon user in your code, e.g:

     using (new CrmImpersonator())
                {
                    CrmAuthenticationToken token;
                    if (offline == true)
                    {
                        token = new CrmAuthenticationToken();
                    }
                    else
                    {
                        token = CrmAuthenticationToken.ExtractCrmAuthenticationToken(Context, orgname);
                    }
                    token.OrganizationName = orgname;
    
                    if (HttpContext.Current.User.Identity.AuthenticationType == "CrmPostAuthentication") //IFD
                    {
                        token.AuthenticationType = 2;
                    }
                    else
                    {
                        token.AuthenticationType = 0;
                    }
    
                    //Create the Service
                    CrmService service = new CrmService();
                    service.Credentials = System.Net.CredentialCache.DefaultCredentials;
                    service.CrmAuthenticationTokenValue = token;
                    service.Url = crmurl;
                    
                    
                    /* query filtered view*/
                    string connectionString = "Data Source=" + sqlServerName + ";Initial Catalog=" + databaseName + ";Integrated Security=SSPI";
                    SqlConnection connection = new SqlConnection(connectionString);
    
                    /* Logon user */
                    if (HttpContext.Current.User.Identity.AuthenticationType == "CrmPostAuthentication") //IFD
                    {
                        try
                        {
                            string queryString = "SELECT domainname FROM SystemUser WHERE systemuserid = '" + HttpContext.Current.User.Identity.Name + "'";                                     
                            SqlCommand command = new SqlCommand(queryString, connection);
                            command.Connection.Open();
                            SqlDataReader reader = command.ExecuteReader();
                            reader.Read();
                            username = reader[0].ToString();
                        }
                        catch (Exception er)
                        {
                            Response.Write(er.InnerException.ToString());
                        }
                        finally
                        {
                            connection.Close();
                        }
    
                    }
                    else
                    {
                        username = HttpContext.Current.User.Identity.Name;
                    }
    
    .......
    }


    I have upload the source code to the MSDN Code Gallery, you may downlaod it from here: http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=crm&DownloadId=3971


    Hope it helps.

    Cheers,
    Jim
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    • Marked as answer by Ken1986 Saturday, May 30, 2009 8:09 AM
    Wednesday, May 20, 2009 9:34 AM
    Moderator

All replies

  • IFD still uses Active Directory for authentication. IFD just presents it differently. You should be able to still use filtered views just the same.
    I say this as an opinion. I am not currently using IFD so I cannot directly test it for you.
    Larry Lentz [Microsoft Dynamics CRM MVP]
    Friday, May 15, 2009 2:37 AM
    Moderator
  • Add to Larry, I had a post regarding your question:

    FilteredView and CrmImpersonator?!



    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";
    
    ........
    }
    Hope that helps.

    Cheers,
    Jim

    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    Friday, May 15, 2009 6:33 AM
    Moderator
  • First of all thanks for all the help.

    However I got really stuck. Please please help

    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 pefectly 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.
    So far I still haven't made any progress on actually get it working on the IFD enviroment.

    Just in response to Jim's reply, Jim, will those code work for all the user, why it is using "domain\\Guest"? Do I need to replace the "domain\\Guest"? Will the security still be maintain using that way? Also you've just provide part of the code, if I need to execute the query, I still need to get the database connection and actually login to the crm database. Now since the web server and database server are different. In my case what should I do? I will try your suggestion out.

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

    Thanks

    Tuesday, May 19, 2009 9:52 AM
  • Hi Ken,

    The guest account is just an example, you should change it to dynamicly pickup the logon user in your code, e.g:

     using (new CrmImpersonator())
                {
                    CrmAuthenticationToken token;
                    if (offline == true)
                    {
                        token = new CrmAuthenticationToken();
                    }
                    else
                    {
                        token = CrmAuthenticationToken.ExtractCrmAuthenticationToken(Context, orgname);
                    }
                    token.OrganizationName = orgname;
    
                    if (HttpContext.Current.User.Identity.AuthenticationType == "CrmPostAuthentication") //IFD
                    {
                        token.AuthenticationType = 2;
                    }
                    else
                    {
                        token.AuthenticationType = 0;
                    }
    
                    //Create the Service
                    CrmService service = new CrmService();
                    service.Credentials = System.Net.CredentialCache.DefaultCredentials;
                    service.CrmAuthenticationTokenValue = token;
                    service.Url = crmurl;
                    
                    
                    /* query filtered view*/
                    string connectionString = "Data Source=" + sqlServerName + ";Initial Catalog=" + databaseName + ";Integrated Security=SSPI";
                    SqlConnection connection = new SqlConnection(connectionString);
    
                    /* Logon user */
                    if (HttpContext.Current.User.Identity.AuthenticationType == "CrmPostAuthentication") //IFD
                    {
                        try
                        {
                            string queryString = "SELECT domainname FROM SystemUser WHERE systemuserid = '" + HttpContext.Current.User.Identity.Name + "'";                                     
                            SqlCommand command = new SqlCommand(queryString, connection);
                            command.Connection.Open();
                            SqlDataReader reader = command.ExecuteReader();
                            reader.Read();
                            username = reader[0].ToString();
                        }
                        catch (Exception er)
                        {
                            Response.Write(er.InnerException.ToString());
                        }
                        finally
                        {
                            connection.Close();
                        }
    
                    }
                    else
                    {
                        username = HttpContext.Current.User.Identity.Name;
                    }
    
    .......
    }


    I have upload the source code to the MSDN Code Gallery, you may downlaod it from here: http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=crm&DownloadId=3971


    Hope it helps.

    Cheers,
    Jim
    Jim Wang - MVP Dynamics CRM - http://jianwang.blogspot.com , http://mscrm.cn
    • Marked as answer by Ken1986 Saturday, May 30, 2009 8:09 AM
    Wednesday, May 20, 2009 9:34 AM
    Moderator
  • Thanks Jim. It is really helpful.
    Saturday, May 30, 2009 8:09 AM