Some days ago in my company someone asked me if we can access to our ERP software which is using Microsoft SQL Server. They want to get data to program which is writing by me using PHP and MySQL. I started to googling the net but I only found that Microsoft is providing drivers for ODBC connection. Those drivers can be found here.
Here is what i have done: (I assume that you have LAMP server setup on your CentOS 6.x)
|
1 |
# yum install php php-odbc wget gcc php-pear php-pecl-apc php-xml php-xmlrpc php-intl php-tidy php-imap php-pecl-memcache |
Next step was to download proper driver: For CentOS 5.x
|
1 |
# wget http://download.microsoft.com/download/6/A/B/6AB27E13-46AE-4CE9-AFFD-406367CADC1D/Linux5/sqlncli-11.0.1790.0.tar.gz |
For CentOS 6.x
|
1 |
# wget http://download.microsoft.com/download/6/A/B/6AB27E13-46AE-4CE9-AFFD-406367CADC1D/Linux6/sqlncli-11.0.1790.0.tar.gz |
Next I have to untar it
|
1 |
# tar xvf sqlncli-11.0.1790.0.tar.gz |
and then build it
|
1 2 |
# cd sqlncli-11.0.1790.0 # ./build_dm.sh |
After that:
|
1 2 3 |
# cd /tmp/unixODBC.5996.21582.3453/unixODBC-2.3.0 # make install # cd /path_to_sql_client_download/sqlncli-11.0.1790.0 |
And the last
|
1 |
# ./install.sh install --lib-dir=/usr/local/lib64 --accept-license |
Now I have to setup ODBC, so we have to edit /etc/odbc.ini file and setup proper DSNName values
|
1 2 3 4 5 6 7 |
[MyDSNName] Driver=SQL Server Native Client 11.0 Description=My Test ODBC Database Connection Trace=Yes Server=[My SQL Server IP address] Port=1433 Database=[my database name] |
To test it I have to use the isql command
|
1 |
# isql -v MyDSNName MSSqlUser MSSqlUserPassword |
I have seen something like this below
|
1 2 3 4 5 6 7 8 |
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ |
OK, now it’s time to test PHP connection – because this is what my cooworkers wanted. So let’s wrote some PHP code:
|
1 2 3 4 5 6 7 8 9 10 11 |
<?php $dbconn = new PDO("odbc:MyDSNName", "MSSQLUserName", "MSSQLUserPassword"); $sql = "SELECT * FROM MSSQL_DataBase;"; $stmt = $dbconn->prepare($sql); $result-> $stmt->execute(); while($row = $stmt->fetch()) { echo "$row[0] - $row[1]"; echo "<br>\n"; } ?> |
I save it under odbctest.php file. Now it’s time to test it in the linux terminal:
|
1 |
# php odbctest.php |
I have seen something like:
|
1 2 3 |
TA00003 - Name 1 TA00004 - Name 2 TA00045 - Name 45 |
Which was good. Now it’s time to test it under Apache, so a I copied this odbctest.php file under /var/www/html
|
1 |
# cp odbctest.php /var/www/html |
After that i run
|
1 |
# wget http://localhost/odbctest.php |
the download seems not working. The problem was in selinux permissions. If I do
|
1 |
# setenforce permissive |
then wget command will work and my webpage was accessible – until next reboot. Another way was to change the semanage context
|
1 |
# semanage permissive -a httpd_t |
Now after reboot it was working 🙂
Important information!
In CentOS 7 all steps will work until →# php odbctest.php. The problem is that Microsoft does’nt provide driver for RHEL/CentOS 7 branch. It only works with PHP up to 5.4 🙁
Thanks a lot. You really saved my day. I wish you best.
Actually, you can get this working in Centos 7. I just had to do it for a project at work. The only extra step is installing and configuring freeTDS. Otherwise, great information here. Saved me many hours! Thanks!
Thanks for the tip 🙂 I will try to do this and share info here. btw. nice www page you have 😉
Thanks! Make sure epel repository is set up for centos 7. Besides that, this blog has the rest of the steps (skipping the setting up for epel of course). http://www.stevepiercy.com/articles/how-to-install-and-configure-freetds-as-an-odbc-connector-to-microsoft-sql-server-on-centosrhel-for-lasso-9/
Thanks for sharing this! It would be very helpfull.
Pingback: Connecting to Microsoft SQL Server with PHP from Centos 6.x/7.x
Where did you download and untar the file? I’m new to linux, and I don’t know where I have to download and untar the file. Is there a specific directory you have to be in etc.. Also what is the very first yum command doing? (yum install php php-odbc….)
the first yum install is downloading php and some php extensions. If you’re running php you probably already have most or all of it. You download and untar it anywhere you want, tmp, home/youruser, /root does not really matter.
Pingback: unixODBC Driver for SQL Server on CentOS 6.x not working in PHP | shareitHQ
Got some problem trying to connect using isql -v DSNName UserName UserPass on Centos. It always give me error :
[S1T00][unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout expired
The problem caused by driver in /etc/odbcinst.ini :
[FreeTDS]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/usr/local/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=2
I change to :
[FreeTDS]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
;Driver=/usr/local/lib64/libsqlncli-11.0.so.1790.0 // bikin masalah timeout terus
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so.2
Threading=1
UsageCount=2
I’m guessing you were trying to connect to a server with a named instance, such as 192.1.3.5\dev, and got the login timeout error. You need to find out what port that instance is set to (default is 1433), and change odbc.ini so that Server=192.13.5,1433 (change to whatever host and port the server is on). Even though there’s a separate option for Port=, that doesn’t work with this driver. So make sure you remove Port= if that’s in there.
Got some problem trying to connect using tsql and isql on Centos. It always give me error :
#tsql -H 192.168.1.187 -p 1433 -U dbusername -P dbpassword
error:
locale is “en_US.UTF-8”
locale charset is “UTF-8”
using default charset “UTF-8”
Error 20009 (severity 9):
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 111, “Connection refused”
There was a problem connecting to the server.
#isql -v MSSQL MSSqlUser MSSqlUserPassword
error:
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
You have new mail in /var/spool/mail/root
i am not getting what to do with this error. i tried everything that posted in internet. but no use i am getting same error.
Help me out this
Thanks in advance.
Worked like a charm on CentOS 6! Very good documentation! Thank you so much!
/path_do_sql_client_download/sqlncli-11.0.1790.0
-bash: cd: /path_do_sql_client_download/sqlncli-11.0.1790.0: No such file or directory
it should be
/path_to_sql_client_download/sqlncli-11.0.1790.0
I got everything working util the test run from php /path/script.php…. return exception with message “Could no find driver”… what extension should I have to set in php.ini???
Regards
If I remember I haven’t change anything in php.ini file only setup odbc.ini