[solved] freeTDS and unixODBC connecting to MS-SQL

[ponytaildude]

I recently added freeTDS and unixODBC to my gentoo installation to enable access to a Micro$oft SQL Server 2000 instance. I added freetds and odbc to my USE flags, then emerged freetds and unixODBC. I then configured and tested the connection to the Windows MSSQL database.

The configuration of freetds was simple. Create a system name, host, port and tds version in /etc/freetds.conf:
[theSQLserver]
host = 192.168.1.240
port = 1433
tds version = 8.0

The configuration of unixODBC was also simple. The 'secret' to a working configuration is 'no leading spaces' in the configuration lines. Two config files need to be created, /etc/unixODBC/odbc.ini and /etc/unixODBC/odbcinst.ini:

/etc/unixODBC/odbc.ini
[theSQLserver]
Driver=FreeTDS
Description=Microsoft SQL Server 2000
Trace=No
Server=192.168.1.240
Database=someDatabaseName

/etc/unixODBC/odbcinst.ini
[FreeTDS]
Description = TDS driver MSSQL
Driver = /usr/lib/libtdsodbc.so
FileUsage = 1

I was then able to successfully test the connection using:
tsql -S theSQLserver -U username -P password

I was also able to successfully test the connection using:
isql theSqlserver username password

If the ODBC config file lines contain leading spaces, the following messages appear when isql command is issued:
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

After restarting apache I was able to test the connection using PHP code:

<?php
$connect = odbc_connect("theSQLserver", "username", "password");
$query = "SOME SQL STATEMENT HERE LIKE SELECT";
$result = odbc_exec($connect, $query);
while(odbc_fetch_row($result)) {
$field1 = odbc_result($result, 1);
$field2 = odbc_result($result, 2);
print("$field1 $field2\n");
}
odbc_close($connect);
?>

I hope this saves some time for anyone attempting to connect to MS-SQL using freetds and unixODBC on gentoo.
-ptd

[jbaum81]

Did you ever figure out why tsql wasnt working for you? I have a simular problem with a mdb dsn but no love from php either. same missing dsn error you had.