Making a PHP Site on Linux Work with a Microsoft SQL Server Database

It's a LAMP site, but the M isn't MySQL. Can PHP on the Linux box make the connection to Microsoft SQL Server?
______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

make error

nedved8891's picture

I need your help! I downloaded freetds-0.60.tgz. Next, uncompress, configure - this is OK. But I can not run make.

make[3]: *** [odbc.lo] Error 1
make[3]: Leaving directory `/home/nedved/freetds-0.60/src/odbc'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/home/nedved/freetds-0.60/src/odbc'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/home/nedved/freetds-0.60/src'
make: *** [all-recursive] Error 1

Configuring freetds problem

khemlal's picture

Hi, I am doing a project with PHP using MSSQL server in linux. I am having some problem while connecting to the server. I tried downlaoding freetds-0.60 and above and installing as directed in your above article. But i could not configure with the given step and command above in your article. and some of the files are missing and could not configure error appears on the terminal. So i cannot move further with the other steps.
So Please help me with a good and easy step or do let me know the problem of it. Suggest me with some ideas and steps.

Thanks

plz help me

deepakprime's picture

your article has been really helpful
I am able to connect to the DSN from the terminal.
but i am not able to connect to the DSN from PHP
"Warning: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect"

Pls HELP

Unicode MAX data type

Anonymous's picture

This works great for basic ASCII and numeric data types, but I have a requirement to deal with large data strings stored as Unicode in SQL Server. Has anyone been able to successfully get the SQL Server data type NVARCHAR(MAX) into and out of the database from PHP?

sql data base

Anonymous's picture

im not able to connect to sql instances on linux red hat thrugh web browser

Zend mssql-extension (built on freetds)

Claes J's picture

On debian i Installed Zend Server CE (http://www.zend.com/en/products/server-ce/), and the extra mssql module. It actually worked like a charm :-)

1. Edit /etc/apt/sources.list and add these lines...

# Zend
deb http://repos.zend.com/zend-server/deb server non-free

2. Install php from the Zend repository (will remove any debian maintained php installation)

apt-get install php-5.3-mssql-zend-server

3. Try it out!

<?php

$db=mssql_connect('mssql.server.local:1433','user','pass') or die("Connect failed");

$q = mssql_query('SELECT * FROM CITY', $db);

if (!mssql_num_rows($q)) {
echo "'No rows found"';
} else {
while ($row = mssql_fetch_assoc($q)) {
echo "";
print_r($row);
}
}
?>

freeTDS on Edgy EFT

GrahamD's picture

Great article, but I'm stuck on the last step.
First a few tips for Ubuntu users, you need unixodbc-dev and tdsodbc. With these packages I could compile freeTDS. Connected to my database with isql and could query the tables.
When I tried the example using php I get the error "Call to undefined function odbc_connect()"

Any ideas ?

Graham

I don't think odbc support

Greg1976's picture

I don't think odbc support is installed with PHP under Ubuntu.

Try

apt-get install php4-odbc

OR

apt-get install php5-odbc

Help please!!

Reda's picture

Hi everybody, how are you?

I'm really lost, i've been trying to make this work several weeks but i can't...!! did anyone make it?

my website is hosted on a linux server (redhat 7.2) and i would like to connect the mysql database to a mssql database on a windows server!!

i downloaded several versions of freetds (0.60, 0.62...) but the terminal returns some errors when i run 'make', here is part of the errors:

../../include/tdsodbc.h:26:17: sql.h: No such file or directory
../../include/tdsodbc.h:27:20: sqlext.h: No such file or directory
../../include/tdsodbc.h:28:22: odbcinst.h: No such file or directory
In file included from odbc.c:60:
odbc_util.h:28:17: sql.h: No such file or directory
odbc_util.h:29:20: sqlext.h: No such file or directory
In file included from odbc.c:63:
prepare_query.h:28:17: sql.h: No such file or directory
prepare_query.h:29:20: sqlext.h: No such file or directory
make[3]: *** [odbc.lo] Erreur 1
make[3]: Quitte le répertoire `/root/freetds-0.63/src/odbc'
make[2]: *** [all-recursive] Erreur 1
make[2]: Quitte le répertoire `/root/freetds-0.63/src/odbc'
make[1]: *** [all-recursive] Erreur 1
make[1]: Quitte le répertoire `/root/freetds-0.63/src'
make: *** [all-recursive] Erreur 1

