Database connectivity problems

Amongst the most common problems with running software over networks is when the software cannot connect to its database. Sometimes the software runs happily on a server but not on a workstation. This chapter is intended to address these kinds of issues. There are five main areas to check, starting with the simplest:

  1. Have you forgotten to install the client tools?
  2. PING the server.
  3. Restarting the SQL Services
  4. Checking your network protocols
  5. Create a manual DSN to test the connection to the server

Have you forgotten to install the client tools?

The Number One technical support call to Lucid Research is when the person deploying the Lucid software on the network neglects to install one or both of the two packages needed on every workstation:

(a) The Thin Client msi (client tools).

(b) The SQL Server Native Client msi, which allows a link with the SQL Server (see section 2.4 below).

Ping the server

From Start > Run type command and in the black window enter

ping <ip address>

where ip address is the address of the server which hosts the SQL Server.

Check for any mismatch on the returned results. If you are not able to ping your target machine, there is a good chance that either the network is broken or the target machine is not running. It's possible the target machine is behind a firewall and the firewall blocks the packets sent by ping, though Windows firewall does not block ping (ECHO) packets by default.

Restarting the SQL Services

Launch SQL Server Configuration Manager.

SQL Server 2008 R2 Express

Right mouse click over the service and choose Restart from the menu. (see example below).

Note: You can perform this same task by choosing Administrative Tools > Services on your server and looking down the list of services to find SQL Server and SQL Server Browser.

Check your network protocols

Again, launch SQL Server Configuration Manager and choose the Protocols as shown in one of the two screens below.

TCP/IP should be enabled.

Named Pipes should be enabled (unless you have good reason to disable it). Shared Memory and VIA are not normally used so don’t enable these if they are disabled.

Create a manual DSN to test the connection to the server

If you cannot manually link to the database from a suitable remote workstation then that suggests a problem with a firewall or services not running. Here’s how to to create a System DSN.

Windows 7

Control Panel > search for ODBC

1. Select System DSN and then Add

2. Choose the most appropriate ADO driver

For SQL Server 2005 use SQL Native Client

For SQL Server 2008 R2 use SQL Server Native client 10.0

For SQL Server 2012 use SQL Server Native client 11.0

3. Give the DSN a name, optional description and choose the Server\Instance.

4. Choose the SQL Server authentication option and enter the Login ID and password. By default these are as below:

Move by clicking on Next and ensure the correct default database is shown (choose from the table below):

5. At the next screen select Finish.

6. You should finally see the Test Data Source screen which should give a ‘Test Successful’ message upon clicking on the large button if the SQL database is visible to the network and the Login is working.

Recreate the connection string to the database

If you suspect you may have chosen the wrong type of Native Client or you have since moved the database to a different instance of SQL Server you may wish to recreate the connection string used by the software to locate the database; do this by simply deleting the file called server_config_2.dat in the folder \data off the Lucid application folder.

Finally, run the tool ‘database installer tool deluxe.exe’ from the application folder itself. This tool recreates the encrypted connection string file you deleted.