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