Recently I have to connect to MSSQL to communicate between two server. One in Linux and the other one is Window Server. But this isn’t easy as work on localhost. As I know the xampp in window doesn’t have odbc driver b’coz it already there. Then after a while I managed to find the solution.

Here what i found:

Open Terminal

Install unixODBC and freetds

yum install unixODBC
yum install freetds

open the odbc.ini in etc folder and paste this code

[MSSQL]
Driver=FreeTDS
Description=MS SQL Test
Trace=No
Server= 121.0.0.1
Database= myDB
Port=1433
TDS_Version = 8.0

Note : Change the the server into your server (window).

open the odbcinst.ini in etc folder and paste this code

[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

[ODBC]
DEBUG=1
TraceFile=/root/sqltrace.log
Trace=Yes

Note : Please check the exact location for Driver/Setup in your Linux Server.

open the freetds.conf in etc folder and paste this code

[MSSQL]
host = 127.0.0.1
port = 1433
tds version = 8.0

Note : Change the the server into your server (window).

then you may test the connection with this code

isql -v MSSQL sa password

you will get this

[root@localhost]# isql -v MSSQL sa password
+—————————————+
| Connected!                                          |
|                                                             |
| sql-statement                                        |
| help [tablename]                                   |
| quit                                                       |
|                                                             |
+—————————————+

sa – refer to the username of the SQL authentication

password – refer to the password  of the SQL authentication

Create test page in php

<?php

// connect to DSN MSSQL with a user and password
$connect = odbc_connect(“MSSQL”, “sa”, “password” or die
(“couldn’t connect”;

$result = odbc_exec($connect, “SELECT Identity_No, Name FROM dbo.user”;
while(odbc_fetch_row($result)){
print(odbc_result($result, “Identity_No” .
‘ ‘ . odbc_result($result, “Name” . “<br>\n”;
}
odbc_free_result($result);
odbc_close($connect);
?>

If you get the result, that mean you has successfully connected to MSSQL. But if you don’t, please try to recheck each of the file (freetds.conf, odbc.ini, odbcinst.ini) in their exact location. This state is the harder one, no one ever told me about the location. I got stuck in there also. But it did figure it out soon. Try to locate the odbc file with this

odbcinst -j

you will get this information about the ODBC driver that you install earlier.

unixODBC 2.2.11
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini

sumber : http://shasscript.blogspot.com/2012/02/connect-to-mssql-in-centos-with-odbc.html