Connectivity: Connectivity Trouble Shooting

The information in this article applies to:
SOAPware 4.x running against SQL and MSDE databases
Many of these solutions don't apply to users with Access Databases

General Information

To understand how to trouble shoot connectivity problems, you must first understand some background information about how the SOAPware client retrieves its data. When SOAPware is launched and used it contacts the ODBC SQL Data Server Driver, which we will simply call ODBC, then passes the request for data to the SQL or MSDE server. The request from SOAPware contains a username and password, this username (soapuser) and password (soapware) are checked at the SQL server. If the soapuser account is in the database and it has the correct permissions/roles then the server will return the data back to ODBC and then ODBC will return the data to SOAPware. Something like this:

Because SOAPware uses ODBC and is constantly making connections to the server, it must have a good constant network connection. ODBC is not vary tolerant of network interruptions or loss of packets. By default the SQL server is using TCP/IP connections across the network. The most common Ports used for Microsoft's MSDE and SQL servers are UDP 1434 and TCP 1433 although the TCP port can vary server to server. Now that you understand how things work we will look at what commonly goes wrong.

Additional technical information on SOAPware and SQL interaction

Common Problems and Solutions

Loss of Network Connectivity
The loss of network connectivity is frequently the cause of client failure. This first thing to remember is that the Microsoft ODBC connection must have a good constant connection to the databases. Any lose of communication from the server to ODBC will result in error. This can also be caused by too many current connections to the database which is most often caused by sites using Terminal Services. To ensure this is not the problem reboot all the machines on the network then bring up one client and see if the problem still persists. The most common error will look something like this:

The Microsoft(tm) ODBC connection experienced the following error:
General network error. Check you network documentation.
ConnectionWrite(send()).
Location- DoWeTurnOnSecurity #1

The problem is obvious, you have a "General network error", but finding where the lost connection is may be complex. Generally, you will find that you have a bad network cable some where between the computer with this error and the data server. A systematic process of discovering the problem starts by finding if all computers receive this message, or if only one specific computer exhibits this behavior. If only one computer presents this error you can narrow your search to a specific network segment. The problem is either in the network switch/hub port, or the cabling from the switch to the computer or the network interface card in the computer.
If it happens on more than one computer your entire network is suspect. It can be a helpful tool to ping the server with a load from all computers at one time to try to help isolate the problem in this environment. If you see a pattern of "Request timed out." notices you can start to narrow down what is causing the problem. This error can not be fixed by uninstalling and reinstalling SOAPware, this is a problem with your network. Network configuration is outside of SOAPware Support Agreement, please consult your local Network Engineer for network configuration issues.

Using the Connection Troubleshooter
If your using the "Connection Troubleshooter" utility, type the IP Address of the server in the IP Address box, click the "Ping IP Address." Observe the results, if you receive the results of "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond" then you have a failure in your network. See above for more details on trouble shooting your network problem.

No Data Source Name (DSN)

Many times the Client Database Setup program has not been ran or the DSN's were accidentally removed, resulting in the following error when SOAPware is launched:

The Microsoft(tm) ODBC connection experienced the following error:
Data source name not found and no default driver specified
Location - Create User DB Connection Cache

The SOAPware Database Setup program allows you to choose the SQL/MSDE server, choose the Authentication method (most use SQL Server Authentication) and then Adds the Data Source Name (DNS) to the ODBC Data Source Administrator . For explanation on how to run this tool see http://www.docs.com/helpfiles/datasourcenamenotfound.htm

Using the Connection Troubleshooter
If your using the "Connection Troubleshooter" utility click the "Test SOAPware Account." Observe if any of the results end with "...Data source name not found and no default driver specified." If this statement is present run the SOAPware Database Setup program as explained on this page http://www.docs.com/helpfiles/datasourcenamenotfound.htm

Access Denied or Login Failed

The request from SOAPware to the database contains a username and password. This username (soapuser) and password (soapware) are checked at the SQL server. If the soapuser account is in the database and it has the correct permissions/roles then the server will return the data back to ODBC and then ODBC will return the data to SOAPware. If the permissions for the soapuser or the password are not set correctly you will receive one of the two the following errors:

Connection failed:
SQLState:'08004'
SQL Server Error:4060
Server rejected the connection; Access to selected database has been denied

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
Microsoft ODBC SQL Server Driver SQL Server Login failed for user 'soapuser'.

The SOAPware Data Server Manager can repair this user account and it's permissions. This utility can be downloaded if needed (http://www.docs.com/support/downloads.asp). In all likelihood it is already present on the server. For explanation on how to run this utility see http://www.docs.com/helpfiles/serverrejectedtheconnectionaccesstodatabasehasbeendenied.htm.

