SQL Database Troubleshooting
Problem
When setting up the Commit SQL Database (CSD), the client PCs running the CommitCRM client application cannot connect to the Commit SQL Database, and an error is displayed stating that the system cannot access the \\SERVER\Commit\DB folder, please make sure your system can see this folder.
Explanation
This problem occurs when the client PCs tries to connect to the Commit SQL Database, which is installed on your server, and the server doesn’t “answer” to their connection requests. By default, CommitCRM clients send a UDP broadcast message on the network, checking where the Commit SQL Database server is found; if found the server answers with the relevant information for the client to connect to it (IP, Port, etc.). 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. It is also required that the Commit SQL Database, which runs on your server as a Windows service called Advantage Database Server, will be up and running and be allowed to communicate with your LAN.
TroubleShooting Steps
In order to assure the Commit SQL Database can communicate with the CommitCRM clients, we ask that you cover the following steps (Please remember to retest the Commit SQL Database communications after applying each step):
Advantage Server
- Verfiy the Advantage Database Server service is started on your server. For installation instructions click here or here if your are using a trial copy of CSD.
DEP
- Please ensure that if using DEP on the CommitCRM server, we highly recommend configuring DEP to allow data execution from Advantage.exe & CommitCRM.exe.
- The CommitCRM client computers may need a DEP exception configured so that the CommitCRM.exe client will not be halted.
Windows Firewall
- Make sure that your software firewall allows the Advantage port through the firewall. Also, make sure that no special UDP restrictions are activated on the CommitCRM server.
- The default settings configured in the Windows firewall are sufficient for use with the Commit SQL Database, unless the firewall has been reconfigured.
ADS.ini – Server Locator
- The CommitCRM client's way of finding the Commit SQL Database Server fails. In order for the client to detect where the server is running it broadcasts a UDP message and asks for the server to answer. When the server answers the client knows the server IP and can communicate with it.
- 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 CommitCRM client and Commit SQL Database on the server.
However, in some cases this is not enough. As explained above, a discovery process takes place when the client 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 CommitCRM 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>\Commit\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 Commit 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 CommitCRM client.
This file tells the CommitCRM client what the server IP is and what port to use and therefore no discovery process is required, avoiding the broadcast stage.
Please 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, however, in some configuration it doesn’t work and using an ADS.ini file is mandatory.
ADS.ini – Server Locator (Advanced)
- If the Ads.ini file does not correct the issue, then we suggest that changing the Server hostname reference in ADS.ini to the server IP address [192.168.x.x] will correct this behavior. (Remember to restart Advantage Service)
For example:
[192.168.0.1]
LAN_IP=192.168.0.1
LAN_PORT=2001