Database Install Issue

I've sent this off to support as well but hoping maybe someone here has run into this and may have a quick answer.

We just update to the Advanced Database version of CommitCRM - SQL version essentially.

The app runs fine now on our server - and the web interface works fine - but none of our client install can connect anymore and we get an error saying we need to make sure these systems can see \\SERVER\CommitCRM\DB - which of course, they all can.

Anyone else run into this problem?

Hi,

Following additional information sent to us by email, we understand that this may be related to the Firewall settings on the server.

To clarify - the SQL Database doesn't use a special port in order for the clients to connect on your LAN. The way it works is that the clients send a broadcast message on the network, checking where the server is found; if found the server answers with the relevant information for the client to connect to it. This handshake process may require allowing UDP broadcasts inside you LAN. Usually this does not require any special setup, however, with some security settings it may be required.

Ethan

Hi Ethan,

You're correct - with some additional troubleshooting last night I narrowed down the culprit to the Windows Firewall on our internal SBS 2008 server. We had already made changes to allow CommitCRM to function over the network and it has been for some months now.

Clearly - the addition of the Database component adds some additional traffic that the firewall is denying thus the clients cannot complete the connection. It would be a simple matter of doing some packet sniffing to find out which but I was hoping your team had this documented.

Hi FunctionOne, just checking up on this - have you managed to set up your Firewall to work well with SQL database?

Ethan

Hi Ethan,

I've not had time to run a packet sniffer yet to find out just what the firewall is blocking. The only "solution" I've had time to try is disabling the Windows firewall completely.

I'm all ears for ideas of what the firewall would be blocking now that we're using the SQL?

Anyone?

I hope the following information will be useful -

First, the default Windows firewall settings should be sufficient to allow RangerMSP to communicate with SQL Database on the server. Therefore, I suspect that you've increased its default security settings...

It looks like RangerMSP client's way of finding the SQL Database Server fails in this case. In order for the client to detect where the server is running it broadcasts a message and asks for the server to answer. When the server answers the client knows the server IP and can communicate with it.

It looks like your Windows firerwall is configured to block the broadcast message to the server or any other communication to the server.

On your server, you can run the Advantage Configuration Utility. Using it you can find the Port number which is used for communicating with the clients.

Make sure that the Windows firewall on each client on your LAN allows communication with the server for this port.

Verifying this will allow communication between the RangerMSP client and SQL Database on the server.

However, in some cases this is not enough. As explained above, a discovery process takes place when the clients loads, trying to find the server... It is possible that your Windows firewall blocks this discovery process even if the port is allowed.

Therefore, in case you enabled the port on each client (using the firewall settings window) and RangerMSP client still doesn't load, you should continue with the following (do not continue with this before testing whether allowing the port has solved the issue):

Under the <server>\RangerMSP\Client folder create a text file called ADS.INI
Open this file in Notepad and add the following settings:

[MYSERVER]
LAN_IP=192.168.0.1
LAN_PORT=2001

IMPORTANT NOTES:
Replace MYSERVER with the name of YOUR server
Replace the 192.168.0.1 IP address with the LOCAL IP address of your server
Replace the port number 2001 to the one used for SQL Database in your setup (this can be viewed by running Advantage Configuration Utility on your server, look for the port in use for your LAN.

After adding the relevant settings, save the file and then try to run RangerMSP client.
This file tells the RangerMSP client what the server IP is and what port to use and therefore no discovery process is required, avoiding the broadcast stage.

One thing to remember is that in case you will ever change the IP of your server on your LAN, you should make sure to update this file again to reflect the change. this is why we usually recommend on allowing the broadcast rather than using this hard-coded way of communication.

I hope this helps.

Ethan

Ethan,

I am just trying the SQL Database and experiencing the same problem with SBS2008. I made the changes you mentioned above and it doesn't appear to help. The clients take forever to launch and then yields a database connection error. Any other suggestions?

--AJ

Hi AJ,

usually similar problems indicate some kind of security settings which prevent from the clients to access the Database Service on the server.

The most common solutions would be:

1) Make sure the DEP settings are defined correctly on your server – You should add the Advantage Database Server service to the allowed DEP list. DEP can be found under Windows: System Properties -> Advanced -> Performance - Settings -> tab DATA EXECUTION PREVENTION, otherwise the service is isolated and does not requests are transferred to it.

2) Add an Inbound Firewall rule on the Windows 2008 server that allows any communication to the ads.exe program.

On of these should do the trick. Let us know how it goes.

Ethan

Ethan,

I've done all of that. I even turned off Windows Firewall temporarily with no luck. Any other ideas? The client works fine on the server.

--AJ

Definitely sounds like security which prevents from the clients accessing the service, especially if this works on the server itself. Stupid question - have you restarted the server after making the changes in DEP and in the Firewall?

Ethan

Ethan,

I got it working it seems but it takes several minutes for the client to load. I see it running in the task manager for quite sometime and then eventually the client login window shows up.

--AJ

I'm glad it's working now :-)

As for the upload time, have you tried using the direct IP settings in the ADS.INI file (as suggested above?) - I'm not sure this is related to the server discovery, but if it is, this may speed this up.

Generally, I'm not sure whether this is related to using the SQL Database version. Has this happened also before using it?

Ethan

Ethan,

It turns out it was Trend Micro Worry Free Business Security Advanced's Behavior Monitoring. I turned it off on clients and servers and it started to work.

--AJ

So would it be possible to move the CommitCRM program files to a laptop and use the ADS.INI file to tell CommitCRM that the data is hosted on a remote machine? (as in over a VPN)?

I'm sure you are probably going to say this isn't supported, but is there a reason that we couldn't do that? This may be a big help for remote users!

Luke

The Ads.ini tells the client where SQL Database server is (i.e. its IP address). You cannot currently move the program files to a local folder on your PC, there are several reasons for this including program upgrades, version control, etc. however, over VPN I can see why it makes sense as it may reduce the time it takes to load the client. We'll keep this in mind. Thanks.

Ethan