SQL Database Troubleshooting
Problem Description
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 saying:
Procedure halted due to data server error.
Verify server is operating and this computer has access to the server folder.
(C:\Commit\DB)
Explanation
This problem occurs when the client PCs try to connect to the Commit SQL Database, which is installed on your server, and it 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 advanced network 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 test the Commit SQL Database communications again after applying each step, and if the issue persists, then the next step should be applied.
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 – Data Execution Prevention
- Please ensure that if using DEP on the server, where Commit SQL Database (aka, Advantage Database Server) is installed, it should be configured to allow data execution for ADS.exe, by default located in the following folder C:\Program Files\Extended Systems\Advantage 9007\Server. This file is the Commit SQL Database / Advantage Database Server Windows service executable file and therefore should be allowed, or else DEP may isolate the CSD software from communicating with the CommitCRM client applications.
Firewall - Windows or any other installed Firewall
- Make sure that your software firewall allows the Advantage port through the firewall (Advantage prot information can be adjusted from Start Menu > Programs > Advantage Database Server 9.0 > Advantage Configuration Utility > Configuration Utility tab > Communications tab). 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.
- If advanced firewall restrictions are configured, please verify that an exception has been placed in the firewall, so that the software may communicate with the network, as well as allowing UDP traffic through the firewall.
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 (Start Menu > Programs > Advantage Database Server 9.0 > Advantage Configuration Utility > Configuration Utility tab > Communications tab). Using it you can find the Port number which is used for communicating with the clients. When adjusting the CSD ports, you can use any port you like, as long as the port is not being used by any other software.
As explained in the previous steps, you should 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 that in case you 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 method of communication; however, in some configurations it doesn’t work and using an ADS.ini file is mandatory.
ADS.ini – Server Locator (Supplemental)
- 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 each time ADS.ini is adjusted)
For example: ,br.
[192.168.0.1]
LAN_IP=192.168.0.1
LAN_PORT=2001