List Web Part for Microsoft Dynamics CRM 4.0 Deployment Scenarios

Kamal Sharma Published Thursday, January 29, 2009 9:53 AM

---

List Web Part for Microsoft Dynamics CRM 4.0 supports Internet Facing Deployment (IFD) and On Premise configuration for Dynamics CRM and SharePoint.

This article briefly describes the steps necessary for configuring List Web Part determined by the deployment scenarios of Dynamics CRM and SharePoint Server.

NOTE: Dynamics CRM 4.0 Server allows IFD + On Premise Authentication mechanism to be configured for Internet Facing deployments. User’s IP address determines the authentication mechanism used to access Dynamics CRM Server (On Premise or IFD). In contrast, SharePoint's Internet Facing website is based on the URL used to access it.

The List Web Part for Microsoft Dynamics CRM 4.0 is supported on following versions of Microsoft CRM and SharePoint:

• Microsoft Dynamics CRM 4.0.
• Windows SharePoint Services 3.0 SP1 or Microsoft Office SharePoint Server 2007 SP1 must be installed.

The different scenarios are represented by a matrix below:

SharePoint

CRM

On Premise

Internet-Facing

On Premise

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is always in the same intranet: Supported [ Scenario 1 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is accessing via Extranet: Not Supported as Dynamics CRM 4.0 Server should directly be accessed by List Web Part User.

On Premise + IFD

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is always in the same intranet: Supported [ Scenario 1 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is in the same zone as SharePoint Server: Supported

[ Scenario 4 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is in the same Intranet or is accessing via Extranet: Supported [ Scenario 2]

· Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is in the same intranet or Extranet with respect to Dynamics CRM 4.0 Server : Supported [ Scenario 3 ]

· Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is in the same intranet or Extranet with respect to SharePoint Server: Supported

[ Scenario 4 ]

Scenarios

In this section, we describe the various scenarios for List Web Part.

1. Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is always On Premise

In this case, we typically assume an organization where the users have access to Dynamics CRM and SharePoint Servers while they are in the intranet of the company. Figure 1 shows the following setup.

Figure 1. Intranet Setup

Prerequisites:

1. Dynamics CRM 4.0 and SharePoint Servers configured as On Premise.

2. List Web Part User through his IE browser can access SharePoint and Dynamics CRM 4.0 Servers from the Intranet.

3. Ensure that Trust for Delegation is set between SharePoint Server and Dynamics CRM Servers.

Troubleshooting: User is unable to configure List Web Part to access On Premise Dynamics CRM Server, instead User's IFD Credential Screen is popped up by List Web Part as shown in Figure 2.

Figure 2. List Web Part IFD Login pane

Resolution:

In this case, the SharePoint server does not receive a valid CRM ticket from Dynamics CRM 4.0 Server and thus displays an IFD Authenticate page. This happens when Trust for Delegation is not setup between the SharePoint Server and Dynamics CRM 4.0 Server. Consequently, the Dynamics CRM 4.0 Server denies the Authentication request as it does not receive valid Active Directory Credentials from SharePoint.

For resolving this issue, Please setup Trust for Delegation between Dynamics CRM 4.0 Server and SharePoint Server as mentioned in the following blog:

http://blogs.msdn.com/crm/archive/2009/01/14/trust-for-delegation-in-list-web-part-for-microsoft-dynamics-crm-4-0.aspx

2. Dynamics CRM 4.0 Server and SharePoint Server present in the same Intranet and List Web Part User is either On Premise or is accessing via Extranet.

In this particular scenario, the List Web Part User is a traveling person who tries to access data from within the Intranet zone as well as from Extranet. The Dynamics CRM 4.0 and SharePoint Server are hosted within the organization. Figure 3 demonstrates this functionality.

Figure 3. List Web Part User accessing remotely as well as in intranet.

Prerequisites:

1. Dynamics CRM 4.0 Server configured as IFD for SharePoint Server. This is necessary because SharePoint Server has no way to determine the List Web Part user's AD credentials when he/she is in the extranet zone.

For configuring this setup, specify the subnet mask to use as 255.255.255.255 in the CRM 4.0 IFD Configuration Tool when Dynamics CRM is configured as IFD + On Premise as shown in Figure 4. For more details, please look at the Additional Configuration steps when SharePoint and Microsoft Dynamics CRM are both IFD Section of List Web Part Readme Documentation.

Figure 4. IFD Configuration Tool

2. SharePoint server setup in IFD mode. ( Expect a blog soon on this topic )

3. User must configure List Web Part using Dynamics CRM IFD URL from within the intranet and externally.

4. List Web Part User can access SharePoint Server and Dynamics CRM 4.0 Server directly from his IE browser.

Troubleshooting: Users are unable to connect to Dynamics CRM 4.0 IFD Server from List Web Part. In this case, users would see the following message being popped up on their screen shown in Figure 5.

Figure 5. Dynamics CRM Connection Error Dialog

Resolution: For resolving this issue in the scenario, please check if one can access Dynamics CRM 4.0 Server from the SharePoint Machine through an IE browser. The Dynamics CRM Server should be asking user credentials as opposed to using Active Directory Credentials to log in.

For a connection issue, please make sure that an appropriate hosts entry or DNS Setting is made.

Host entry can be made by editing the hosts file in %windir%\system32\drivers\etc and adding the Dynamics CRM IP Address and Hostname. For e.g.

2. Users are unable to see icons displayed in the List Web Part on IE Browser. Screenshot in Figure 6 shows this behaviour.

Figure 6. Images incorrectly rendered

Resolution: This issue arises when the List Web Part User is unable to access the Dynamics CRM 4.0 IFD Server Machine from his own machine. For verifying this, just try accessing the Dynamics CRM Server from IE browser directly. Also, ensure that appropriate hosts entry or DNS Settings are made from the computer which is connecting to the Dynamics CRM Machine. In order to add the hosts entry, see the details in the above step.

3. Users not able to connect to SharePoint Internet Facing site.

Resolution: The cause of this problem is most likely a connection issue to SharePoint. Please check if there is a hosts entry for SharePoint Server on the Users machine.

3. Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is either On Premise or Extranet with respect to Dynamics CRM Server.

Here, the Dynamics CRM Server and the List Web Part User are part of the same organization. The List Web Part User is a travelling person who tries to access List Web Part internally as well as externally. SharePoint is hosted as an external server. Figure 7 outlines this scenario. This is similar to the scenario 2, however, here the users may move in and out of the Dynamics CRM Server Intranet.

Figure 7. List Web Part User in same organization as Dynamics CRM Server

Prerequisites:

1. Dynamics CRM 4.0 Server and SharePoint Server configured in IFD mode.

2. List Web Part User can access SharePoint Server and Dynamics CRM Server directly from his IE browser.

3. User configures the List Web Part using Dynamics CRM IFD URL from within the domain and externally.

Troubleshooting: User is unable to connect through List Web Part when he/she is with the intranet domain of Dynamics CRM.

Resolution:

Please ensure that one is using Dynamics CRM IFD URL during configuration and not the usual On Premise URL. This is due to the fact that Dynamics CRM Server always appear as IFD to SharePoint Machine where List Web Part is installed.

Please look at Troubleshooting steps of Scenario 2, for other problems.

4. Dynamics CRM 4.0 Server and SharePoint Server present in different zones and List Web Part User is either On Premise or Extranet with respect to SharePoint Server.

Figure 8 shows the illustration of this scenario. The List Web Part User is a travelling person which belongs to the same organization as the SharePoint Server. Dynamics CRM Server is hosted externally to the organization.

Figure 8. List Web Part User in same intranet as the SharePoint Server

Prerequisites:

1. Dynamics CRM 4.0 Server and SharePoint Server configured in IFD mode.

2. List Web Part User can access SharePoint Server and Dynamics CRM Server directly from his IE browser.

3. User configures the List Web Part using Dynamics CRM IFD URL.

For other issues and resolution, please check out the Troubleshooting steps mentioned in Scenario 2.

Useful Links

a) Microsoft Dynamics CRM 4.0 IFD Tool Download, http://www.microsoft.com/downloads/details.aspx?FamilyID=69089514-6e5a-47e1-928b-4e4d4a8541c0&displaylang=en

b) Using Microsoft Dynamics CRM 4.0 IFD Tool, http://support.microsoft.com/kb/948779

c) Host File Troubleshooting, http://technet.microsoft.com/en-us/library/bb727005.aspx

