Martin

freetds odbc Datas [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

While we tried to set up odbc with freetds in order to connect to a mssql server we ran into several issues.

Before trying to connect with freetds to the mssql server, make sure, that your mssql server is accessable from "outside" (Your seetings should look like that (note this articel is written in german)).

With the following configuration we could connect to the mssql server (Make sure you installed all required packages unixodbc libmyodbc freetds...)

nano /etc/freetds.conf

# A typical Microsoft server
[sqlexpress]
host = 192.168.1.3
port = 1433
tds version = 8.0

nano /etc/odbc.ini

[sqlexpress]
Servername = sqlexpress
Driver = FreeTDS

nano /etc/odbcinst.ini

[FreeTDS]
Description = FreeTDS driver
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so.2
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
UsageCount =1

You have to check where your Driver/ Setup libtds/S is located. You can check this with locate libtds.

First check if freetds is able to connect to the mssql server with

/usr/bin/tsql -S 192.168.122.157 -U martin
locale is "en_US.utf8"
locale charset is "UTF-8"
Password:
1>

The biggest problem was the next testing command isql. With

[root@FEC-A120D19MA martin]# isql -v sqlexpress martin
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[37000][unixODBC][FreeTDS][SQL Server]Fehler bei der Anmeldung fuer den Benutzer 'martin'.
[ISQL]ERROR: Could not SQLConnect

we got this error. I was searching for hours to figure out why the connection could not be ethablised. After a while I added a password parameter to the isql command. And..... it worked! So the whole time my configuration was correct. The error message "Unable to connect to data source.." wasn't related to the actual problem. This was really retarded :D So with

isql -v SQLEXPRESS martin <passwort>

it should work.

When connecting with pdo_odbc to the sqlexpress Datasource, we got the PHP Error:

- [unixODBC][FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

The solution of this problem is to add "TDS_Version    = 8.0" in the /etc/odbc.ini.
  

Some good links related to this topic:

http://permalink.gmane.org/gmane.comp.db.tds.freetds/13133

http://www.debian-administration.org/articles/496

(c) 2011 | Impressum |