Using the Connection Troubleshooter
If your using the "Connection Troubleshooter" utility click the button "Test SOAPware Account." Observe if any of the results end with "...Cannot open database requested in login 'sw_DatabaseNameHere' Login fails" If this statement is present run the SOAPware Data Server Manager utility as explained on this page http://www.docs.com/helpfiles/serverrejectedtheconnectionaccesstodatabasehasbeendenied.htm
An example of the error as described above would be,

"ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'sw_charts'. Login fails. "

SOAPware Fails To Load or Never Gets Past Splash Screen

The SQL or MSDE server listens to request on a specific port number. When a request is made from ODBC it makes this request via a port number. The port number that ODBC communicates and the port number that the server is listening to must be the same. If it is not you will received one of these two scenarios:

Scenario 1: SOAPware is launched and the purple splash screen appears, but it never gets past the splash screen.
Scenario 2: SOAPware is launched and the following error appears:

Connection Failed:
SQLState:'01000'
SQL Server Error: 100061
[Micosoft][ODBC SQL Server Driver][TCP/IP Sockets] ConnectionOpen (Connect()).
Connection failed:
SQLState:'08001'
SQL Server Error:17
[Micosoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

No Connection could be made because the target machine actively refused it.

One of three possibilities problems exits:

The SQL server is turned off, either the computer or just the SQL Server.
A firewall is blocking the port that SQL is using, most SQL servers use TCP port 1433 and UDP port 1434.
The SQL or MSDE server is not using the default port of 1433 and the client is not auto detecting the port.

# 1. Resolution - Check and see if the server computer is running, if it is, check to make sure the SQL server is running. To make sure the SQL server is running, start the SQL Server Service Manager by clicking on Start, Run, in the "Open:" field type sqlmangr.exe and click OK. Once the SQL Server Service Manager is running you will either see a Green arrow for running or a Red square for stopped. If Stopped, click the Start/Continue button.

# 2. Resolution - Firewalls generally close port 1433 because of SQL's past vulnerability to worms. The solution is to allow the correct port to be opened to the internal network. Microsoft still recommends that external firewalls block your SQL ports. Make sure of what port your SQL server is using. Please check with your local network administrator to see if your firewall is blocking that port.

Using the Connection Troubleshooter
If your using the "Connection Troubleshooter" utility follow these steps:

Choose the radio button next to TCP, Check to see what port MSDE or SQL is using and place it in the adjacent text field
In the "IP address" text field, type in the servers IP
Click the "Scan Port Now" button

If you receive anything other than "Connection OK " in the "Results:" area, this computer can not reach the servers SQL port. If it can not make a connection and your network is working properly, you need to contact your network administrator to have them open your SQL port. The results for "Scan Port Now", can be helpful in diagnosing these problems.

# 3. Resolution - The ODBC driver uses the default port of 1433 or it dynamically resolves the port. Unfortunately, not all ODBC drivers resolve the correct port when the SQL or MSDE server not using the default of port 1433. First you must find the correct port number that your server is using. Then you must open your ODBC's network Library Configuration tool and manually set it for this new port. This must be done on all 6 SW mappings, SW Charts, SW Codes, SW Exchange, SW Images, SW Transactions, SW Users. The steps for setting a client computers ODBC mapping's port number are:

Open Control Panel
Open your ODBC Data Source Administrator; in Windows 98 its under Control panel, in 2000 and XP its under Administrative tools in Control panel.
Select the tab of System DNS
Click one of the SW mappings, Click the Configure button, Click the next button
Click the "Client Configuration..." button
If "Dynamically determine port" is checked, uncheck it
In the Port number text field, type in the port number that your SQL or MSDE server is using.
Click OK
Click Back
Click Finish
Click OK
Repeat steps 4 - 11 for each SW mapping
Click Cancel to close the ODBC Data Source Administrator
Try to run SOAPware

MORE INFORMATION

SOAPware uses 6 ODBC mappings, SW Charts, SW Codes, SW Exchange, SW Images, SW Transactions, SW Users. The mappings correspond to the same named SQL or MSDE databases. Each database, if using SQL authentication, should have the user/role of soapuser with the permissions of public, db_ddladmin, db_datareader and db_datawriter. The SQL or MSDE server must be running TCP/IP protocols on the network. Each client must be using TCP/IP protocols. The soapuser's password is soapware.

Close This Window

The information provided in the SOAPware, Inc. Knowledge Base is provided "as is" without warranty of any kind. SOAPware, Inc. disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall SOAPware, Inc. or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if SOAPware, Inc. or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.

Published	03/24/2004
Last Modified	05/09/2007 12:50:45
Keywords	Trouble shooting guide, Networks and trouble shooting, CTS, login, log in

The information provided in the SOAPware, Inc. Knowledge Base is provided "as is" without warranty of any kind. SOAPware, Inc.. disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall SOAPware, Inc. or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if SOAPware, Inc. or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.

Pinging: A protocol that sends a message to another computer and waits for acknowledgment, often used to check if another computer on a network is reachable.

To find a computer's IP address in Windows XP, 2000 and 2003:

Click Start, Run, type the letters "CMD" in the Open field, then click the OK button.

At the Command Prompt type "ipconfig" this will return the IP Address of the computer you are on.
To find the IP of another computer such as your server, type in Ping and then the other computer's name (for example c>:ping servernamehere).

To find a computer's IP address in windows 98:

Click Start, Run, type the letters "command" in the Open field, click the OK button.

At the Command Prompt type Ping and the computers name.
To find the IP of another computer such as your server, type in Ping and then the other computer's name (for example c>:ping servernamehere)

Pinging With A Load: Pinging with a load can be done with the following command at the command prompt: ping servernamehere -t -l 5000 (Example C:>ping officeserver -t -l 5000) or ping IPofTheServer -t -l 5000 (Example C:>ping 192.168.1.100 -t -l 5000). The word "ping" invokes the program, the "-t" stands for continues, the "-l" stands for load, and "5000" is how many bytes your pinging with. The common response to this ping should be Reply from IPofTheServer: bytes=5000 time=2ms TTL=128

Command Prompt: The "command prompt" is a program which allows your to easily issue commands via a line command. To invoke the command prompt click Start, Run, type the word "Command" in the Open field if using Windows 98 or CMD if using Windows 2000 or newer, then click the OK button.

Microsoft SQL: SQL (pronounced "ess-que-el or S. Q. L. ") stands for Structured Query Language. Microsoft's SQL Server is relational database management systems used to supply data to clients using the SQL language. http://www.microsoft.com/sql/

Microsoft MSDE: MSDE (pronounced "M.S.D.E.") stands for Microsoft SQL Server Desktop Engine. The Microsoft SQL Server Desktop Engine is a free relational database management systems designed for workgroup size environments (less than 50 clients) to supply data to clients using the SQL language. http://www.microsoft.com/sql/msde/

IP Address: Each computer that is directly connected to a TCP/IP network is uniquely identified by a 32-bit binary number called its IP address. This address is usually seen as a four-part decimal number, each part in the decimal range or 0-255. For example, 192.168.1.100.

To find a computer's IP address in Windows XP, 2000 and 2003:

Click Start, Run, type the letters "CMD" in the Open field, then click the OK button.

At the Command Prompt type "ipconfig" this will return the IP Address of the computer you are on.
To find the IP of another computer such as your server, type in Ping and then the other computer's name (for example c>:ping servernamehere).

To find a computer's IP address in windows 98:

Click Start, Run, type the letters "command" in the Open field, click the OK button.

At the Command Prompt type Ping and the computers name.
To find the IP of another computer such as your server, type in Ping and then the other computer's name (for example c>:ping servernamehere)

Discover what port SQL is using: To discover what port your MSDE or SQL server is running on, on the server, click on Start, Run, in the "Open:" field type svrnetcn.exe and click OK. In the Enabled Protocols you should see "TCP/IP" choose it and then click the "Properties..." button. Here you can find what port SQL is using to supply the data. Most SQL servers use TCP port 1433 for data transfer and UDP port for server acknowledgment, but this is not always the case for the TCP port. Many times the TCP port will be assigned to another number.

Scan Port Now Results: The "Scan Port Now Results" can be very helpful in determining what might be wrong with your connection. It is very important that you know you are scanning the IP of the server and the correct port of the SQL/MSDE server.

Result:
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
The possibilities are;
The server is not reachable on the network,
or the computer is not running,
or the SQL/MSDE server not running,
or the port is being blocked.
To verify the computer is on and running and the SQL or MSDE server is there, choose the UDP port of 1434 and choose scan port. If it reports "Connection OK" you can narrow your possibilities to the port is being blocked.

Result:
No connection could be made because the target machine actively refused it
The possibilities are;
The computer is reachable and SQL/MSDE is not running,
or the port is being blocked.
To verify the SQL or MSDE server is running, choose the UDP port of 1434 and choose scan port. If it reports "Connection OK" you can narrow your possibilities to the port is being blocked.

Result:
Connection OK.
The server is there, SQL or MSDE is on and the connection has been made.