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

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?

This question is related to php sql-server sql-server-2005 freetds unixodbc

The answer is


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.


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!


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


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.


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

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).


Try changing server name to "localhost"

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


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!


Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to freetds

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

Examples related to unixodbc

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