Considerations on Connecting to MS SQL Server from PHP/Linux

[Google Mike]

After a lot of thought and research, and playing with FreeTDS and
InlineTDS, as well as various ODBC connections, I have determined that
the fastest and cheapest way to get up and going with PHP on Linux,
connecting to MS SQL Server, unless it was already pre-installed by
your Linux installation, is to build your own multithreaded TCP socket
server on Windows and connect to it through the socket API in PHP on
Linux (if you have installed that). I noticed that RH9 comes with PHP
4.2.2, but it doesn't come with the MS SQL connections. It does come
with MySQL and PgSQL connections, as well as socket API.

I previously had a lot of trouble building just the loadable PHP
modules for MS SQL Server. I could get them combiled for 4.3.x, but
then I couldn't load them in PHP 4.2.2 because I guess it was a
version conflict. I was faced with having to recompile and reinstall
PHP entirely on the latest stable version 4.x.x, and I feared this
would affect my already existing code I had written. I had no problem
installing InlineTDS or FreeTDS, then connecting them through to MS
SQL Server with the command-line tools, but getting PHP to talk to
that was looking to be near impossible. The same with ODBC on Linux.
In fact, there is no more discussion with me on this. After a month of
frustation, I'm stubbornly not going back in this direction. My next
PHP upgrade will be with whatever Fedora Linux ships with (when the
RedHat Fedora project starts making stable Fedoras). If MS SQL API
works in that release, then I'll use it. Otherwise, I'll use a socket
server.

One can build this socket server easily in VB6 or C#.NET on the MS
platform. (Aside: With C# out, why would one ever use VB.NET in
VS.NET? VB is dead.) You build a knock-knock port on the socket
server, and then it chats back the port number it agrees to talk to
the client on. (Besides the main knock-knock port, you should stick
with a range and not go totally random in the 5000-65535 range.) You
then send the SQL (including EXEC for stored proc and stored proc with
parameter support), and it chats back either an error, number of rows
affected, or row- and column-delimited table data (with schema). The
socket server could open up an OLE DB connection via ADODB (or
whatever works best in C#.NET). When the PHP concludes, it sends a
close port request to the socket server and that port becomes
available again. The socket server should also be smart enough to
watch port traffic for long delays, shutting them down, and you might
want to build something that resets all ports at a certain time at
night.

Once I built this, I used the W2K Resource Kit tools (srvany, instsrv)
to load the socket server EXE as a service in the Services Control
Panel. I then built a scheduled task that bounced this service at
midnight with a net stop, net start script. I found that this also
worked on XP, even though I didn't have the XP RK.

Encryption is probably not a recommended option for sending back table
data, however, because of the tremendous speed impedement with that.
Your best option is probably to authenticate your user/pass combo,
then put a proprietary CRC check on the command request stream. If
either of those fail in the command request, then it's being hacked
and the program can automatically block that IP address via an update
to an INI file on the socket server's host. Another option is to
encrypt all command-requests, but send replies as unencrypted,
blocking any IP addr that fails to send proper requests.

Once this connectivity is achieved from PHP to the socket server, you
can build a wrapper that makes the API look similar to the MySQL API
in PHP.

I wrote this in a day and it came back with 4,000 records extremely
quickly. On a 100Mbs network, I don't notice any speed difference
between using a local MySQL server and using a remote MS SQL Server.
(Aside: Here's another interesting factoid -- when I started building
this, I mistakingly had the socket server on 10Mbps and it STILL came
back fast!) I wouldn't want to rely on it for more records than 4,000,
however. A web app should probably be redesigned if it's relying on
pulling back more than about 1,000 records at a time. Such a web app
should probably utilize a stored proc or a tighter query to reduce the
number of returned columns and records.

Using OLE DB on the socket server, I found no problem connecting to MS
SQL 6.5, 7, and 2K (on the latest security service packs, mind you). I
also had no problem calling stored procedures with or without
parameters because I used the EXEC statement to achieve it.

If you build one of these, later on you'll start to realize the
powerful opportunities here, such as making this a two-way bridge
between Windows and Linux to send/rcv XML data and all kinds of
encrypted command requests, not just for database data. On the Windows
side, you could have a COM or .NET object that uses sockets to
interact with a special PHP URL on Linux that you build to receive
command requests.

Happy sockets, y'all!