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

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!

Cannot change Business Unit.

Wow, that was rough! I have a client that had a very weird error. Every time you tried to change someone’s business unit you received the all-so-helpful error page that told you nothing. Looking into the event logs held no errors and the trace logs were difficult to ponder as well. That led to a call to MS for help. In the end it appeared to be a timeout issue while it was trying to change the business units.

Within the logs they found the following error:

Crm Exception: Message: , ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

If you read the article http://support.microsoft.com/kb/918609 it appears to be around customizations and uploading them, but it worked in this instance as well.

To resolve this problem, use one of the following methods.

Method 1: Add the OLEDBTimeout and the ExtendedTimeout registry subkeys to increase the time-out values

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

1. Click Start, click Run, type regedit, and then click OK.



2. Locate the following registry subkey:





• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM





3. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.



4. Rename the DWORD value to the following value:





• OLEDBTimeout





5. Right-click the DWORD value, and then click Modify.



6. In the Edit DWORD Value dialog box, type 86400 in the Value data box, click Decimal in the Base option, and then click OK.
   
   
   Note According to the requirement of the computer that is running SQL server and the number of customization files, the value can be larger than 86400. The value of 86400 is equivalent to 24 hours.



7. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.



8. Rename the DWORD value to the following value:





• ExtendedTimeout





9. Right-click the DWORD value, and then click Modify.



10. In the Edit DWORD Value dialog box, type 1000000 in the Value data box, and then click OK.
    
    
    Notes

• In the Value data box, you can type a value that is larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. This is hexadecimal 0x7FFFFFFF.



• If this key already exists, notice the current value. After you have completed the import or the upgrade for Microsoft Dynamics CRM, set the value of this key back to the original value or delete the key if it did not previously exist. The default OLEDB timeout value is 30 seconds.

Method 2: Modify a parameter in two different Web.config files

1. Click Start, click All Programs, click Administrative Tools, and then click Internet Information Services (IIS) Manager.



2. Expand the server name, and then expand Web Sites.



3. Right-click the Microsoft CRM v3.0 Web site or the Microsoft CRM v4.0 Web site, and then click Open.



4. Right-click the Web.config file, click Open With, and then click Notepad.



5. In Notepad, locate the following line.





• <httpRuntime executionTimeout="300" maxRequestLength="8192"/>





6. Increase the "8192" value to a value that is large enough to handle the Customization.xml file.



7. Save and then close the Web.config file.



8. In the directory in which you opened the Web.config file, open the MSCRMServices folder.



9. Right-click the Web.config file, click Open With, and then click Notepad.



10. In Notepad, locate the following line.





• <httpRuntime maxRequestLength="8192"/>





11. Change the "8192" value to the value that you specified in step 6.



12. Save and then close the Web.config file.

The OLEDBTimeout value

The unit of the OLEDBTimeout value is seconds. By default, the OLEDBTimeout value is 30 seconds. The OLEDBTimeout value controls the SQL time-out value that is used for a single SQL query. The increased OLEDBTimeout value is useful when the SQL server is overloaded. Additionally, the query takes a longer time to process.

The ExtendedTimeout value

The unit of the ExtendedTimeout value is milliseconds. By default, the ExtendedTimeout value is 1000000 milliseconds. The ExtendedTimeout value controls the ASP.NET time-out value that is used for import requests. The time-out value must be larger than the time for finishing the whole import process. The increased ExtendedTimeout value is useful when the import process takes a long time.

Microsoft CRM Outlook client and authentication balloons

Posted by Mike Snyder on July 1, 2008

---

You know those little "balloons" that pop up from time to time in the Outlook 4.0 client? Below are some registry settings to change the Outlook client's behavior around network connectivity, notifications and so on. Our support manager recommend increasing ClientAuthAllowRetries to 10 and ClientAuthNotificationThrottle to 60000 (60 seconds), but you can obviously tweak to fit to your unique environment.

Regkey Name	Default	Description
ClientAuthNotificationThrottle	30000	Specifies the required elapsed time before which another balloon notification may be displayed (helps prevent too frequent balloon notifications).
ClientAuthRenewPeriod	60000	Specifies how frequently the hoster process will check if it should renew authentication.
ClientAuthVerifyConnectionPeriod	30000	Specifies how frequently the hoster process will check connectivity to the web application by downloading a tiny icon (anonymous access).
ClientAuthVerifySignedInPeriod	5000	Specifies how frequently the hoster process will check if authentication has been lost (ex: detect if cookies have been deleted or the CRM ticket has expired).
ClientAuthAllowRetries	3	Specifies the number of failed authentication attempts (after this an error will be shown or the client will move to the offline state).

Posted by Mike Snyder on July 1, 2008 in Microsoft Dynamics CRM 4.0