I'm having trouble connecting to a SQL Anywhere server. How can I fix this?


Note: This is the first in what I hope to be a series of articles covering frequently asked questions on this forum. The idea is to give generic and comprehensive answers to these questions so they can be referenced in more detailed answers (for example. "Here's how to solve your particular connection problem, and here is some generic background information about connecting") and will also show up in internet searches.

The "faq" tag will be used for these questions, and existing questions using that tag will be edited to remove it. Most of them are or were frequently asked questions about the forum itself, so they will be given the "meta-sqla" tag.

asked 10 Jan '14, 12:41

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109
accept rate: 52%

edited 11 Jan '14, 08:00

That's a great idea!

(11 Jan '14, 06:23) Volker Barth
Replies hidden
1

And thanks for making sure that the "real" questions are back on the "active question list" after your massive re-tag - that's another clue for the "Watcom does the things the way they should be done" rule:)

(11 Jan '14, 08:45) Volker Barth

"Watcom"? You're showing your age, Volker! I've been at this job for sixteen years and worked for at least three different companies (Sybase, iAnywhere Solutions, and SAP) but I never worked for Watcom.

(11 Jan '14, 10:26) Graeme Perrow

Graeme, you post was excellent and should help the vast majority of new users that are having a problem connecting to an ASA database. I would like to suggest that you clarify the word server in the documentation. From reading the documentation, a new programmer might not know the difference a physical server (or PC) and the logical instance of an ASA engine (server). The documentation might be clearer if it were stated something like... ASA server FRODO (engine) running on Windows or Unix server named HOBBIT with an ip address of 192.168.1.54...

(11 Jan '14, 14:40) Tom Mangano

I don't know about our documentation conventions, but I tend to use the term "server" as a piece of software (i.e. DB server, HTTP server, etc.) and use "machine" or "computer" as a piece of hardware.

(11 Jan '14, 15:21) Graeme Perrow

After reading more CAREFULLY, I see the conventions you use in your explanation. Maybe a capitalized Machine or Computer will help those (ME) pay attention to your convention. Thanks again.

(11 Jan '14, 16:24) Tom Mangano
Comment Text Removed
Comment Text Removed
Comment Text Removed
Comment Text Removed
Comment Text Removed
Comment Text Removed
showing 1 of 6 show all flat view

Note: This answer gives generic information on the most common problems when connecting to a SQL Anywhere server. It does not describe every connection parameter, every TCP/IP option, or every problem you may run into. For more details, look at the documentation for connection parameters or Troubleshooting connections. If you are still unable to connect, ask a new question with your specific connection string and server details.

1 Connection Strings

When connecting to a SQL Anywhere server, you need to specify a connection string containing a number of connection parameters. Each connection parameter is a "key=value" pair, and parameters are separated by semicolons. Some parameter values have options that can be specified in parens ( ) or braces { }.

There are well over 30 different connection parameters, but here are the ones used most often (along with documentation links):

ParameterShort formDescription
UserIDUIDUser ID
PasswordPWDPassword
ServerNameServer or ENGServer name
DatabaseNameDBNDatabase name
DatabaseFileDBFDatabase file
DataSourceNameDSNData source name
Host Host name and port (v12+)
CommLinksLINKSNetwork protocol and options

The only required connection parameters are UID and PWD, since you need to tell the database server who you are (and if you're using integrated login you don't even need those, but that's beyond the scope of this question). However, it is generally good practice to specify the Server parameter as well, in order to ensure that you connect to the correct server. If the server is hosting multiple databases, the DBN parameter is also recommended.

2 Finding the server

2.1 Same machine

If you are connecting to a server running on the same machine then you can likely use shared memory to connect. However there are situations when shared memory cannot be used: If using Windows, see Session and terminal considerations; On Unix both client and server processes must be using the same temporary directory.

To use shared memory, you can either use "LINKS=shmem" in your connection string or leave out the LINKS and HOST parameters entirely.

2.2 Using TCP/IP

If you cannot use shared memory, you must use TCP/IP. If your client is using SQL Anywhere version 12.0.0 or later, you can use either the HOST parameter or the LINKS parameter (but not both). For earlier clients, you must use the LINKS parameter.

In most cases, the HOST parameter is recommended over LINKS since it's much simpler.

2.2.1 HOST

The HOST parameter is the easiest way to tell the client library how to find the server. The value of the HOST parameter is the IP address and, optionally, the port on which the server is listening. You can list multiple addresses, separating them with commas. The addresses are tried in the order listed.

The HOST parameter is not the same as the Host TCP/IP option.

Notes:

  • If the port is not specified, it defaults to 2638.
  • The IP address and port are separated with a colon (:). If the IP address is an IPv6 address and a port is specified, the address must be in square brackets, eg. HOST=[1:2:3::7:8]:1234.
  • If the HOST parameter is specified, the Server parameter is not required but is still recommended.
  • The HOST parameter does not support options like LINKS does. If you need to specify options, you must use LINKS.

2.2.2 LINKS

The LINKS parameter tells the client library which protocol to use to find the server. The only values for this parameter are "shmem" or "tcpip". For TCP/IP, you can also use a number of options to help find the server. Options are appended to the value in parens. Options are also "key=value" pairs, separated by semicolons (;). These options are documented here (in the TCP/IP column), but the most often used ones are:

ParameterShort formDescription
HostIPHost name or IP address of server
ServerPortPortPort number of server
DoBroadcastDoBroadHow to use UDP broadcasts to find the server

The DoBroadcast option takes the following values:

  • all - The client sends a UDP broadcast to the local subnet looking for the server. The server then responds with its IP address and port number. This is the default if Host is not specified.
  • direct - The Host option is required. The client sends UDP packets to each of the hosts specified with the Host option. If the server is running on one of those hosts, it will respond with its IP address and port number.
  • none - The Host option is required. The client attempts to make TCP connections to each of the hosts specified with the Host option. This is the default if the Host option is specified.

3 Finding the database

When the server is started, the first database to be started on the server is called the "default" database. Any connection to that server that does not specify a database name or file is connected to the default database. If you know that the server you're connecting to is only running one database then this is fine, but if you are not sure, you may find yourself connecting to the wrong database. This could be simply annoying (your userid and password won't work) or it could be disastrous (your userid and password do work and you are accessing / updating / deleting the wrong data).

To indicate which database you want to connect to, use the DatabaseName (DBN) parameter with the name of the database you want to connect to. If the database is not running, it may be possible to autostart it by specifying the DatabaseFile (DBF) parameter, but that is beyond the scope of this question. A future FAQ question will deal with autostarting databases and servers.

4 Examples

All examples assume you are connecting to the user "fred" with the password "secret".

4.1 Connecting to a local server

You want to connect to a local server (i.e. running on the same machine as your client) called Frodo using the default database, then you can use:

  • uid=fred;pwd=secret;Server=Frodo

4.2 Connecting to a remote server

You want to connect to a server called Frodo on the same subnet as your computer, but you don't know what machine it's running on, then you can use:

  • uid=fred;pwd=secret;Server=Frodo;links=tcpip

You want to connect to a server called Frodo on a host called Shire, listening on the default port. Any of the following connection strings will work:

  • uid=fred;pwd=secret;Server=MyServer;host=Shire
  • uid=fred;pwd=secret;Server=MyServer;links=tcpip(host=Shire)
  • uid=fred;pwd=secret;Server=MyServer;links=tcpip

Note: The last one will only work if the server machine is on the same subnet as the client.


You want to connect to a server called Frodo on a host called Shire, listening on port 50000. Any of the following will work:

  • uid=fred;pwd=secret;Server=MyServer;host=Shire:50000
  • uid=fred;pwd=secret;Server=MyServer;links=tcpip(host=Shire:50000)
  • uid=fred;pwd=secret;Server=MyServer;links=tcpip(host=Shire;port=50000)
  • uid=fred;pwd=secret;Server=MyServer;links=tcpip(port=50000)

Note: The last one will only work if the server machine is on the same subnet as the client.

5 Troubleshooting

Still having problems after you've read this answer? Here's how you can get some more information from both the server and the client library to figure out what's going wrong.

5.1 On the client

Add the LOG parameter to your connection string. This parameter takes a filename as a value, and then writes a bunch of connection diagnostic information to that file. After a failed connection, you can look at that file and see where the problem occurred.

For example, let's say you have "Server=Frodo;host=1.2.3.4" in your connection string and you see this in the log:

Attempting TCPIP connection (no sasrv.ini cached address)
Looking for server with name Frodo
Trying to find server at address 1.2.3.4:2638
TCP/IP link, function connect(), error code 10061

Error code 10061 (on Windows) is WSAECONNREFUSED (Windows error codes are listed here). On Unix, the equivalent code is ECONNREFUSED but the numeric value may differ on different flavours of Unix. This code tells you that there is no service listening on that port, i.e. the SQL Anywhere server is not running on that port on that machine. Perhaps you have the port number or IP address wrong, or perhaps the server is not actually running.

But let's say you see this instead:

Attempting TCPIP connection (no sasrv.ini cached address)
Looking for server with name Frodo
Trying to find server at address 1.2.3.4:2638
Found server, verifying server name
A HOST value was specified, skipping LDAP check
Sending broadcast to find server

This tells us that we did find a server but after attempting to verify the server name, the connection attempt continued, so obviously the verification failed. So there is a server running on that machine, but it has a different server name than the one we want to connect to.

5.2 On the server

To enable diagnostic information on the server, it needs to be restarted with some extra command line switches. Add the -z and -o <file> switches to the command line and then restart the server. This will include extra diagnostic information on the server console, and save the console output to a "console log" file called <file>. This file will tell you what IP addresses and ports the server will be listening on, and this may help diagnose connection problems.

You can also change these values on a running server without the need to stop and restart it using the sa_server_option system procedure using the 'DebuggingInformation' and 'ConsoleLogFile' option names. However, this doesn't help if you need the information from the server initialization. In that case, you will need to stop and restart using the command line switches.

For example, you may see this in the console log:

Trying to start TCPIP link ...
Could not bind to address (::):2638
Unable to start on default port; starting on port 49152 instead

This tells us that the server attempted to use port 2638 (the default port), but was unable to because something else was already using it - likely another SQL Anywhere server. If your connection string contains "HOST=Shire:2638", then the client will only try port 2638, but your server is listening on port 49152. Thus you will likely connect to the wrong server.

permanent link

answered 10 Jan '14, 12:41

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109
accept rate: 52%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×10
×7
×1

question asked: 10 Jan '14, 12:41

question was seen: 15,180 times

last updated: 13 Jan '14, 09:48