SQL Database Troubleshooting

Problem Description

When setting up the SQL Database for CommitCRM, the client PCs running the CommitCRM client application cannot connect to the CommitCRM 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:\CommitCRM\DB)

Explanation

This problem occurs when the client PCs try to connect to the SQL Database for CommitCRM, 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 SQL Database for CommitCRM 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 SQL Database for CommitCRM, 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 SQL can communicate with the CommitCRM clients, we ask that you cover the following steps.

• Please remember to test the SQL communications again after applying each step, and if the issue persists, then the next step should be applied.

Advantage Server

• Verify 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 SQL.

• Please restart all CommitCRM services, and retest communications by loading a CommitCRM Client application from another computer.

DEP – Data Execution Prevention

• Please ensure that if using DEP on the server, where SQL (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 SQL Database for CommitCRM / Advantage Database Server Windows service executable file and therefore should be allowed, or else DEP may isolate the SQL software from communicating with the CommitCRM client applications.

• Please restart the Advantage service & all CommitCRM services, and retest communications by loading a CommitCRM Client application from another computer.

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.
• 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.
• Add an Inbound Firewall rule on the Windows Server that allows any communication to the ads.exe program on your server.

• Please restart the Advantage service & all CommitCRM services, and retest communications by loading a CommitCRM Client application from another computer.

ADS.ini – Server Locator

• The CommitCRM client's way of finding the 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 SQL 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 SQL 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>\CommitCRM\LastVer 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 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.

• Please restart the Advantage service & all CommitCRM services, and retest communications by loading a CommitCRM Client application from another computer.

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:

[192.168.0.1] 
LAN_IP=192.168.0.1
LAN_PORT=2001 

IMPORTANT NOTE:

• Please replace the ServerName (between the brackets) with the servers IP address, as displayed in the example above.

• Please restart the Advantage service & all CommitCRM services, and retest communications by loading a CommitCRM Client application from another computer.

Important Note For Users that Use our ODBC API

I some cases your system may still present an error message regarding failure of the Discovery process (Advantage Error 6320) when trying to access to the CommitCRM database using an ODBC connection, even after the ADS.ini file has resolved the issues with CommitCRM clients being able to discover the Advantage server. The following solution should resolve this issue as long as the following conditions are met:

• You use the SQL (aka, Advantage Database Server on your server) and ODBC.

• The ADS.ini file succeeded in logging in the CommitCRM Software Applications (Clients).

• When using applications you developed with ODBC you receive a message regarding Database Discovery Failure (Error code 6320).

If all of the above prerequisites exist, you should copy the ADS.ini file you prepared from the CommitCRM server folder into the following folder on each the affected workstation:

• %Windir%\system32

This folder usually translates to: C:\Windows\system32

Note: To open these folders on your PC you can paste the paths listed above into a Windows Run dialog box, or into any Windows Explorer path.

Recurring issues after Rebooting the CommitCRM Server Machine

The CommitCRM database chooses its database mode upon the first concurrent connection to the system. If a client application, or CommitCRM Addon Service (Such as CommmitCRM WebInterface, CommitCRM Server or CommitCRM RMMServer) service is already loaded and connected to the server before the Advantage Database Server service finishes loading, then all subsequent connections will be rejected since the prior connection to the database would not allow the Advantage Database Server service full control over the database.

Typical characteristics of this type of problem are repeated manual attention needed to properly connect Advantage Database Server to CommitCRM after rebooting, and restarting the Advantage Database Server service. To verify this is the issue, please follow these steps:

1. Stop any CommitCRM Services installed in the CommitCRM server.
2. Verify no other computers are running CommitCRM.
3. Restart the Advantage Database Server Service.
4. Once the Advantage Database Server service has started, Try Logging in with one client on the server machine.
5. Check the CommitCRM window title to see if SQL is printed next to your username.
6. If successful, Start CommitCRM Services.

If this process was successful, this is because the CommitCRM Services loaded before Advantage Database Server did, locking Advantage Database Server out of the CommitCRM database. To permanently resolve this behavior, manual rules will need to be created in order to ensure that The CommitCRM Services do not try to load without Advantage Database Server being started.

Adding Service Dependency Rules

Now that CommitCRM depends on the Advantage Database Server Database service, the CommitCRM Services (CommitCRM WebInterface, CommitCRM Server and CommitCRM RMMServer) also need the Advantage Database Server server to to be up before they try to load. To set these services to be dependent on the Advantage Database Server Service, follow these steps:

1. Backup your current registry settings.
2. Run regedit to open your registry.
3. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and locate the CommitCRM WebInterface, CommitCRM Server or CommitCRM RMMServer services that you need to set a dependency for.
4. Open the DependOnService key on the right side. If the selected service does not have a DependOnService key, then create one by right-clicking and selecting New > Multi-String Value.
5. In the value field, enter Advantage.
6. Click OK, close your registry and restart your machine.

See Also

• SQL Database Installation Guide
• SQL Database for Trial Users