Scenario: I wanted to connect to an SQL server 2008 from a FreeBSD 8.2 machine using PHP5.3 and FreeTDS. We have a similar set up on another server running FreeBSD 7.2 with PHP 5.2 . This is still running in production and connecting to the same DB server successfully. The logical thing to do was to just copy over the freetds configuration and db connection code and away we go. Unfortunately not.
The config files:
freetds.conf
# SQL 2008
[MyServer2008]
host = 123.123.123.123
port = 1433
tds version = 8.0
client charset = UTF-8
dataDatabaseClass.php
$mssqlhostname = "MyServer2008";
$mssqldbname = "MyDB";
$mssqlusername = "username";
$mssqlpw = "password";if(!$this->mssqldbh){
$this->mssqldbh = new PDO("dblib:host=$mssqlhostname;db=$mssqldbname","$mssqlusername","$mssqlpw");
}
The webpage showed the following error:
[Wed Feb 06 16:17:50 2013] [error] [client123.123.123.123] PHP Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[28000] Login incorrect. (severity 9)' in /usr/local/www/apache22/data/BT/lib/DatabaseClass.php:19\nStack trace:\n#0 /usr/local/www/apache22/dataDatabaseClass.php(19): PDO->__construct('dblib:host=MySe…', 'username', 'password')\n#1 /usr/local/www/apache22/bt-controller.php(32): DatabaseClass->__construct()\n#2 /usr/local/www/apache22/controller.php(157): postRequest(NULL, NULL, NULL)\n#3 {main}\n thrown in /usr/local/www/apache22/data/BT/lib/DatabaseClass.php on line 19
As I had copied over all the configuration files required, a "login incorrect" error message was a bit odd. Nevertheless I checked the SQL 2008 SQL logs. No sign of any attempted login.
The next thing to check was the that we had pdo_dblib installed:
pkg_info | grep pdo
php53-pdo-5.3.15 The pdo shared extension for php
php53-pdo_dblib-5.3.15 The pdo_dblib shared extension for php
php53-pdo_mysql-5.3.15 The pdo_mysql shared extension for phpAll looked good.,
Next I wanted to look at freetds config. There is an option to write a dump file for diagnostic purposes. I enabled this with the following line freetds.conf
dump file = /tmp/freetds.log
The dump file showed the following:
locale is "C"
locale charset is "US-ASCII"
Password:
Msg 20004, Level 9, State 0, Server OpenClient, Line 0
Read from SQL server failed.
Msg 20014, Level 9, State 0, Server OpenClient, Line 0
Login incorrect.
There was a problem connecting to the server
Looking again at the Server 2008 I saw these "errors" appearing in the event viewer (Windows Logs -> System) whenever we tried to attempt to connect
An TLS 1.0 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
RESOLUTION:
After much troubleshooting the resolution was to actually change the freetds settings:
Edit the freetds.conf file and change the
tds version = 8.0
TO
tds version = 7.0
and remove the following line:
client charset = UTF-8
This was strange as I had the original config working on another similar server but offered much relief in the end as we spending about 3 hours working it out!