Configure unixODBC in CentOS to connect to mssql in PHP

Source: Internet
Author: User
Tags odbc connection
After changing my work, I have been busy with the LINUX system architecture, and have gone through the pains of never understanding too much to be familiar with others. In the course of work, Baidu has always been the preferred search engine. But later, I had to and eventually used Google. This helps me better and more. OK. Let's get back to the topic. the number currently used by the company

After changing my work, I have been busy with the LINUX system architecture, and I have never been too familiar with myself. In the course of work, Baidu has always been the preferred search engine. But later, I had to and eventually used Google. This helps me better and more.
OK. Let's get back to the topic. The company's current databases are quite complicated. MSSQL, ORACLE, and MYSQL are all in use. we used the WINDOWS + PHP environment before, in this case, it is easier to use PHP to connect to MSSQL, but the performance is much worse than LAMP. Therefore, the main task of the new job is the migration of system and WEB configuration.

However, in the LAMP environment, PHP has many problems with MSSQL. The company's single-point login problem cannot be achieved. It cannot reflect the advantages of LAMP over WAMP. What should I do? use BAIDU. However, it is found that many Chinese solutions cannot be implemented either when the version is old. Later, I chose GOOGLE and found many E-files solutions. I tried to do it. although I had taken some detours, I finally realized my needs. during this period, I carefully read the documentation and solutions provided by our predecessors on the freetds official website. Thanks to them.
Currently, I have installed it in the following situations:
First, LAMP has been installed and configured during system installation. It is quite difficult to re-compile the PHP environment.
I use the RPM Package and TAR package to install the environment. PHP installation and configuration are not changed, but only ODBC can be used to connect to ms SQL (data source ).

Second, after the system is installed, use the TAR package to install and configure the LAMP environment. In this case, you can re-compile PHP.
I use the TAR package for installation, and there are two ways to re-compile PHP and not re-compile PHP. Recompilation can achieve direct connection and ODBC Connection. ODBC Connection to MSSQL (data source) can be implemented without recompilation)


