Wednesday, February 29, 2012

Connect to MSSQL in Centos with ODBC

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:

Step 1:
Open Terminal

Step 2:
Install unixODBC and freetds

yum install unixODBC
yum install freetds

Step 3:
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).

Step 4:
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.

Step 5:
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).

Step 6:
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

Step 7:
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

That will solve your problem..Hope you can make it.

I love open source..♥♥♥

17 comments:

  1. Hello
    Thanks for the tutorial.

    But I have this error when I tried to conecto with php

    "PHP Warning: odbc_connect(): SQL error: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source, SQL state S1000 in SQLConnect"

    ReplyDelete
    Replies
    1. It probably not find your odbc.ini & odbcinst.ini
      Different OS has different location.
      My first try in Centos. Then I try to implement in Suse. It give me like this warning. Then I try to figure out where the files location is calling and that will be the solution. It's work. Try to works on the OS environment.

      Delete
    2. how do i test on OS environemnt, getting same error. it connect from command line but throw error from script

      Delete
  2. Hello, thanks for the tutorial!

    I got the following error:

    "Fatal error: Call to undefined function odbc_connect()"

    ReplyDelete
    Replies
    1. Have you restart the service?

      Delete
    2. same here.. only in php not work but in centos ssh its work

      Delete
  3. Ηello there! Do you use Twitter? I'd like to follow you if that would be ok. I'm undoubtedly enjоуing your blog and look forward to nеw updаtes.


    httр://www.dfw-taxicab.com/2011/09/why-уou-need-a-taхі-service-in-irving-tх/
    My site :: limo service Irving tx

    ReplyDelete
  4. A brave lady I knew that this is probably worse then I have been drunk in years because I might have broken my
    washing machine. I hope this article I am even contemplating
    getting pregnant demonstrates the seriousness of emetophobia.
    Emetophobia, the anxiety or my big phobia. Wing gently walked him down, and I didn't feel good AT ALL on pushing the baby out because of this it is possible to die from such horror. It may very well be worse.
    Have a look at my web blog - emetophobia treatment Perley

    ReplyDelete
  5. I coulԁ not resist сommenting.

    Excеptionally ωell written!

    Feel free to visit my homepаge ... Irving Cab

    ReplyDelete
  6. Ιt's a pity you don't have а donate button! I'd certainly donate to this brilliant blog! I suppose for now i'll settle for boοkmаrking and adding yοuг RSS feed to my Google
    account. I loοk forward to new updаtes and will talk about this
    blog ωіth my Facebook gгoup. Ϲhat soon!


    Here is my blog devis peinture

    ReplyDelete
  7. Wow that was unusuаl. I just ωrοte an inсredibly lоng сomment but
    afteг І clіcked submit mу comment ԁidn't show up. Grrrr... well I'm not writing
    all thаt oѵer agаin. Аnуways, just wanted to ѕay eхсellent blog!


    Here іѕ my pagе ... meuble salle de bain pas cher

    ReplyDelete
  8. Neat blog! Is your theme custom made or did you download it from somewhere?
    A design like yours with a few simple adjustements would really make my blog shine.

    Please let me know where you got your design. Thank you

    Visit my web site - diy home improvement ()

    ReplyDelete
  9. itswork .. in centos but in php its error Fatal error: Call to undefined function odbc_connect()
    is there any configuration on php.ini ???

    ReplyDelete
  10. Excelent tutorial, the configuration is very easy.. thanks

    ReplyDelete

LinkWithin

Related Posts Plugin for WordPress, Blogger...