Creating a WebReference to a specific CRM tenant

Sometimes it’s the little things that can take forever to figure out.  I inherited a web application that connected to a CRM tenant and after adding a few new attributes to CRM, I needed to update my WebReference.  Unfortunately the last developer had been working in a VPC so my WebReference that I had set to the CRM server resulted in the proxy classes that were “generic” and did not contain my customizations.  I remembered doing this before, but it had been awhile so I had to search for the answer (again):

 

<add key="crmservice.CrmServiceWsdl" value="http://<CRM Server>/mscrmservices/2007/crmservice.asmx?uniquename=<tenant>"/>

Setup a domain user account as the CrmAppPool identity

It was difficult to find the documentation on exactly which groups / policies the CrmAppPool identity needed when changing from Network Service to a domain user account.  Fortunately I found this Microsoft KB which gives you step-by-step instructions (note, I removed the CRM 3.0 portions) and it worked on the first try:

 

1.     Include the domain account user in the following groups in Active Directory:

o    The Domain Users Active Directory group

o    The PrivUserGroup

o    The SQLAccessGroup

To do this, follow these steps:

d.             Log on to a server as a user who has the Domain Admin rights or the rights to update these groups.

e.     Right-click the Domain Users group in Active Directory, and then click Properties.

f.      In the Group name box, type the name of the user who is running the CRM Application Pool, and then click OK two times.

g.     Repeat steps b and c for the PrivUserGroup group and for the SQLAccessGroup group.

If you have more than one Microsoft Dynamics CRM deployment installed, multiple groups exist in Active Directory. To determine the groups that you want to update, follow these steps.

For Microsoft Dynamics CRM 4.0

h.             Run the following SQL statement against the MSCRM_config database:

select id, friendlyname from organization

i.              Note the GUID. For example, the GUID may be C8AB1D52-9383-4164-B571-4C80D46674E3 Org Name.

j.      Find the PrivUserGroup group and the SQLAccessGroup group in Active Directory. The group name contains the GUID that you noted in step b.

2.     Include the domain account user in the following groups in the Microsoft Dynamics CRM server:

o    The local IIS_WPG group

o    The local CRM_WPG group

Note The domain account user must have the following local user rights:

o    The Impersonate a client after authentication right

o    The Log on as a service right

To do this, follow these steps:

d.     In the Microsoft Dynamics CRM server, click Start, point to Administrative Tools, and then click Local Security Policy.

e.     Expand Local Policies, and then click User Rights Assignment.

f.      Right-click Impersonate a client after authentication, and then click Properties.

g.     Click Add User or Group.

Note You may have to click Location to select the domain instead of the local computer.

h.     In the Group name box, type the name of the user who is running the CRM Application Pool, and then click OK two times.

i.      Repeat steps 2c through 2e for the Log on as a service right.

3.     Restart Internet Information Services (IIS). To do this, click Start, click Run, type IISRESET, and then click OK.

 

Connect to CRM Web Service using impersonation

My Dev and Test servers live in two different domains and I am developing an external “portal” for public users to interface with CRM using web services. I am developing locally, but my CRM server is on a different domain, so I need to use a different domain user to connect to the web service. I am using the new SDK and the connection string was easy to change from “Integrated” to “AD”:

<add name="Crm"

connectionString="Authentication Type=AD;Server=http://CRM/Org1;User ID=USA\user1;Password=pass1"

/>

But I also need to use the MetadataService to lookup some picklist values and that requires connecting the “old school” way. We were already using the Crm connection string to configure the MetadataService, so I just needed to check the Auth type and grab the User ID and Password:

public MetadataService GetMetadataServiceForConnString()
{
    string serverPath = GetConfigAttribute("server=http://");
    string[] serverPathParts = serverPath.Split('/');
    string connServer = serverPathParts[0];
    string connOrgName = serverPathParts[1];
    string username = null;
    string password = null;
    string domain = null;
    if (GetConfigAttribute("authentication type=").ToLower() == "ad")
    {
        // need to use active directory credentials
        string[] usernameParts = GetConfigAttribute("user id=").Split('\\');
        domain = usernameParts[0];
        username = usernameParts[1];
        password = GetConfigAttribute("password=");

    }
    return GetMetadataService(connServer, connOrgName, username, password, domain);
}

private string GetConfigAttribute(string searchString)
{
    string connCrm = System.Configuration.ConfigurationManager.ConnectionStrings["Crm"].ConnectionString;
    int pathPadding = searchString.Length;
    int pathStart = connCrm.ToLower().IndexOf(searchString.ToLower()) + pathPadding;
    int pathLength = connCrm.IndexOf(";", pathStart);
    if (pathLength == -1)
        pathLength = connCrm.Length;
    pathLength -= pathStart;
    return connCrm.Substring(pathStart, pathLength);
}

public MetadataService GetMetadataService(string server, string organizationName, string username, string password, string domain)
{
    CrmAuthenticationToken token = new CrmAuthenticationToken();
    token.AuthenticationType = AuthenticationType.AD; //Active Directory
    token.OrganizationName = organizationName;
    MetadataService service = new MetadataService();
    service.Url = string.Format("http://{0}/mscrmservices/2007/MetadataService.asmx", server);
    if (username == null)
    {
        service.UseDefaultCredentials = true;
    }
    else
    {
        service.UseDefaultCredentials = false;
        System.Net.ICredentials WebServiceCredentials = 
            new System.Net.NetworkCredential(username,password,domain);
        service.Credentials = WebServiceCredentials;
    }
    service.PreAuthenticate = true;
    service.CrmAuthenticationTokenValue = token;
    return service;
}

Use xp_cmdshell and BCP to Export SQL Server data to CSV

This article describes how to use xp_cmdshell and BCP to export data to CSV.  One problem I ran into was the error “Unable to open BCP host data-file”.  This turned out to be a file permission issue.  I had to give NETWORK SERVICE (the local account that runs my SQL Server service) Full Access rights to the queryout directory.  I also had to use the –S flag to specify which SQL Instance (my server has two) to connect to:

 

EXEC xp_cmdshell 'bcp "SELECT * FROM <my database>.dbo.account" queryout "D:\queryout\bcptest.txt" -T -S "<my server>\<my instance>" -c -t,'

 

 

Get a list of all the tables in a SQL 2005/2008 database

I thought this SQL 2005/2008 tip was very cool.  This gets a list of all the tables in my database:

 

SELECT [name] FROM <my database>.sys.tables;

 

Grant Permission for SQL User for Membership Provider using Database Role

The SQL User connecting to your Membershp Provider database (default named aspnetdb) should have the following database role:

aspnet_Membership_FullAccess

 

Otherwise you will get the following error:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'aspnetdb', schema 'dbo'.

Connection String for Membership Provider using a Trusted Connection over TCP/IP on a specific Port

<add name="LocalSqlServer"

  connectionString="Data Source=192.0.0.1,2116;

                    Network Library=DBMSSOCN;

                    Initial Catalog=aspnetdb;

                    Integrated Security=SSPI;"/>