7

I am trying to connect to a SQL server database that is running on a windows server. I am running this PHP code on a linux server (centos 7).

I am using the following pdo connection string to connect to the database.

$db = new PDO ("dblib:192.168.2.1;dbname=TestDB","username",'pass');

When i run the code i get the following exception. 'PDOException' with message 'SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or does not exist (severity 9)'

I have tried to test the connection using tsql and i am able to connect to the database without any error. The following code gave me a list of all the tables in TestDB. It wouldnt work if i didng type use TestDB first. 

tsql -S 192.168.2.1 -U username -P 'pass' -L TestDB

use TestDB 
GO 
select * FROM sys.Tables 
GO

My freetds.conf file contains the following 

[Server1]
    host = 192.168.2.1
    port = 1433
    tds version = 8.0

I cannot figure out how i am able to connect using tsql, but cannot do the same when connecting with php.

The dblib driver is definitely installed.

print_r(PDO::getAvailableDrivers()); 

Array ( [0] => dblib [1] => mysql [2] => sqlite )

Answer

Found the cause of the problem. Turned out to be SELinux. The following commands fixed the issue.

setsebool -P httpd_can_network_connect 1

setsebool -P httpd_can_network_connect_db 1
Dan Hastings
  • 3,241
  • 7
  • 34
  • 71
  • Does your port match? Is your SQL Server really running under 1433? You can check this in your SQL Server Configuration Manager to be sure. – Ionic Jul 01 '15 at 13:24
  • The way you instantiate your PDO object is afaik wrong. See [the docs here](http://php.net/manual/en/pdo.construct.php). What dblib? Whats the host? ... Edit: Okay, dblib links to MSServer, but then the "host=" prefix before the IP address is missing. – vitus37 Jul 01 '15 at 13:24
  • What dblib is? The connector for SQL Server. :-D See PHP docs. http://php.net/manual/de/ref.pdo-dblib.php – Ionic Jul 01 '15 at 13:28
  • yes the port is correct. I can connect to the DB using tsql so i think everything is ok there. It looks like it may be php specific. dblib is the driver used. mysql doesnt work with sql server. – Dan Hastings Jul 01 '15 at 13:28
  • It seems this question has been asked before. See if any of these answers help: http://stackoverflow.com/questions/8511369/adaptive-server-is-unavailable-or-does-not-exist-error-connecting-to-sql-serve – Just Lucky Really Jul 01 '15 at 13:42

4 Answers4

3

You have the datasource name, you should make use of it:

$db = new PDO ("dblib:host=Server1;dbname=TestDB","username",'pass');

You are running linux right? I recommend giving odbc a shot.

meda
  • 45,103
  • 14
  • 92
  • 122
  • I tried this and it did change the output, still an error though. 'SQLSTATE[HY000] (null) (severity 9)' . Sometimes the error is 'SQLSTATE[] (null) (severity 0)' – Dan Hastings Jul 01 '15 at 13:45
  • @DanHastings are able to run `tsql -S Server1 -U username` – meda Jul 01 '15 at 13:52
  • Tried it there and it says "There was a problem connecting to the server". So something is up with the info in the freetds.conf file im guessing? Do i have to define this anywhere else? – Dan Hastings Jul 01 '15 at 13:54
  • run `tsql -C` and paste the output – meda Jul 01 '15 at 14:02
  • I found a typo. Rather than "port" i had "post". I have changed this and restarted apache. This let me connect using server1. Still hasnt fixed it with php though – Dan Hastings Jul 01 '15 at 14:03
  • found the cause of the issue to be SELinux. Thanks for all your help though – Dan Hastings Jul 01 '15 at 14:21
  • great what did you use for the connection string – meda Jul 01 '15 at 14:23
1

Three things to check for you.

First try your connection using your defined port.

Instead of:

$db = new PDO ("dblib:192.168.2.1;dbname=TestDB","username",'pass');

try using this:

$db = new PDO("dblib:host=192.168.2.1:1433;dbname=TestDB","username",'pass');

Second, you should be sure if your SQL Server is configured to hear on port 1433. You can check this using the SQL Server Configuration Manager.

The third (if you run it on windows) thing you can check is one thing I find in the PHP docs. Inside a comment, another one mentioned the same error. Here is the answer which seems to work:

For PDO MSSQL connection issues, ensure that you have the updated version of ntwdblib.dll (currently 8.00.194 as of this post). Overwrite the existing (old) file or place it in the Windows system32 folder. The version that ships with PHP 5.2.X does not work. This is a well known issue apparently, but I had a really hard time finding information on this issue until I was able to lock in a file name. I hope that this helps someone.

Another possible issue could be SELinux if it's enabled. I've gotten some errors which are something familiar with this on my Ruby on Rails installation. You can give it a try by disabling SELinux and try it again.

Ionic
  • 3,884
  • 1
  • 12
  • 33
0

When connecting using PDO, if you leave out the host= it uses the Unix domain sockets which could be causing your problem. Putting in the host= will connect to the database via TCP/IP.

So try change your line of code to:

$db = new PDO ("dblib:host=192.168.2.1;dbname=TestDB","username",'pass');

Or with the port aswell:

$db = new PDO ("dblib:host=192.168.2.1:1433;dbname=TestDB","username",'pass');
Just Lucky Really
  • 1,341
  • 1
  • 15
  • 38
0

For me, the problem was using the IP address. I changed it to "MyServerName/SQL2017DEV" and it connected

Shaakir
  • 464
  • 5
  • 13