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:\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
Hi Gustaf, Nice blog !! I don't know why Microsoft has been so mysterious about the ports that MSCRM works with. Many times, consultants like me face the wrath of the client due to this. They blame it on our incompetence. Recently I installed MSCRM for a semi-govt organization in a distributed network where the network is very secure and opening a single port requires approvals, escalations and time and loads of explanations/proofs which are unfortunately not many on the web. You can imagine the pain and iterations. I don't really see the purpose of not declaring and documenting the ports in Install Guide. Is it related to potential exploits on those ports ? Your thoughts plz
ReplyDeleteYes, I agree it is strange this isn't documented better. I presume the reason for changing the port between system all the time is to make it harder for hackers to find it without portscanning as it could probably be used to exploit the system with malicious SQL.
DeleteIt is a hazzle that they don't document how it works though since we need to get it to work for our customers. Why, I don't know.
You can imagine the pain and iterations. I don't really see the purpose of not declaring and documenting the ports in Install Guide. Is it related to potential exploits on those ports ? Your thoughts plz
ReplyDelete@Online Cloud Backup, well, I guess it is. I discussed it with a friend who is an IT expert and works some with SQL-server. His comment was that SQL Server 2008 has a lot of strange features addet to it, one thing being random ports for SQL connections. Accordning to him, it is supposed to be a setting somewhere in the SQL-server, and I wonder if it would revert back to 1433 if changed. The only reasonable explanation to it from my point of view is to reduce the attack area, and confuse potential attackers since the SQL-port is such a common entry point for attacks.
ReplyDelete