The dynamic Excel export functionality of Dynamics CRM is a real killer functionality. I often get standing ovations from users, especially managers, when showing dynamic pivot table functions of CRM, partly due to the fact that many users don’t actually know what pivot tables are and find them rahter magical.
I was working with a demo environment today and had some issues getting the dynamic excel to load data from CRM properly. I got the error message: “Connection failed”. This was a lab environment with a separate Windows 7 virtual machine in an on-premise installation. This is important, because in IFD or CRM-online, dynamic excel data is tunneled through the outlook addon, but not when using the on-premise installation when it communicates directly with the server.

So I started looking for answers on the internet and found the following thread on the CRM forum which helped out a bit: http://social.microsoft.com/Forums/en/crmdeployment/thread/c2d3029c-5487-435b-90e7-ba030aeba8ac

I started by trying to do the obvious thing by opening the TCP port 1433, the SQL communication port in the firewall. This did not help. According to some of the participants of the thread, this KB article by Microsoft might help: http://support.microsoft.com/kb/968872 – it does more or less the same thing, opens 1433 and some additional ports in the firewall all related to SQL. However, it did not help either.

One of the participants of the thread mentioned that he had opened the TCP port 53021, something I found very strange if it was to work, since ports this high are not supposed to be used as incoming ports but are ephemeral ports. I tried opening this port as well, but it didn’t work either. The port number seemd sort of random so it might be that my deployment just used some other high port number.

A colleague of mine working at Hermelin IT-Partner, Anders Jildestrand, then joined me in the hunt for the mysterious TCP port. With his assistance and netstat we got the following result:

C:Usersadministrator>netstat
Active Connections
  Proto  Local Address          Foreign Address        State
  TCP    192.168.75.15:3389     192.168.75.131:29837   ESTABLISHED
  TCP    192.168.75.15:52422    192.168.75.13:5555     ESTABLISHED
  TCP    192.168.75.15:52426    WIN-LGEKH2VQ6FI:epmap  TIME_WAIT
  TCP    192.168.75.15:52427    WIN-LGEKH2VQ6FI:49155  TIME_WAIT
  TCP    192.168.75.15:52428    192.168.75.13:63831    SYN_SENT

C:Usersadministrator>

And if you are sharp eyed, you might spot it, as we had the SQL-server on the CRM server, and the TCP-SYN was sent but never answered, it is of course the last row, hence the TCP port 63831.

After opening this port in the firewall, the dynamic Excel worked just like it should.

The question now remains. Why this port?

We also had a discussion on which program, CRM or SQL Server that is actually answering this call, and it seems that it is actually SQL Server since the ODBC connectionstring in the Excel-file is directed to the SQL-server, not the CRM-server. But I think we could probably investigate this further with fiddler. If you have any interesting input, please leave a comment!

Gustaf Westerlund
CEO, Chief Architect and co-Founder at CRM-konsulterna AB
www.crmkonsulterna.se