In this blog post, freetds is installed with the TAR package and unixODBC-devel and unixODBC are installed with rpm. Use ODBC to connect to MSSQL (data source ). (For more information about how to connect to MSSQL in php, and for several methods, refer to the HOWTO document in [url] [/url, I used two direct connections and odbc .)

I. installation environment
1, CentOs 5.1 + FreeTds0.64.tar.gz + unixODBC-2.2.11-7.1 (except FreeTds is an additional package, other can be installed using YUM, or installed on the system)
2. it is best to install XWINDOWS. I installed GNOME because it will be used when configuring the DSN (data source. Of course, you can also configure it in the text interface.

II. Installation and version display of some packages
1. [root @ rpm-mssql ~] # Rpm-aq | grep httpd
Httpd-2.2.3-11.el5_1.Centos. 3
Httpd-devel-2.2.3-11.el5_1.centos.3 (This package contains include files, header files, and APXS utilities for the ApacheHTTP server. If you want to load additional modules (except for those provided by this product), you need it. Hey hey, if you don't use HTTPD for TAR package when installing PHP, you can use -- with-apxs2 =/usr/sbin/apxs. Additional instructions on installing this)

2. [root @ rpm-mssql ~] # Rpm-aq | grep php
Php-Mysql-5.1.6-15. el5
You do not need to install as many as you need. This is because I customized the installation to the LAMP environment during system installation.

3. [root @ rpm-mssql ~] # Rpm-aq | grep unixODBC
Unixodbc-devel-2.2.11-7.1(the environment of this package is the same as httpd-devel,you must install freetds.tar.gz. it will automatically find the installation path of unixODBC. Otherwise, compilation errors may occur .)
UnixODBC-kde-2.2.11-7.1 (Comrades, this to install AH, was originally KDE desktop graphics configuration ODBC data source tool. It can also be used in GNOME .)

OK. I will not mention the installation package of mysql, because ODBC (DSN data source) is used to connect PHP to ms SQL in the LAMP environment.

4. packages required for installation:
Freetds-0.6.tar.gz [url]]
UnixODBC-devel-2.2.11-7.1.i386.rpm yum install-yunixODBC-devel [url] OS/I #/centos/#/url]

III. installation, testing and configuration:
1. install freetds (both under the root user. if you are under another user, remember to run make install su-root)
Note: install GCC compiler and other development tools and libraries during system installation. However, yum is used to install anything less in CentOs.
Note: the same conditions can be installed under REDHAT AS5.1 and as5.

[Root @ rpm-mssql freetds-0.64] # tar zxvf freetds-0.64.tar.gz
[Root @ rpm-mssql ~] # Cd freetds-0.64
[Root @ rpm-mssql freetds-0.64] #./configure -- with-tdsver = 7.0 -- with-unixODBC

Run the following command under the root user, especially make install
[Root @ rpm-mssql freetds-0.64] # make; make install
[Root @ rpm-mssql freetds-0.64] # make clean
OK. you have installed it now. The freetds. conf file is generated in/usr/local/etc.
[Root @ rpm-mssql freetds-0.64] # cd/usr/local/etc
[Root @ rpm-mssql etc] # ll
Total 12
-Rw-r -- 1 root 3572 Jan 24 19:13 freetds. conf
-Rw-r -- 1 root 362 Jan 24 19:13 locales. conf
-Rw-r -- 1 root 219 Jan 24 19:13 pool. conf

You can use ODBCConfig to configure it.
Note: After unixODBC-devel is installed, two files odbc. iniodbcinst. ini are generated in/etc:
[Root @ rpm-mssql freetds-0.64] # ll/etc/od
Odbc. ini odbcinst. ini. rpmnew oddjobd. conf. d/
Odbc. ini. rpmnew oddjob/odbcinst. ini oddjobd. conf

2. test and configure
A. After freetds is installed, use tsql to test it:
[Root @ rpm-mssql etc] #/usr/local/bin/tsql-S saPassword: password (xxx is a three-digit number, changed for security. And the following password is the actual SA password)
Locale is "en_US.UTF-8"
Locale charset is "UTF-8"
Password: (enter the Password again here)
If you are lucky enough, you will see "1>", indicating that your previous compilation is correct.
Next, let's get started. configure freetds. conf.

B. edit freetds. conf (this step can be omitted after testing. Because the purpose of installing freetds is to drive freetds. In fact, this also implies what you need to do when using the rpm Package)

[Root @ rpm-mssql etc] # vi/usr/local/etc/freetds. conf
Add the following code,
[MySql2k] (in FreeTds0.64, this parameter is very important. In "SystemDSN", you need to use. This is also a difference from the previous version)
Host =
Port = 1433
Tds version = 8.0

C. configure/etc/odbc. ini/etc/odbcinst. ini)
Here we need to use ODBCConfig/usr/bin/ODBCConfig
[Root @ rpm-mssql etc] #/usr/bin/ODBCConfig
ODBCConfig: cannot connect to X server

D. switch to XWINDOWS. As mentioned earlier, GNOME is used. you can also use KDE. UnixODBC-kde is dedicated for KDE.
[Root @ rpm-mssql etc] #/usr/bin/ODBCConfig, as shown in the following figure. However, it should be declared that the FreeTds version I use is 0.64, which is somewhat different from the previous version 0.63. It mainly changes "Servername" and "Setup. In the next series of diagrams, they are described separately.

The purpose of FreeTds installation is to provide a PHP driver for connecting to MSSQL. All the above actions are prepared for this purpose. If the driver has a loading path, you need to add a data source to make the connection to MSSQL successful.

The following E and F are highlighted in illustrated text.
E. The Driver setting process is the process of configuring/etc/odbcinst. ini.
Freetds0.64 adds support for 64-bit databases.

The following figure shows the odbcinst. ini file after graphical configuration. MySql2k is a new location:
[Root @ rpm-mssql ~] # Cat/etc/odbcinst. ini
Description = ODBC forPostgreSQL
Driver =/usr/lib/libodbcpsqlS. so
Driver64 =/usr/lib
Setup =/usr/lib
Setup64 =/usr/lib
UsageCount = 1
CPTimeout =
CPReuse =

Description = v0.64 withProtocol v8.0
Driver =/usr/local/lib/libtdsodbc. so
Driver64 =/usr/local/lib/libtdsodbc. so
Setup =/usr/lib/libtdsS. so
Setup64 =/usr/lib/libtdsS. so
UsageCount = 1
CPTimeout =
CPReuse =

F. creation of DSN data sources and precautions:
The image setting process of "System DSN" is the process of configuring the/etc/odbc. ini file:

The following figure shows the odbc. ini file after "System DSN" is configured. if you do not have or do not want to install the X Window, set it directly.
[Root @ rpm-mssql ~] # Cat/etc/odbc. ini
Description = MySql2k
Driver = MySql2k
Servername = MySql2k (here, do not set an IP address or something. it should be the same as setting in FREETDS. CONF. at least I tried this. this is not the case in FREETDS0.63 .)
Database =
UID = sa
Port = 1433

G, isql testing:

This is a success. In fact, you can stop this article. However. Let's test the effect of connecting PHP to ms SQL.

H. php page test
[Root @ rpm-mssql ~] # Vi/var/www/html/phpmssql. php
$ Conn_beidabiz = odbc_connect ("mssql", sa, "password"); ### password, which is your own direct password. ####
Echo "OK ";

The full text is over. The next article is estimated to write down the configuration of php connection to MSSQL using the rpm package.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.