Cheers,

Kamal Sharma

Some SQL Snippets

Posted by David Jennaway

---

Over time (don't ask how long – suffice to say I first used Microsoft SQL Server on OS/2) you pick up a fair amount of useful SQL Server knowledge. This post is intended to be a random collection of snippets that I use and remember, and I expect to add to the post periodically as I encounter further uses for SQL knowledge. So, in no particular order...

Finding SQL objects that contain a particular string
The definition of SQL objects can be accessed via the sys.syscomments view in the SQL database, and can be queried. The following example returns the name of objects that contain 'Test' somewhere within the definition. The object_name function is a quick way to get the name of an object from its id – the other way is to join to the sys.objects view.

select distinct object_name(id) from sys.syscomments where text like '%Test%'

Note that this only works if the SQL object definition was not encrypted with the WITH ENCRYPTION option

Granting Permissions to a set of objects
I've yet to find a good user-interface in SQL for setting permissions on a set of objects quickly, so I tend to use SQL commands. The following procedure shows how to use a cursor to iterate through a set of objects and execute a dynamically-built GRANT statement on them

declare cur cursor fast_forward for

select name from sys.objects

where type = 'V' and name like 'vw_%' -- Get all views, beginning vw_

declare @obj sysname, @sql nvarchar(2000)

open cur

fetch next from cur into @obj

while @@fetch_status = 0

begin

set @sql = 'GRANT SELECT ON ' + @obj + ' TO public'

-- grant select permission to public

exec (@sql)

fetch next from cur into @obj

end

close cur

deallocate cur 

Outputting stored procedure information to a table

There are cases when you might want to use the results of a stored procedure in a table structure for future processing. There's not an EXECUTE INTO statement but you can use INSERT ... EXECUTE. You can also use this with dynamically constructed SQL, using EXECUTE (@sql). The following example uses both EXECUTE syntaxes, and shows how to iterate though the names of 'tables' from a linked server – this is used to query Excel spreadsheets where there is a dynamic range of identically structured worksheets

create table #excelsheets -- Store names of spreadsheets in Excel

( TABLE_CAT sysname null

,TABLE_SCHEM sysname null

,TABLE_NAME sysname not null

,TABLE_TYPE sysname null

,REMARKS nvarchar(255) null )

insert #excelsheets execute sp_tables_ex 'EXCELDYNAMIC'

-- EXCELDYNAMIC is a linked server

create table #tmp

-- Temporary storage of data, so results can be output as one result set

( TABLE_NAME sysname

,[Month] int

,[Target] decimal(10,2) )

declare cur cursor fast_forward

for select TABLE_NAME from #excelsheets

declare @tbl sysname, @sql nvarchar(4000)

open cur

fetch next from cur into @tbl

while @@fetch_status = 0

begin

-- Build dynamic SQL statement. It would be nice to pass the statement as a parameter to OPENQUERY, but that's not permitted

set @sql = 'Select ''' + @tbl + ''' as TABLE_NAME, [Month], [Target] FROM EXCELDYNAMIC...[' + @tbl + ']'

insert #tmp exec (@sql)

fetch next from cur into @tbl

end

-- Cleanup and output results

close cur

deallocate cur

select * from #tmp

drop table #tmp

drop table #excelsheets

Posted by David Jennaway

Dynamic Export to Excel feature – How to protect data over the wire

Monika Borgaonkar Published Monday, January 26, 2009 10:22 AM

---

One of the well-known features offered in Microsoft Dynamics CRM v4 is export data to Excel as a dynamic worksheet, a dynamic pivot table, or even to a static worksheet. Users can then refresh these dynamic Excel sheets alone to get the most current data without having to pull up the web client and do a re-export.

A common question asked with respect to Excel web queries is how to protect query data from being exposed over the wire. When using Excel web queries, especially in an on-premise deployment, in spite of having configured CRM web server to allow traffic only over https, the query data exchanged, will not be protected over SSL. This is because Excel web queries are designed to directly query the SQL server mainly for performance reasons. In an on-premise deployment, since enabling https will cover all connections going over port 443 on your web server, this particular entry point remains exposed unless you have IPSEC configured. This blog discusses solutions to protect the dynamic export to Excel traffic over the wire in case of intranet scenarios.

Note that when accessing an Internet Facing Deployment from an external network or a CRM Online deployment via a dynamically exported sheet, Excel web queries do not talk directly to SQL server instead submit a fetchxml to the web server which then forms a SQL query accordingly to retrieve data from CRM database. Hence, permitting SSL only connections to the web server will protect data over the wire even for dynamic Excel sheets in case of IFD and Crm Online.

There are two solutions using which one can achieve data protection over the wire for dynamic Excel sheets. Before deciding upon which method works best for your needs, I suggest to ponder upon the caveats (also described below) associated with each method.

Solution A: Forcing CRM application to always run live queries using fetchXML

You can force an on-premise deployment to always serve dynamic Excel worksheets using the fetchXML route by adding a registry key to the machine running CRM application server role. Add a type DWORD registry key named UseWebQueryForLiveExport, with value set to 1, under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM hive.

Using the above method will now force all newly exported dynamic Excel sheets with CRM data to pull data using fetchxml and thereby not directly talk to the SQL server.

Caveats: Once the UseWebQueryForLiveExport registry key is in effect, if you want to refresh your newly exported dynamic Excel sheets with CRM data, you will require CRM Outlook client installed. This is primarily because once the above registry key is applied, all dynamically exported Excel sheets will use CRMTicket based authentication mechanism instead of integrated authentication.

Also note that once the above registry key is applied, newly exported dynamic sheets will be forced to pull data via fetchxml using ticket based authentication. Any sheets exported prior to the introduction of the UseWebQueryForLiveExport registry key will continue to pull data over integrated authentication by directly talking to SQL server hosting the CRM database and hence exposed over the wire. So you may have to ask all the users to re-export their dynamic Excel sheets.

In case you want to limit the usage of Export to Excel feature to few users, there is a special privilege “Export to Excel” which can be revoked from the role assigned to a user. This privilege is granted by default to all roles shipped out of the box. As a good practice, you want to create custom role with this privilege granted/revoked per the specific needs of your organization.

Solution B: Enabling Force Protocol Encryption on SQL Server

The second method requires very less user interaction and can be done silently without having any users to re-export their dynamic Excel sheets. This method relies on enabling SSL encryption on your SQL server. To enable encryption on the instance of SQL server hosting the CRM database, a server-side setting “Force Protocol Encryption” needs to be switched on.

A few things to be noted before enabling Force Protocol Encryption on your SQL instance:

a. You will have to acquire a Server Authentication certificate from an Enterprise Certificate Authority that your company interacts with.

Selfssl.exe utility can be used to generate a certificate for trial purposes. Selfssl.exe can be found as part of IIS6.0 resource kit http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/993a8a36-5761-448f-889e-9ae58d072c09.mspx?mfr=true . Selfssl.exe generated certificates are not to be deployed in production environments.

b. The subject property of the certificate should exactly match the fully qualified domain name of your SQL server hosting the CRM database and the intended purpose of the certificate should be Server Authentication.

c. Before applying the certificate, ensure that you have SQL services running under a domain user account credential and not a built-in account like NT AUTHORITY\Network Service. In order to apply the certificate to your SQL instance, you require to login under the same credential as your SQL service and hence this note.

d. Note that by enabling Force Protocol encryption on the SQL server, communication between all clients and SQL Server is encrypted. So before enabling this, ensure that this is indeed what your organization needs.

How to enable Force Protocol Encryption for SQL 2005: http://support.microsoft.com/kb/316898

The same steps listed in the above article work for SQL 2008 also.

Additional considerations for SQL 2008: http://msdn.microsoft.com/en-us/library/ms131691.aspx

A major advantage of this method is that in an intranet only (on-premise SKU) setup, all Excel dynamic sheets would be inadvertently protected over the wire without having the users re-export their previously created sheets. Also in other words, with this solution in place, Excel dynamic sheets will still be using integrated authentication and will not be forced to install MSCRM Outlook client.

Caveats: Though this method does not require any action from users, the SQL services indeed need to be restarted and hence a downtime will be involved. Also if you share the SQL server hosting MSCRM databases to host databases from other applications (this is not recommended from security aspect), then enabling Force protocol encryption will affect those database connections also. Enabling encryption on SQL server is known to have performance impact and hence if speed is prime criteria, then this may not be the solution to opt for.

Cheers,

Monika Borgaonkar

Hooray!! PerformancePoint Server is merging with SharePoint

I just read a posting from Microsoft and in the Summer of 2009 we should see a nice addition to SharePoint called PerformancePoint Services.

 

"Microsoft's strategy is to bring BI to the masses through the familiar, widely used tools of SharePoint and Excel, while building on the highly scalable SQL Server platform. Based on an in-depth product roadmap review and customer feedback, we've decided to consolidate PerformancePoint Server into SharePoint Server, making the widely used scorecard, dashboard and analytical capabilities available to everyone in the organisation."

Can't publish a workflow after installing a Rollup Update? Read on...

Posted by Mike Snyder on January 22, 2009

---

If you have installed the Microsoft CRM Update Rollup 1 or Update Rollup 2, you might experience an error where you cannot publish a workflow rule anymore. According to Microsoft this might happen if you modified the web.config file in your deployment:

If you have modified the Microsoft CRM website web.config file, then you need to manually open the Microsoft CRM Web site web.config file in an editor like Notepad and add the following section to it in order to continue being able to publish workflows:

In the section <authorizedTypes>

…multiple lines here

<authorizedType Assembly="mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Namespace="System.Globalization" TypeName="CultureInfo" Authorized="True"/>

</authorizedTypes>

Microsoft said they are looking at modifying the install notes to include this warning and/or revising the Update Rollup 2 installation files.

Posted by Mike Snyder on January 22, 2009

Update Rollup 2 for Microsoft Dynamics CRM 4.0

Smiti Shah Published Tuesday, January 20, 2009 8:11 AM

---

The Microsoft Dynamics CRM Sustained Engineering team released Microsoft Dynamics CRM 4.0 Update Rollup 2 on Thursday, January 15, 2009.

Below is the link to the release and related information about the Rollup. Please see the Knowledge Base (KB) article for more details about the Update Rollup 2 content and instructions..

• Microsoft Download Center: http://www.microsoft.com/downloads/details.aspx?familyid=aa671769-61e9-45c4-919f-c88199aa4241&displaylang=en&tm
• Microsoft Knowledge Base Article: http://support.microsoft.com/kb/959419

Install Details about Update Rollup 2

• Update Rollup 1 is not a prerequisite for installing Update Rollup 2
• The Update Rollup 2 client can be deployed before the server is upgraded to Update Rollup 2
• Update Rollup 2 can be uninstalled
• Unlike Update Rollup 1, Update Rollup 2 is packaged so that each language is packaged individually. As a result, the download size of the packages for Update Rollup 2 is significantly smaller. For example, the CRM Server package size is reduced from about 171 MB in Update Rollup 1 to about 9 MB in Update Rollup 2. The Outlook Client package size is reduced from about 78 MB in Update Rollup 1 to about 4 MB in Update Rollup 2

How to avoid a required reboot when installing Update Rollup 2 for CRM Outlook Client

• Before starting the update process, go to Options from the CRM menu.  On the “General” tab, uncheck the bottom checkbox that says “Always run the…Host process” and then click “Ok” button.
• Check for Updates and start the install.  While that is running, choose Exit from Outlook’s File menu to close down Outlook.  This will avoid any “File in use” errors.  If you do get the error, you were probably a little slow in shutting down Outlook.  You can click-on “Retry” once Outlook and the CRM hoster process have completed their shutdowns.
• Restart Outlook after the patch is installed.

Making Update Rollup 2 available to your clients via AutoUpdate:

You can find more information about AutoUpdate in Eric Newell’s blog entry at http://blogs.msdn.com/crm/archive/2008/05/08/crm-client-autoupdate.aspx and the Microsoft Dynamics CRM 4.0 Operating and Maintaining Guide, part of the Microsoft Dynamics CRM 4.0 Implementation Guide.

If you have a direct internet connection from your client machines, you can avoid some of the configuration steps and use the linkID directly. Below are the necessary steps to configure the AutoUpdate for Update Rollup 1.

Note: These are steps 5, 6 and 7 of Eric’s blog.

1. Create the configuration XML file and save it.

<ClientPatches>

   <Create>

      <!--- *** UR2 PATCH -->

        <ClientPatchInfo>

          <PatchId>{9EA7FDEB-9D7E-4278-8CD1-94ACEFA40D3F }</PatchId>

          <Title>Update Rollup 2 for Microsoft Dynamics CRM 4.0 (KB 959419)</Title>

          <Description>Update Rollup 2 for Microsoft Dynamics CRM 4.0 (KB 959419)</Description>

          <!--- *** This will make it Mandatory -->

          <IsMandatory>true</IsMandatory>

          <IsEnabled>true</IsEnabled>

          <ClientType>OutlookLaptop, OutlookDesktop</ClientType>

          <LinkId>140023</LinkId>

      </ClientPatchInfo>

   </Create>

</ClientPatches>

2. From the command prompt, go to the directory where the ClientPatchConfigurator.exe is located ([ServerInstallDir]\Tools and type microsoft.crm.tools.clientpatchconfigurator.exe [configfile].xml

3. Once the patch has been uploaded, launch the Outlook client

The dialog should now appear saying that “Update Rollup 2 for Microsoft Dynamics CRM 4.0 (KB 959419)” is available. If the <IsMandatory> is set to false, the client will only see the update if the user selects “Check for Updates” via the CRM Menu in the Outlook client.

Smiti Shah

List Web Part for Microsoft Dynamics CRM 4.0: Understanding Connections

Nimisha Saboo Wrote this article on the CRM Web Part. Way to go!

---

List Web Part supports three different types of connections that you can use to build various dashboards on a SharePoint portal. They are:

1. Field to field connections
2. Row to row connections
3. Table to table connections

Let us walk through each of them to understand how they work, and what job they are best suited for. But before we dive into greater details, I’ll briefly put together the concept of connections in web parts for the uninitiated.

A web part connection is like a contract between two web parts to exchange some data. There are two parties in a connection: provider, and consumer. The provider sends the data as agreed in the contract to the consumer. The consumer can then use this data in whatever way it wants.

While one List Web Part provider can provide data to many consumers simultaneously, a List Web Part consumer can consume data only from a single connection at a given point of time. The same instance of List Web Part can act as both a provider and a consumer at the same time, thus enabling branched connections and cascaded connections. More on this in a bit after we go through each of the connection types List Web Part supports.

Field to Field connections:

The field to field type connections enable filtering based on relationships between two entities in CRM. For example, let’s consider Accounts and Orders in CRM. They are related by a one-to-many relationship on the attribute Customer. So if you enable a field-to-field connection between Account and Order List Web Part instances, with Account being provider, and Orders being consumer, then selecting a record in Account grid will show you all those records in Order, which have Customer as that selected account.

If there is more than one relationship between two entities, then the result in the consumer is an OR of all the relationship fields. For instance, let’s say, order has one more relationship with account on an attribute called Partner Customer. Then selecting an account record in the provider will return all those order records, which have the selected account either as Customer, or Partner Customer, or both.

List Web Part supports this connection for all the three types of relationships in CRM: One to Many, Many to One, and Many to Many.

How to create a field to field connection:

i. Add two List Web Part instances on a SharePoint web page.

ii. Open the web part page in Edit mode.

iii. Choose one of the web parts as provider, and other as consumer.

iv. Click the provider part’s connection menu. Select ‘Send Selected Field To’, and choose the consumer web part from the list that shows up.

Figure 1: Configuring field to field connections

That’s it! Your field to field connection is ready to be used.

Field to Field Connections with custom web parts

Custom web parts can connect to a List Web part using field to field connections, if they implement the following interface:

public interface IWebPartCrmData

    {

        /// <summary>

        /// Schema name of the CRM entity

        /// </summary>

        string EntityLogicalName{ get; }

        /// <summary>

        /// Display Name of the CRM entity

        /// </summary>

        string EntityDisplayName{ get; }

        /// <summary>

        /// Schema name of the primary key of the CRM entity

        /// </summary>

        string PrimaryKeyLogicalName{ get; }

        /// <summary>

        /// Display name of the primary key of the CRM entity

        /// </summary>

        string PrimaryKeyDisplayName{ get; }

        /// <summary>

        /// Value of the Primary Key of a particular instance of CRM entity.

        /// </summary>

        string PrimaryKeyValue{ get; }

        /// <summary>

        /// Value of the Primary Attribute of a particular instance of CRM entity.

        /// </summary>

        string PrimaryAttributeValue { get; }

    }

If your custom web part is a provider, then it needs to implement this interface. If it is a consumer, then its consumer method should consume an instance of this interface.How to write custom web parts is beyond the scope of this blog, but more details can be found here.

Row to Web Part Parameter Connections:

This type of connection enables one set of fields from provider web part to be mapped to another set of fields from the consumer web part. While configuring this connection between two List Web Parts, you need to perform a mapping between various columns shown in the provider grid to another set of columns in the consumer grid. The consumer then filters its results based on the data from provider.

Here's an example that'll help explain this. Suppose you want to reach out to all contacts in CRM who belong to the same city as yours, then this is how you'll do it:

i. Add two list Web Part instances on a SharePoint web page. Configure first for User Entity, and second for Contacts. User will act as provider and Contact will be the consumer. For our example scenario, you should configure both User and Contact with a view that has City in its column set.

ii. Open the web part page in Edit mode.

iii. Click the provider part’s connection menu. Select ‘Send Selected Row To’, and choose the consumer web part from the list that shows up.

Figure 2: Configuring row to web part parameter connections

iv. A Configure Connection webpage dialog shows up, which will let you map one or more columns from the provider to respective columns in the consumer. Please note that only those columns show up in the list, which are present on the grid. Map the City from User to Address1_City in Contact. Keep clicking Next until you reach Finish.

Figure 3: Configuring connection mappings

The connection has been configured. Now select yourself from the list of users shown in the User web part, and you should see only those contacts which have the same city as yours. Select any other user with a different city, and the results in contact web part should change to reflect the new city.

You can configure this connection so as to filter on a group of columns instead of a single one. Just map as many columns from the provider to respective consumer columns as shown in the transformer screen, and you should be good to go. The result shown is an AND of all columns. What this means is that if you apply a filter on City, and zip code, only those contacts which satisfy both the city AND zip code will be shown.

Also, if you map more than one column from the provider to the same consumer column, then consumer will filter results only on the last mapped column from provider.

This connection is really powerful as it enables you to filter data crossing the organization boundaries in CRM. You can connect List Web Parts from two different CRM organizations, or two different CRM Server deployments, and filter data. You can even connect and filter data from other LOB applications, if you have custom web parts built for them.

Row to Web Part Parameter Connections For Advanced Scenarios

While configuring Row to Web Part Parameter type of connection, you'll notice that there are some columns which appear twice on the Transformer screen. For example, for a Contact Web Part, you should be able to see two columns with respect to Parent Customer, namely Parent Customer and Parent Customer: Id.

And here's why: Every CRM column has a property called Attribute Type, which represents the nature of data a column is used to store. The Attribute Type could be a boolean, a picklist, an integer, a nvarchar, and so on and so forth. While some of these columns store their values in text format, others store them using IDs, and do a reference lookup when showing up in the text format.

Examples of the latter kind are Lookup Types, Booleans, Picklists , Integers(of type Time Zone, Duration, or Language), etc.

Let's get back to the previous example of Parent Customer, which is a lookup field from Account in Contact. In CRM, lookup fields are referenced using GUIDs, and while displaying, their display names are shown. So from a provider web part, if you map Parent Customer to some consumer attribute, the actual data passed will be its display value. For example, if the Parent Customer in a record is called Active Cycling, and behind the scenes, has an ID as {E6E82758-99BB-4717-8D46-BC6A848A3442}, then mapping Parent Customer from provider will send the value 'Active Cycling' and mapping Parent Customer: Id will send the value ' {E6E82758-99BB-4717-8D46-BC6A848A3442}' to the consumer.

The point to note here is that mapping IDs will make sense as long as the two web parts in the connection have been configured for the same CRM organization. Also, if we choose to map Parent Customer: Id, then the receiving field in the consumer should be of a type that accepts a GUID, i.e. in a mapping, the data type of the receiving field should be compatible with the data that provider is sending. In case, it is not, the consumer web part will show an appropriate error message, telling you that your field mappings are incorrect.

You should use ID fields to map, in case you are connecting two web parts configured for the same organization. This will give you a better performance, and accurate results.

You can still choose to map the Non-ID fields(example Potential Customer) even when in the same organization. But because you are mapping on display names now, then all the records with the same display name on the mapped attribute will be considered while applying filtering on the consumer.

Of course, Non-ID field mappings make sense when you are connecting across CRM organizations, or to other custom web parts, which do not understand CRM IDs.

Removing Row to Web Part Parameter Connections:

You can remove this connection by following the steps below:

i. Open the web part page in Edit mode.

ii. Click the provider part’s connection menu. Select ‘Send Selected Row To’, and choose the consumer web part from the list that shows up.

iii. The configure Connection web page dialog shows up as shown in the figure below:

Figure 4: Removing row to web part parameter connection

iv. Click on Remove Connection.

Table to Table Connections:

List Web Part acts as a Table Provider, and you can connect it to any other web part that consumes the IWebPartTable interface. The provider sends the entire data displayed in the grid to the consumer, and the consumer is free to use it in whatever way it wants. Possible examples of consumers could be: A charting web part, that takes a view containing revenue, and customer cities, and then creates a city wise distribution of revenues.

Please note that List web Part by itself does not consume any table data.

Dashboard creation using connections:

Now that we know the various connection types supported by List Web Part, we can use them to build dashboards, which give you a 360⁰ view of you data. You can create cascaded connections, branched connections, and also combine various type of connections.

For example, you can create the following dashboard to take a look at all Opportunities and Orders associated with a particular account that belongs to a certain user:

Figure 5: CRM dashboard using List Web Part

Figure 6: CRM Dashboard

To accomplish this, create a Row to Row Connection between User and Account on the column Owner(present in both User and Account), with User being Provider, and Account being Consumer.

Next create a connection between Account and Order using Field to Field Connections, with Account being provider and Order being Consumer. Similarly, you can create a field to field connection between Account and Opportunity.

NOTE: While configuring the connections above, please ensure that you add web parts on the web page and configure connections in the same order in which data is flowing from provider to consumer. For example, in the above scenario, data flows from User -> Account -> Order, and User -> Account -> Opportunity.

Hence the order of steps here can be:

1. Add User web part

2. Add Account web part

3. Add Order web part

4. Add Opportunity web part

5. User to Account connection

6. Account to Order connection

7. Account to Opportunity connection

I believe you are pretty excited to try your own hands on connections by now, and hopefully, you have all the raw material to get started. Happy dashboarding!

Nimisha Saboo

CRM WebService Error: Only one usage of each socket address (protocol/network address/port) is normally permitted

by Luke Simpson 01.15.09

---

When performing a data integration or migration into CRM, it is very common to create a .Net application that transforms the data, then pushes the records into CRM using the WebServices.  At times, however, the load of data being pushed to IIS can be more than is acceptable to the default settings in an IIS implementation.  At these high load times, the server might post an error stating "Only one usage of each socket address (protocol/network address/port) is normally permitted (typically under load)."

What is happening, is that connections are being repeatedly opened and closed on the webserver.  When a connection is closed, the connection goes into a TIME_WAIT state for 240 seconds.  This is the default setting.  In this case, the IP being used is typically fixed, which means that the variable is the local port.  By default ports 1024-5000 are available to be used, which means that using default setting you have approximately 4000 ports to be used during a 4 minute span (240 seconds).  So if your code is making more than 16 webservice calls per second, you will exhaust all of the available ports!

To fix this problem, you can make 2 different registry changes on the CRM Application Server.

1. Increase the dynamic port range.  As stated above, the default is 5000 but this can be raised up to 65534.

   • Using Regedit, navigate to  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort (if this key does not exist, create it as DWORD value)

   • Set the value to 65534, or a value of your choice

2. Reduce the amount of time that the connection is in a TIME_WAIT state.

   • Using Regedit, navigate to  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters\TCPTimedWaitDelay (if this key does not exist, create it as DWORD value)

   • Set the value to 30

By performing the actions above, you are allowing the server to use a far larger number of available ports, and you also allow the server to recycle them faster.  Problem solved!

Tips for faster and better performance on Microsoft Dynamics CRM 4.0

There are many different factors that affect performance when implementing a CRM system; the hardware, network, infrastructure, business processes, system configuration, ect. Finding those bottlenecks and identifying their actual causes can take a lot of time and might not be an easy task.

In this post, I’ll show what to look for based on my experience from global and local CRM implementations.

Latency

When deploying MS CRM in a WAN environment, a major consideration is the distance between the server(s) and the clients in the solution. As the distance increases, the delay, or latency, associated with each round trip can also increase.

For example, for a connection between a user located in London and a server located in Stockholm, the associated round trip time is 15ms, but were the user located in China, the connection to the sever in Stockholm would require approximately 300ms.

Because downloading a CRM page can consist of multiple roundtrips, the latency associated with a particular connection can directly affect CRM performance. For the example above, a CRM page required 10 round trips to load, the user in London would see the page in less than 0.150 seconds (ten times 15ms), while the user in China would have to wait for 3 seconds (10  times 300ms).

As a result, the fewer the round trips between the client and the server, the better CRM performs.

What is the best way to account for latency during solution design? My experience indicates that during the early phases of a project, it is critical to identify any potential areas of the solution that that might require extra roundtrips. Then, if possible, limit or build smarter functionality for those areas. Try only to render what’s in context or what the user needs at the moment. Perhaps have a verification triggered manually rather than during each onload of the CRM form?

Question every requirement and think about how that specific function might have impact on performance. Using third party tools such as WAN accelerators has been successfully implemented as noted in the Optimizing and Maintaining Microsoft Dynamics CRM 4.0 documentation. The use of caching on the client is also a trick and make sure all static files are marked correctly in the IIS. Crunching and removal of white spaces improve performance in jscript added to forms. There are multiple tools out there to be used and could be part of the build process. Usage of the offline client might also improve performance if the client is used in offline mode and later background synchronized back to the server.

I hope that IE 8 will support more ports open during the load of pages. Currently IE can only have 2 ports open per domain. Microsoft Dynamics CRM 4.0 does not support the possibility split files on separated domain to have IE open more ports and then load files more in parallel.

Adding IIS fronts closer to end-users but still having a centralized SQL server does not show any better performance from my experience, rather surprising the performance was worse using that approach. What’s doing great results are adding custom indexes to DB and I would recommend using the different DBA tools available and monitor where to add new indexes.  

Centralized vs. De-centralized Topology

When deploying MS CRM in a WAN environment, you can opt for a centralized or decentralized topology. A centralized approach Using a de-centralized (federated) The two types of typology are described and compared in the following table:

Topology	Description	Pros	Cons
Centralized	CRM servers consolidated in a central location	A fully centralized infrastructure is easier to manage and maintain; consolidating resources carries lower TCO and procurement costs.	Query performance suffers from long distance access; retrieving data from remote locations significantly impacts processing speed and data latency
De-centralized	Local CRM servers are distributed around the globe	Users benefit from considerably faster query and reporting; local adoption of the solution at management level will typically be easier	Considerably higher procurement, operations, and maintenance costs; much greater difficulty in maintaining alignment of local solutions to a standard. Data volumes transferred to local implementations have high, concentrated impact on the network

Hardware Planning

What about hardware then? When planning for hardware selection, I consider the following points.

§  Number of transactions per hour – Total average number on key entities used to support business process.

§  Number of concurrent users – In global implementation user amount might be high but not all working in the system at the same time. Take advantage of the time zones. Don’t forget that outlook client talks to the servers regularly depending how you setup your synchronization.

§  Amount of customizations – Workflows, plug-ins do we need to have dedicated SDK servers? In CRM 4.0 you can separate as shown in the following table:

Services	CRM Application Server	CRM Platform Server
Web Application Server	ü
Help Content Service	ü
SDK Service		ü
Asynchronous Service		ü
Discovery Service		ü
Deployment Service		ü

§  Availability and failover – Clustering on SQL, farm on application and web fronts. Don’t forget Reporting Server! The more you secure uptime on your system -the more servers but better sleep and happier users

§  Central vs de-centralized – De centralized requires more hardware as noted above

Figure 1: Sample of suggested server topology, this is just one example, and depending on the scenario, another topology might work better.
Customer environments are unique and each implementation requires analysis and consideration of the variables in specific scenario.

 

Additional Information

Optimizing and Maintaining Microsoft Dynamics CRM 4.0
 http://www.microsoft.com/downloads/details.aspx?FamilyID=ba826cee-eddf-4d6e-842d-27fd654ed893&displaylang=en

Microsoft Dynamics CRM 4.0 Performance and Scalability White Papers
http://www.microsoft.com/downloads/details.aspx?FamilyId=5852B14A-394C-4898-8374-CAF5E6479EB0&displaylang=en

Support for Microsoft Dynamics CRM 4.0 on a computer that is running Windows Server 2008 Hyper-V
http://support.microsoft.com/kb/957054

Planning a Deployment Topology (SSRS)
http://technet.microsoft.com/en-us/library/ms157293.aspx

 

Published Wednesday, January 14, 2009 8:51 PM by Jonas Deibe

---