Sunday, February 21, 2010

Replicate Local Data Group (Offline Client / Outlook)

If you are using offline client application in CRM implementation, you will notice that configuring local data group is a very painful task for all developers. For me, most of our customers are using offline client applications in their daily operations. We have to configure local data group for all laptops based on their user roles and configuration.  It took more than one and half hour to finish the whole configuration setup for each laptop.

Last year, one of our customers suggested a console application called MSCRM: Automate Local Data Group Creation. It is very helpful application for us. We need to setup configuration for only one laptop (one user role) and copy it to the rest of  users' laptops. Amazingly, it took only a few minutes to finish the whole process. But, there still  has some draw backs. It required a few configuration steps to finished very carefully. Here is the list of steps for configuration:
  1. Go to SQL server and delete some records
  2. Create one text file which contains the list of views that you want to copy, and
  3. Run batch program with pre-defined parameters/commands, etc.
In short, it is not possible to ask users to help configuration on themselves.  They still need supervision of professionals. One mistake can screw up the whole database and operations.

So, I decided to do re-engineer works based on existing application. I changed existing web service calles to CRM 4.0, take out the first step (SQL delete process) and add user interface to automate the whole process. Thanks to David Jennaway for contributing his knowledge, idea and source code to community.

Below is the screen shot of my "Replicate Local Data Group" application:

You can run this application from any location/computer if you have access to their CRM application. 

Caution: Please backup your CRM user database first before running this application. Application will delete all Local Data Groups of destination users to prevent duplication.

System Requirement:
  • .NET Framework 2.0
 Access Matrix:
  • The login user who is connecting to CRM must be a member of PrivUserGroup. If you done more thant one installation for CRM, you will fine multiple PrivUserGroup in AD. Please make sure you are using correct one for login user.
  • All replicate users must have CREATE, READ, WRITE  and DELETE access to their own records (User) in "Saved View" entity. You can find "Saved View" entity under Core Record tab of your user role.
Download Link:


Saturday, February 13, 2010

Authentication and Impersonation between CRM database and External databases

Sometime we may need to create stored procedures and views to access CRM data for faster retrieval and processing. To do so, we have to use an external database to store all stored procs and views since Microsoft does not recommend to keep our stored procs, functions and views in CRM database.

For me, I am using an external database for THREE obvious reasons:
  1. Processing very complex business logic through stored procs
  2. Recording data changes for future reference (for example; audit data)
  3. To retrieve data using impersonation (through plug-in and/or C# code)

Below is the steps you may need to configure if you want authentication and impersonation between CRM database and external databases.(In this example, I am using "crm admin" account as a service account to do authentication and impersonation.)

In SQL Server Management Studio:

1. SQL Server >> Security >> Login >>
    Create domain\crmadmin in account MS SQL Server

2. SQL Server >> Security >> Login >> Select User properties >> Server Role >>
    Give domain\crmadmin to sysadmin server role

3. CRM/Extenal Database >> Security >> Login >>
    Add domain\crmadmin account and domain\ReportingGroup {GUID} account into orgname_CRM database and external database

4. SQL Server >> Security >> Login >> Select User properties >> User Mapping >>
    Map domain\crmadmin account to THREE databases (MSCRM_CONFIG, orgname_CRM, External database) and give database role to db_owner and public

5. SQL Server >> Security >> Login >> Select User properties >> User Mapping >>
    Map domain\ReportingGroup {GUID} account to TWO databases (orgname_CRM, External database) and give database role to public
6. Extenal Database >> Database Properties >> Permissions >>
    Choose domain\ReportingGroup {GUID} accountand give explicit permission to GRANT CONNECT, EXECUTE and SELECT

7. Run grant impersonation of crmadmin to reporting group

USE [master]

grant impersonate on login :: [domain\crmadmin] to [domain\ReportingGroup {GUID}]

8. Use EXEC AS LOGIN = 'domain\crmadin' for crmadmin impersonation if necessary

9. Use REVERT after every impersonation

Tips for development:
  1. To make your live easier in writing and deploying SQL scripts, please use my MSSQL Synonym Generator to create synonym names for you.
  2. If your CRM plug-ins need to access data from SQL server through impersonation, please use stored procedure and defined you impersonation code there. (You cannot pass through EXEC AS LOGIN, Stored Proc. and REVERT commands together in sqlcommand.commandText)
  3. If your environment does not have enough CALs to access remote desktop to use SQL server, you can refer EXEC AS LOGIN command to access SQL data to through your laptop/PC.

Friday, February 12, 2010

Impersonate CrmService web service calls by using CallerId

At times users access a resource as though they were someone else. This is known as impersonation. For example, in CRM, when we tried retrieved "saved views" from userquery entity, you can only retrieve those views that are belongs to you even though you are using CRM Admin account to retrieve those information.

Below is the code sample to impersonate CrmService calls in CRM 4.0:
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.OrganizationName = _strOrganization;
token.AuthenticationType = 0;
token.CallerId = userid; // userid that you want to impersonate

CrmService crmService = new CrmService();
crmService.Url = _strServerUrl + "/MSCRMServices/2007/crmservice.asmx";
crmService.CrmAuthenticationTokenValue = token;

// You can use System.Net.CredentialCache.DefaultCredentials
crmService.Credentials = new NetworkCredential("username", "password", "domain");

Caution: The credential user must be a member of PrivUserGroup. (You can check it under Active Directory Users and Computer) If is not assigned properly, you will receive following error message:
error code: 0x80040204
description: Invalid user auth.
error type: Platform

Wednesday, February 3, 2010

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

A few day ago, my colleague got above error message when he clicked on extension (aspx) pages in CRM application. Though he tried to browse extension pages from Development server, the IP address shown in error message was always point to LIVE server one. I checked database server setting in Deployment Manager and Windows Registry. All configuration settings were correct.

Below is the detail error message:
Exception information:
    Exception type: SocketException
    Exception message: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond  

Thread information:
    Thread ID: 7
    Thread account name: NT AUTHORITY\NETWORK SERVICE
    Is impersonating: False
    Stack trace:    at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)

After searching through google for a while, I found a good solution. I checked Deployment Properties table in CRM and found that ADSdkRootDomain and ADWebApplicationRootDomain values were pointed to LIVE server URL. Updating those values with Development Server URL solved the above issue. (Please refer to reference link for detail information.)

To check server URL:
SELECT ColumnName, NVarCharColumn FROM DeploymentProperties
WHERE ColumnName IN ('ADSdkRootDomain', 'ADWebApplicationRootDomain', 'AsyncSdkRootDomain')

To update server URL:
UPDATE DeploymentProperties
SET NVarCharColumn = '[servername]:[port]'
WHERE ColumnName IN ('ADSdkRootDomain', 'ADWebApplicationRootDomain', 'AsyncSdkRootDomain')
CRM 4.0 Client wont connect