gustafwesterlund: Finding the TCP port for Dynamic Excel Export

[Blog bot]

Источник: http://gustafwesterlund.blogspot.com...mic-excel.html
==============

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/e...7-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:\Users\administrator>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:\Users\administrator>

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


Источник: http://gustafwesterlund.blogspot.com...mic-excel.html