is that normal ?

i also wanted to try : ./configure --with-mssql=/usr/local/freetds but should i put after '='? what path is '/usr/local/freetds'??

Thank you thank you so much for your help.
have a good day

about :Making a PHP Site on Linux Work with a Microsoft SQL

Anonymous's picture

Hi,
All the processes have been successful before this step:
isql -v MyLServer username password

when I execute isql -v MyServer UsedId Password
i get the message
[unixODBC][FreeTDS][SQL Server]Unable to connect
to data source
[ISQL]ERROR: Could not SQLConnect
.

when i open the DataManager and expand the
ODBC > System Data Source > MyServer

input for ID
and PWD

After giving the input I get the message
[unixODBC][FreeTDS][SQL Server] unable to connect to the data source

and how could I can reconfigure the packages because in the Linux cd all the packages are rpms.

Plz , show me the way.............

[IM002][unixODBC][Driver Manager]Data source name not found, and

bill's picture

Please make sure that the named section that contains the ODBC driver for MySQL in your odbcinst.ini file matches the driver name you point to in your odbc.ini file.

For example...

[MySQL-test]
Description = MySQL database MusicDB
Driver = MySQL
Server = 192.168.0.17
Database = test
Port = 3306
Socket =
Option =
Stmt =

Since this file points to a driver named "MySQL" your obdcinst.ini file section for that driver needs to start with

[MySQL]

There is an OpenOffice howto on the net that overlooks this important detail.

Hope this helps.

Cheers,

Bill

[ISQL]ERROR: Could not SQLConnect

Anonymous's picture

Hi, I have the same problem:
-----------------------------------------------------------------------
[root@localhost ~]# isql -v MSSQLServer linuxuser mypassowrd
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect
-----------------------------------------------------------------------
I get the error: Could not SQLConnect; I alreadu made sure the odbcinst.ini & odbc.ini have the right settings!

any suggestions will be gladly apreciated!
thank you

[ISQL]ERROR: Could not SQLConnect

Anonymous's picture

Hi, I have the same problem:
-----------------------------------------------------------------------
[root@localhost ~]# isql -v MSSQLServer linuxuser mypassowrd
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect
-----------------------------------------------------------------------
I get the error: Could not SQLConnect; I alreadu made sure the odbcinst.ini & odbc.ini have the right settings!

If u hav got the answer please let me know.....

Making a PHP Site on Linux Work with a Microsoft SQL Server Data

Anonymous's picture

your article has been really helpful
I am able to connect to the DSN from the terminal.
but i am not able to connect to the DSN from PHP
"Warning: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect"

Pls HELP

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

For an excellent out of the box solution, I highly recommend looking into UniverSQL.

A point-and-click setup, remote admin tools (for both Windows and Linux) and very high performance led my company to choose this approach.

NB

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

For an excellent out of the box solution, I highly recommend looking into UniverSQL.

A point-and-click setup, remote admin tools (for both Windows and Linux) and very high performance led my company to choose this approach.

NB

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Hi, how can i configure unixODBC without gui, I'm using

R.H. 8.0

unixODBC-2.2.5

freetds-0.61

i'm trying with template files, but it doesn't work, the freetds.log file looks like:

2003-03-04 12:40:22.003738 iconv will convert client-side data to the "" character set

2003-03-04 12:40:22.003928 IP address pointer is NULL

2003-03-04 12:40:22.003949 Server SYBASE not found!

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

The unixODBC gui writes to /etc/odbc.ini and /etc/odbcinst.ini

If you haven't already, try something like this in odbc.ini:

[MSSQLServer]

Description = TDS MSSQL

Driver = TDS

Servername = your.server.com

UID = sa

PWD =

Port = 1433

and

in odbcinst.ini...

[TDS]

Description = v0.6 with protocol v7.0

Driver = /usr/local/freetds/lib/libtdsodbc.so

Setup = /usr/lib/libtdsS.so

FileUsage = 1

CPTimeout =

CPReuse =

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

hpoyatos's picture

In the most recent versions of PHP (4.2.x, 4.3.x), exists another way to access a Microsoft SQL Server from PHP in Linux. Compile the PHP source in parameter :

$./configure --with-mssql=

Examples :

$./configure --with-mssql=/usr/local/freetds

$./configure --with-mssql=/usr/local/lib/

