How to run filtered views in MS CRM through ASP .NET in a distributed server environment (DB machine different than web server)

Posted on 13 August 2009 by Spyros Moschovakos

When attempting to play with MS CRM 3.0/4.0 API and build your own web applications over the web services provided, there may come a time when native CRM authorization will be needed, which essentially means that you will need to get the connected user and try to query the SQL server database retrieving only the account/contact/lead/marketing list or any other entity records that the user is allowed to see. And whenever user ownership is applied, filtered views are extremely useful.

However, there are certain requirements in order to impersonate the current windows user into SQL server. Here is a quick checklist of the required infrastructure:

1)      First of all we have to ensure that our ASP .NET application is configured properly to perform its tasks impersonating each time the windows user that makes the request. Therefore, ensure that you have the following parameters in web.config (under <system.web> element):

<authentication mode="Windows" />
<identity impersonate="true" />

2)    Modify your virtual directory settings in IIS to require windows authentication. In order to do this, you will have to disable anonymous authentication and enable Integrated Windows Authentication, as shown below:

Virtual Directory Security on IIS

Virtual Directory Security on IIS

2)  At the application level, ensure that the database connection is done using integrated windows authentication. Here is a sample connection string for SQL Server OLEDB  Provider:

PROVIDER=SQLOLEDB;Data Source=DBServerName;
Initial Catalog=Organization_MSCRM;Integrated Security=SSPI;

The key to the above is the “Integrated Security=SSPI” option which tells us that the connection with the SQL Server CRM Database will attempted using the windows user who is running the current thread. This, combined with the “impersonate=true” parameter in web.config ensures that the windows identity that is grabbed from the server will be running our sql queries. Note that for the data source name, it is necessary to use computer name, not IP address (see hints at the end of the article).

3)      Now the tricky part: the above would be enough in an environment that hosts the SQL Server CRM databases in the same machine with the application server (IIS). If this isn’t true, you will probably get authentication error messages upon DB connection. The most common one is: “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’”, which is typically “solved” once you add the NT AUTHORITY\ANONYMOUS LOGON” to the database logins. However, this workaround -apart from making your database extremely vulnerable- simply will not do in our case, since we need the queries to be executed by the windows domain users themselves.

This happens due to the “double hop” problem. For the lazy devs (like me) who do not wish to go into detailed problem analysis, let’s just say that your web server computer is not granted the required permissions from the domain controller’s active directory to pass the user’s domain credentials to the database server. That means that the authentication takes place well enough on IIS but the latter is unable to pass them correctly to the SQL Server machine. This process is known as “delegation”.

This is solved by the following steps:

a. Open the Active Directory management console on the domain controller

b. Find the computer that hosts MS CRM and IIS. Right click/Properties

c. Select delegation tab, and check the radio button that says “Trust this computer for delegation to specified services only”.

d. Click “Add”, and on the pop up form click “Users or Computers”. Select your Database server that hosts the SQL Server CRM DB.

e. You will then see a list of available services. Find the SQL Server’s service (MSSQLSvc)and add it to the list.

Note: if you cannot find such a service, then this probably means that your SQL server runs under a domain account, see here for details on manually adding an SPN.

f. Click “OK” and you should have the following settings:

Active Directory Delegation Configuration

Active Directory Delegation Configuration

Click OK and you are ready. You could always allow this computer to delegate credentials to any service (and therefore click on the second radio button) but this is generally not recommended due to security restrictions (when dealing with stuff from IT departments throughout large organizations, do NOT underestimate this factor).

5)      Finally, as delegation requires Kerberos protocol we must ensure that IIS treats windows authentication correctly, that is, it first negotiates with the client in order to accept and delegate credentials through Kerberos. IIS 7 (Win2008) supports Kerberos by default, in some IIS 6 installations you’ll have to add the appropriate headers manually. Here’s how (taken from the respective kb article):

a. Click Start, click Run, type cmd, and then press ENTER.

b. Locate the directory that contains the Adsutil.vbs file. By default, this directory is C:\Inetpub\Adminscripts.

c. Use the following command to retrieve the current values for theNTAuthenticationProviders metabase property:

cscript adsutil.vbs get w3svc/WebSite/root/NTAuthenticationProviders

In the above command, WebSite is a placeholder for the Web site ID number. The Web site ID number of the default Web site is 1.

d. If the Negotiate process is enabled, this command returns the following information:

NTAuthenticationProviders : (STRING) “Negotiate,NTLM”

