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 test the Commit SQL Database communications again 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 – 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.

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

See Also

• Commit SQL Database Installation Guide
• Commit SQL Database for Trial Users