Then, $make, $make test, $make install...

If you will set the correct directory, you won't have problems.

Make a page with and search the mssql table. If have one, The direct suport to MSSQL is done.

Now, I can use the mssql commands (mssql_connect(), mssql_bind()) to work. More informations in http://www.php.net/manual/en/ref.mssql.php.

Sorry for my terrible English ! Hope it helps.

Please Help!!

Reda's picture

Hi everybody, how are you?

I'm really lost, i've been trying to make this work several weeks but i can't...!! did anyone make it?

my website is hosted on a linux server (redhat 7.2) and i would like to connect the mysql database to a mssql database on a windows server!!

i downloaded several versions of freetds (0.60, 0.62...) but the terminal returns some errors when i run 'make', here is part of the errors:

../../include/tdsodbc.h:26:17: sql.h: No such file or directory
../../include/tdsodbc.h:27:20: sqlext.h: No such file or directory
../../include/tdsodbc.h:28:22: odbcinst.h: No such file or directory
In file included from odbc.c:60:
odbc_util.h:28:17: sql.h: No such file or directory
odbc_util.h:29:20: sqlext.h: No such file or directory
In file included from odbc.c:63:
prepare_query.h:28:17: sql.h: No such file or directory
prepare_query.h:29:20: sqlext.h: No such file or directory
make[3]: *** [odbc.lo] Erreur 1
make[3]: Quitte le répertoire `/root/freetds-0.63/src/odbc'
make[2]: *** [all-recursive] Erreur 1
make[2]: Quitte le répertoire `/root/freetds-0.63/src/odbc'
make[1]: *** [all-recursive] Erreur 1
make[1]: Quitte le répertoire `/root/freetds-0.63/src'
make: *** [all-recursive] Erreur 1

is that normal ?

i also wanted to try : ./configure --with-mssql=/usr/local/freetds but should i put after '='? what path is '/usr/local/freetds'??

Thank you thank you so much for your help.
have a good day

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Excellent!!!!

I've searched for weeks for a guide like this one.

It worked for me straith forward...

My system:

Red Hat 8.0

FreeTDS 0.6

Thanks.

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Andika's picture

I'm using Mandrake with PHP, and i want to connect it to SQL Server...

I'm using Freetds 0.6, but still confuse...
and i couldn't find this file Driver =/usr/local/freetds/lib/libtdsodbc.so

[root@10 source]# isql -v MSSQLServer ccjkt password
isql: error while loading shared libraries: libreadline.so.5: cannot open shared object file: No such file or directory
[root@10 source]#

What should i do ???

Thank's

Andika Arsi P

Please Help!!

Reda's picture

Hi, i've been trying connecting my mysql databased hosted on a linux server (red hat) to an mssql database on windows but i couldn't do iy!! could you please tell me how you did?

thank you very much

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Just for those bashing this article because Linux Journal posted an article about connecting to MS SQL Server via PHP and FreeTDS.

The article was written for real world use of Linux, such as, in large corporations.

In large corporations, they use a variety of databases: IBM DB2, Oracle, MS SQL, etc...

If the web servers in the company use PHP on Linux machines, how do you retrieve data from the MS SQL database when MS only supports accessing MS SQL from Windows? Answer: FreeTDS and unixODBC.

Both FreeTDS and unixODBC are open source software.

Moreover, this article was written for paying customers of their magazine - the ones most likely using Linux in the corporate world - ie. the real world.

Boycott egov-OS and stop the Microsfot madness!

Anonymous's picture

Boycott Microsoft and the Egov-OS conference. NYFAIRUSE

is leading aprotest. Microsoft wants to destroy Free Software and your rights!

No more Microsoft articles in Free Software Journal!

They can pay for their own advertising!

Re: Boycott egov-OS and stop the Microsfot madness!

Anonymous's picture

Get a grip! The facts are that computing involves both free and non-free software. There is no harm in being familiar with both Linux/Unix and Microsoft products.

Re: Boycott egov-OS and stop the Microsfot madness!

dmarti's picture

Companies don't shut down existing, working, systems if they don't have to. It's better for free software to integrate with proprietary software, and then replace it piece by piece when the user decides to.

How did the GNU Project get users, and get good? By making a good editor, tools, and libraries that ran on proprietary Unix. We have a stable all-free system today because people spent 1984-1991 developing and testing parts of GNU in combination with proprietary software.

Re: Boycott egov-OS and stop the Microsfot madness!

Anonymous's picture

Don, although that it might be useful to sometimes integrate with existing systems, over all, this is a bad philosophy. The desktop environment is different from the server market, but the desktop market is more integrated and the verticle markets makes this approach not workable.

Even in the server market, this is a loosing proposition. For example, SAMBA has been intentionally broken on a number of occasion.

And then there is the issue of constantly playing follow the leader rather than spending the engery on being innovative.

Other all, there are a lot of hard working people working to replace the Microsoft nameless SQL Server with Free Software, and they deserve complete support on this issue.

Ruben

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

why use PHP's ODBC functions with FreeTDS?

http://www.php.net/mssql

Almost completely identical with PHP's MySQL functions, you can access the MS-SQL server directly without unixODBC. Simply compile PHP --with-mssql=/path/to/freetds and you're good to go.

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

But it only works if you are runing PHP on Windows platform! This article is talking about running PHP on a Linux platform.

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Because you might want to write code that abstracts away the database connection, and doesn't tie you to one database server. And you might want to migrate to another db later, like sqlserver -> postgreSQL ;)

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Enable PHP/MS SQL with --with-mssql, this is, by far, the best way to connect to MSSQL from PHP. Don't waste your time with ODBC. I use PHP via FreeTDS everyday without ODBC, using this setup.

Boycott of MS after the egov-os Fiacso

Anonymous's picture

Boycott Microsoft and the Egov-OS conference. NYFAIRUSE is leading aprotest. Microsoft wants to destroy Free Software and your rights!

No more Microsoft articles in Free Software Journal!

They can pay for their own advertising!

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Just a few questions:

1) How dog-slow is the ODBC connection?

2) Doesn't this violate your MS SQL Server license requiring you not to work with "viral" GPL software?

3) Why pay for MS SQL when there are many better alternatives from commercial companies that actually contribute to the community?

4) Why use a Detroit Diesel when you could use a Briggs & Stratton to do the job?

5) Don't you have to have a different server (Win NT or 2K) for MS SQL?

Properly annotated, this would be LAP, not LAMP or maybe WIMPO - (Windows/MS SQL/through PHP over ODBC).

I am certain some people might find this interesting, but Microsoft will eventually restrict you from doinng this with their licenses, if they haven't already.

Your article is well-written and good - don't get me wrong. It just seems more of a "why?" question to me.

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

To answer your first question, not at all. ODBC doesn't slow down your database connections, you won't see any noticeable performance penalty vs. native database connections (all other factors being equal). I've used unixodbc for over 3 yrs on linux/bsd, and seen benchmark testing done on it with very high loads, albeit not with PHP.

I have another question for you - if you're db is already SQLServer, why write your code for one database server, and then rewrite for another? With ODBC all you do is point your datasource at another driver, and rely on SQL compatibility between db's (which is very do-able for most web apps, among them those I've tried are mssql/postgreSQL/mysql/sybase/oracle/db2).

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

Why?

I was using php oracle/informix for web development for two years. As a result of the recent IT fallout, I found myself working for a company as an ASP/ASP.net and MSSQL programmer. Which was not good, but I produced some very usefull applications.

My company also supported numerous static sites on Linux. The powers that be decided that it would be best to move off of the linux server, because I could make "much better sites" on the Microsoft platform. I don't think they really understood the difference between PHP and ASP, but I had to use MSSQL for the database. Because "We had standardized on it" and, for Chrissakes, "security reasons".

By accessing MSSQL from PHP, not only are we still using Linux as a server, but I get to use good tools again to do my job.

if the EvilM stops allowing access through FreeTds, there are only two options:

1) Take two months to rewrite everything that has already been written at considerable cost.

2) Take two hours to install either MySQL or Postgress, which will result in an overall savings.

Like the other guy said, take what you can in small steps.

Re: Making a PHP Site on Linux Work with a Microsoft SQL Server

Anonymous's picture

To answer the question of "why?": because MS SQL is out there, and people need to be able to interact with it -- just like they need to interact with Windows NT domains (Samba), with Windows closed-source programs (Wine), and with any number of other things that Microsoft has inflicted upon the world.

Because MySQL, for all its zippiness, does not compare favorably with MS SQL in terms of feature set; and while there are other databases available on Linux that do, sometimes Linux advocacy requires taking things one step at a time.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix