Finding the TCP port for Dynamic Excel Export

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

Running Microsofts Demo VPC on Windows 7

Running Microsofts CRM Demo VPC (Available on Partnersource) on Windows 7 is a bit problematic but can be done. I found a good blog that showed how it can be done using VirtualBox but there were some extra things that needed to be done to make it work.
As many of you probably are aware, from Windows Server 2008, only 64 bit versions of the server operating systems are available which isn’t very strange since the 32 bit versions of Windows only support up to 3.5 GB of memory which is really enough for even a small server.

Hence the Virtual Machine that Microsoft have released with a demo environment of CRM 2011 is based on this 64 bit environment. Since Windows Virtual PC, the Virtualization Technology for Windows 7 does not support 64 bit guest operating systems, it has been created on Hyper-V and is meant to be run on this virtualization technology. However, this is not really what you average CRM salesperson is running in his or her Lenovo X11 for many obvious reasons, power management probably being one of the most striking.

So, it is it just not possible to run the demo environment on a Windows 7 machine? Yes, it is. On the blog Leon’s CRM Musings there is a very informative posting on how to make the virtual harddrive work in the VirtualBox virtualization technology. I was quite amazed when I saw this working as this is quite similar to installing an operating system on a computer, ripping out the harddrive and screwing it into a completely different computer and hoping it will work. However, the Hyper-V virtualization and VirtualBox virtualization must be very similar from the guest operating system perspective, otherwise the operatingsystem would have given a lot of errors.

Do note the part in the posting that the demo image does not support being run as a SATA drive but must be run as an IDE harddrive.

There are some comments that both SharePoint and CRM are installed on port 80. This is in part true, but they are installed on different websites separated by different host headers.

However, the sharepoint site did not work when I tried running it, hence the document manangement from CRM did not work either. After checking the IIS website bindings and the host-file (C:system32driversetchosts) I noticed that the host headers for the SharePoint site and some other sites were pointed at the IP 192.169.100.1 and a quick check with ipconfig showed that this was not the ip of the current computer. Hence I just changed the mapping to be 127.0.0.1 (always means “this” computer).

I then closed all IE-windows, flushed the DNS (ipconfig /flushdns) just to be sure, then tried the sharepoint site, and it worked. As did the document management in CRM.

I saw some people in Leon’s blog commenting on the networking issues of making the exchange server communicate with the CRM-server. This can be done be setting the networkadapter in VirtualBox to “Internal Network” on the Exchange machine and using two adapters on the CRM machine, one with NAT (external) and one internal using the “Internal Network”. Both the internal networks adapaters need to be configured manually, but the external one can have a dynamic IP. If anyone doesn’t know how, please leave a comment and I will tell you how.

Regarding optimization of speed I strongly recommend upgrading your computers memory to at least 8 GB so that you can give the virtual machine between 4-6 GB. Try to give it even multiples of 512 MB as that will make for better memory management.

Another well known fact regarding VPC:s is disk access. It is highly recommended that you use a separate disc on a high speed controller (i.e. USB 3.0/eSATA/internal SATA).

As for processor power, it is not as essential, eventhough it is always nice. Try to allocate at least 2 cores to it. One for the SQL-server and one for IIS/GUI.

The recommendation that Leon gives to reduce the memory for the SQL server I am not so sure that is a good idea as CRM is very SQL heavy due to its meta data driven nature. However, if you only have 4 GB:s of memory and you have to make this VPC work, then you might have to try it. Kingston memory modules are very cheap now, 2×4 GB for a T410 is less than €100 last time I checked.

I have several computers, my “sales computer” is a Lenovo T410s Corei5 8Gb with 2 SSD HD. I use one of the harddrive for my host operating system and one for the virtual machines. I have removed the DVD-drive since I never use it, and the extra drive is a lot better use of the space.

Last of all, cudos to Leon for a great solution with VirtualBox for the VPC!

Update:
An additional thing to do is to convert the VHD to the Virtualbox native VDI-format for virtual harddrive using the media Media manager in VirtualBox. This is probably a more optimized format for Virtualbox and hence a bit Quicker. (I have not tested it, it is just a thought)
I have also tried to run the VPC on an external USB SSD, both USB 2.0 and USB 3.0 with 2 GB of RAM with 2 Cores on my Core i5 with the converted disk and it works fine. The SSD gives it a lot of extra speed.

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

Cross browser and CRM Mobile

Cross browser and CRM Mobile

Today Dennis Michalis, General Manager of Dynamics CRM at Microsoft announced that in Q2 2012 a service update of Microsoft Dynamics CRM 2011 will include cross browser functionality to allow users on a multitude of browsers and operting systems to finally be able to use all the great functionality of Dynamics CRM without having to use workarounds like virtualization or terminal services.

Supported Browsers (From Microsoft Roadmap PDF)

He also announced that Microsoft Dynamics CRM Mobile will be released, a Micrsoft based product for bringing CRM functionality to Windows Phones, iPads/iPhones, Androids and Blackberries.

CRM Anywhere on iPad (From Microsoft Roadmap PDF)

There were also some other updates that were announced that you can read about in the Service Update Roadmap Q2 2012.

The cross browser functionality is something many of my customers have been waiting for a long time and I really look forward to seeing it. My colleague Rickard noted that no Unix/Linux based systems were mentioned in the matrix which is notable. According to what they previously have mentioned the mutlibrowser version will be based on HTML5 there might hence be two reasons for it not being included. Either it is not tested or there might be parts of the system that required Silverlight or some other component that only exist on Windows and Mac operating systems.

The other notable thing is that the mobile client will be big drawback for companies like Resco, TenDigits and CWR Mobile who have very competent mobile clients. This is always a risk for companies working with products close to Microsofts since Microsoft always need to expand and grow. So, if you are a company looking to invest in a mobile solution, think twice, it might be a good idea to wait a month or two and have a look at the Microsoft version of the product. It will require an extra cost so there will still be room for the other mobile clients but as others have previously noted, it is always hard to compete with Microsoft. Their mobile products do still have several upper hands on Microsoft Dynamics CRM Mobile in this early version for example,  it does not support offline functionality and I would presume the mobile API is not as extensive as that of Resco or CWR Mobile (I do not have any experience of TenDigits).

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