"Adaptive Server is unavailable or does not exist" error connecting to SQL Server from PHP

Question

I'm attempting to connect to a SQL Server 2005 DB from my Mac using unixODBC and FreeTDS as I have outlined here. However, when I try to connect in to a different DB using the same setup, I get:

Connection Failed:[FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist.

Here is my freetds.conf setup:

[my_db]
host = 12.34.56.789
port = 1433
tds version = 8.0

And here is my odbc.ini:

[my_dsn]
Driver = /opt/local/lib/libtdsodbc.so
Description = My Database
Trace = no
Servername = my_db
Database = MyDB

[ODBC Data Sources]
my_dsn = FreeTDS

I am still able to connect to the other DB I set up on this computer (described in my blog post linked above), so I'm pretty sure that the error isn't on the Mac end. I've verified on the server that I'm using the correct IP address and port. Any thoughts on what could be the problem, and if it's on the server end?

Chiffchaff

i found the following ini files. which one would i use? ./root/php/freetds-0.91/samples/odbc.ini ./usr/local/unixODBC/etc/odbc.ini ./usr/local/etc/odbc.ini ./usr/share/doc/freetds-devel-0.64/samples/odbc.ini ./etc/php.d/odbc.ini ./etc/odbc.ini ./var/tmp/php-5.2.6-2-root-root/etc/php.d/odbc.ini – Wahlstrom

I cannot find libtdsodbc.so anywhere usinf find command. I am using centos 5 – Wahlstrom

Answer 1

1. See information about the SQL server

tsql -LH SERVER_IP_ADDRESS

locale is "C"
locale charset is "646"
ServerName TITAN
InstanceName MSSQLSERVER
IsClustered No
Version 8.00.194
tcp 1433
np \\TITAN\pipe\sql\query

2. Set your freetds.conf

tsql -C    
freetds.conf directory: /usr/local/etc

[TITAN]
host = SERVER_IP_ADDRESS
port = 1433
tds version = 7.2

3 Try

tsql -S TITAN -U user -P password

OR

 'dsn' => 'dblib:host=TITAN:1433;dbname=YOURDBNAME',

See also http://www.freetds.org/userguide/confirminstall.htm (Example 3-5.)

If you get message 20009, remember you haven't connected to the machine. It's a configuration or network issue, not a protocol failure. Verify the server is up, has the name and IP address FreeTDS is using, and is listening to the configured port.

Psychedelic

Thanks for this, I needed to specify the port (ip:port for pymssql) and the LH command allowed me to easily find it. – Durable

If tsql -LH gives no output, what is the next step that should be taken? – Inhalator

Awesome. Saved my day. – Tumulus

@EvgeniyTkachenko: Sometimes the LH command does not return anything, even though the address is correct. Is LH deprecated? – Insanitary

@Adrian Keister I use FreeTDS(May 14, 2011) (man tsql) .I didn’t get your problem. – Psychedelic

Answer 2

After countless hours of frustration I managed to get all working:

odbcinst.ini:

[FreeTDS]
Description = FreeTDS Driver v0.91
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
fileusage=1
dontdlclose=1
UsageCount=1

odbc.ini:

[test]
Driver = FreeTDS
Description = My Test Server
Trace = No
#TraceFile = /tmp/sql.log
ServerName = mssql
#Port = 1433
instance = SQLEXPRESS
Database = usedbname
TDS_Version = 4.2

FreeTDS.conf:

[mssql]
host = hostnameOrIP
instance = SQLEXPRESS
#Port = 1433
tds version = 4.2

First test connection (mssql is a section name from freetds.conf):

tsql -S mssql -U username -P password

You must see some settings but no errors and only a 1> prompt. Use quit to exit.

Then let's test DSN/FreeTDS (test is a section name from odbc.ini; -v means verbose):

isql -v test username password -v

You must see message Connected!

Cullin

Answer 3

It sounds like you have a problem with your dsn or odbc data source.

Try bypassing the dsn first and connect using:

TDSVER=8.0 tsql -S *serverIPAddress* -U *username* -P *password*

If that works, you know its an issue with your dsn or with freetds using your dsn. Also, it is possible that your tds version is not compatible with your server. You might want to try other TDSVER settings (5.0, 7.0, 7.1).

Electrify

TDSVER=8.0 tsql -S *serverIPAddress* -U *username* -P *password* this works for me but in freetds makes no difference – Wahlstrom

Is it also possible that odbc isn't installed correctly? What are the correct steps on, say, Ubuntu? – Insanitary

Answer 4

I had the same issue, my problem was that the firewall on the server wasn't open from the current ip address.

Caprice

Answer 5

Responding because this answer came up first for search when I was having the same issue:

[08S01][unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist

MSSQL named instances have to be configured properly without setting the port. (documentation on the freetds config says set instance or port NOT BOTH)

freetds.conf

[Name]
host = Server.com
instance = instance_name
#port = port is found automatically, don't define explicitly
tds version = 8.0
client charset = UTF-8

And in odbc.ini just because you can set Port, DON'T when you are using a named instance.

Mairemaise

@Jan: That web site is no longer available. – Insanitary

@Naidim: What are the possible values for instance? That doesn't seem to be in the documentation, and I have a funny feeling mine isn't SQL Express. – Insanitary

@AdrianKeister check the name of your SQL Server instance see these answers #16088651 – Cullin

Answer 6

I've found an issue happen to be with the Firewall. So, make sure your IP is whitelisted and the firewall does not block your connection. You can check connectivity with:

tsql -H somehost.com -p 1433

In my case, the output was:

Error 20009 (severity 9):
  Unable to connect: Adaptive Server is unavailable or does not exist
  OS error 111, "Connection refused"
There was a problem connecting to the server

Natterjack

Answer 7

I was able to solve the "adaptive server" error by adding the hostname and its IP to a new line in the file /etc/hosts on the client (on linux/unix machines this is the file location, for windows machines search for an etc folder with a hosts file somewhere below c:\windows\):

...
192.168.1.10    sqlserver10
...

So in this case the name resolution was missing.

I know there can be a lot of different reasons that lead to this error message. Maybe this helps someone.

Aerostation

Answer 8

Found another issue that can cause this. FreeTDS v1.3 and higher doesn't support protocol version 8.0 as it was reportedly renamed to 7.1:

Regarding obsolete versions In the earlier days of FreeTDS, Microsoft did not release official specs for the TDS protocol. When MSSQL 2000 (product 8.0) was released, there was semi-official indications from the Microsoft community that the TDS protocol would be version 8.0. So the FreeTDS developers adopted that version for FreeTDS. Years later, when Microsoft started releasing official specs of the protocol, it became obvious that the TDS versions that FreeTDS had labeled 8.0 and 9.0 were actually versions 7.1 and 7.2 respectively.

Version 8.0 cannot be used from FreeTDS version 1.3. https://www.freetds.org/userguide/ChoosingTdsProtocol.html

It seems though the TDS versions changed even before FreeTDS 1.3 (in our case v1.2.21). Plus the documentation seems to have version pairing mixed up. It took many sleepless nights, but eventually in ~/.freetds.conf we had to change:

   tds version = 8.0

to:

   tds version = 7.0

to get it working. Also I'd recommend setting up the dump file in freetds.conf (or ~/.freetds.conf), for example:

   dump file = /tmp/freetds.log
   debug flags = 0xffff

and checking this file for more info.

Egide

Answer 9

When I got this error, I was working from home and the problem was just that my VPN had died.

The database I was trying to access isn't available from outside the firewall without a VPN.

Once I realised the VPN wasn't working, I just switched it back on and the problem vanished.

Concentration

Answer 10

Bud, disable selinux or add the following to your RedHat/CentOS Server:

setsebool -P httpd_can_network_connect_db 1
setsebool -P httpd_can_network_connect 1

Best always!

Gauleiter

Answer 11

Try changing server name to "localhost"

pymssql.connect(server="localhost", user="myusername", password="mypwd", database="temp",port="1433")

Pattison

If you're trying to connect to a database on a different machine, this answer will not help at all. – Insanitary

i think this answers not relevan – Pierides