e. If the command in step c does not return the string “Negotiate,NTLM,” use the following command to enable the Negotiate process:

cscript adsutil.vbs set w3svc/WebSite/root/NTAuthenticationProviders “Negotiate,NTLM”

f. Repeat step c to verify that the Negotiate process has been enabled.

That’s all! Some necessary hints:

  • ALWAYS USE DOMAIN NAMES for the servers when applying credential delegation. This means that the database connection string in your application should contain the data source server with its computer domain name, NOT THE IP ADDRESS! If you use IP addresses, the SQL Server authentication will fail because delegation rights are granted at the level of the domain, thus active directory needs to know the source and target machine’s computer names.
  • If your application contain queries to other views also (not filtered ones), then make sure that the users that are connected have the appropriate permissions to execute SELECT on these views/objects. By default, non-administrative users in CRM have access only to the filtered views of entities where user ownership is applied, so be careful with your joins!

Test it!

You are now all set to run any CRM filtered view through ASP .NET. In order to test it, create a new web page, and place the following code on Page_Load event (after modifying the connection string of course):

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim conn As New OleDb.OleDbConnection
        Dim table As New System.Data.DataTable()
        Dim ConnectionString As String = _
        "PROVIDER=SQLOLEDB;Data Source=MyDBServer;Initial Catalog=MyOrganization_MSCRM;Integrated Security=SSPI"
        'first get logged in user
        Dim w As System.Security.Principal.WindowsPrincipal = Threading.Thread.CurrentPrincipal()
        Response.Write("<b>connected user is:</b>" & w.Identity.Name)
        Response.Write("<br /><br />")
        Response.Write("<b><u>CRM DB User details</u></b><br />")

        'now query for the details of the user in the CRM DB
        Dim cn As New System.Data.OleDb.OleDbConnection(ConnectionString)
        cn.Open()
        Dim da As New System.Data.OleDb.OleDbDataAdapter("select systemuserid as id, fullname, " & _
        "domainname as username from filteredsystemuser where systemuserid=dbo.fn_FindUserGuid()", cn)
        da.Fill(table)
        da.Dispose()
        cn.Close()
        If table.Rows.Count > 0 Then
            Response.Write("<b>User ID:</b>" & table.Rows(0)("id").ToString() & "<br>")
            Response.Write("<b>User Full Name:</b>" & table.Rows(0)("fullname").ToString() & "<br>")
            Response.Write("<b>User Domain Name:</b>" & table.Rows(0)("username").ToString() & "<br>")
        Else
            Response.Write("<i>User Not Found</i>")
        End If
        Response.End()
    End Sub

2 Comments For This Post

  1. mycapi Says:

    Hi,
    i did all this steps, but i still get the error System.Data.SqlClient.SqlException (0×80131904): Login failed for user ‘appPoolUser’. My code:

    CrmAuthenticationToken token = new CrmAuthenticationToken();
    token.AuthenticationType = 1; // Use Active Directory authentication
    token.OrganizationName = “ORG”;

    CrmService service = new CrmService();
    service.Url = “http://crm.org.internal/mscrmservices/2007/CrmServiceWsdl.aspx”;
    service.CrmAuthenticationTokenValue = token;
    service.Credentials = System.Net.CredentialCache.DefaultCredentials;

    WhoAmIRequest userRequest = new WhoAmIRequest();
    Response test = service.Execute(userRequest); // HERE IS THE ERROR IF RUNNING LOKAL
    WhoAmIResponse user = (WhoAmIResponse)test;
    token.CallerId = user.UserId;

    string conString = String.Empty;

    conString = ConfigurationManager.ConnectionStrings["nameConString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
    using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStatememt, conString))
    adapter.Fill(selectResult);
    }

    The connection string:

    The only difference is: with I get an 500 – Internal server error when calling my page

    Can anyone help me?

  2. Spyros Moschovakos Says:

    hi,

    I’m not sure I understand why you are both calling services and database, but the error indicates that your code is trying to access the crm db with the application pool identity, which probably is a sign that either the anonymous authentication is enabled, or the delegation does not work. You should disable anonymous authentication and leave only “integrated windows” and “impersonation” in your IIS configuration.

    If you inject this code in a web page:

    System.Security.Principal.WindowsPrincipal w = Threading.Thread.CurrentPrincipal();
    Response.Write(“connected user is:” + w.Identity.Name);

    and this reports the connected user, then it means that the delegation configuration in AD is wrong. If it reports the application pool identity then this means that the IIS/web.config options are not correctly setup.

Leave a Reply