Adding PHP MSSQL support … is it possible ?

Gertjan

without blowing the actual PHP 5.2.17 out of the water ?

Hi all,
I'm using pfSense 2.0.1 - plain basic vanilla setup using WAn+LAN+OPT1(= Wifi accès for our hotel clients, using a bunch of AP's).
All is working great- for years now.

My question is: Our (hotel) PMS system is using a Microsoft SQL 2008 R2 database.
This database contains tables with names, room numbers etc.

I'm able to write some SQL scripts so I can use the info from this MSSQL database to grant access to our clients (modifying the existing captive portal PHP code).
But : how do I integrate Microsoft SQL "PHP commands" the the actual PHP 5.2.17 setup that pfSense is using, so I can connect to the R2 2008 database ?

I already tried to pkg_add -r ftp://…... /php5-myssql.tbz but this also upgraded the PHP version to 5.3.2 ... and this isn't NOT what pfSense likes ... the box will not survive a reboot anymore.

Do I need to use FeeTDS + unixodbc ?
Some one has a walk-through for me ?

jimp

Use php52-mssql and not php5-mssql, and then touch /etc/php_dynamodules/mssql, then /etc/rc.restart_webgui

Gertjan

Thank for the follow up.

Correct, installing
http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
looks better.

after the
/etc/php_dynamodules/mssql
and the
/etc/rc.restart_webgui
mssql.so was loaded (visible when I issued a php -m)

Please note: I had to use this
/rc.php_ini_setup
so that the php.ini file was re-generated.
(from what I understood, "rc.php_ini_setup" will include touched file names from 'php_dynamodules' to the php.ini file)
Nevertheless
The function mssql_connect() was still unknown. phpinfo() didn't show any mssql support.

BUT: the package php52-mssql-5.2.13_3.tbz depends on PHP 5.2.13 …. (and 4 more packages like freetds, libxml, libiconv and pkg-config - these installed without issues)
5.2.13 was installed and the cli version of PHP became 5.2.13 - the web server still showed 5.2.17 at that moment (that might explain something), which is the version used by pfSense 2.0.1
Also, some issues started with the lib pcre.so (it wasn't present anymore) - so function like preg_match_all() became unavailable. And that means: no good.
Adding the package php52-pcre-5.2.13_3.tbz put that straight, but, as before, pfSense continued functioning, but all web server activity died.

I'll put this on a hold, and use a second pfSense station to install the dev version.
Maybe I should 'make' my own PHP …. (hoping that the special libs like pfsense.so will still be accepted ...)

jimp

You might also try:

pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

That should tell it to force the install and not install dependencies, so it won't bring along the other cruft.

sash99

just for your information
I find that if you you do a software installation if you reinstall some of pfsense original software it breaks it ie:freetds, libxml, libiconv and pkg-config and others

your best bet it to step through the installation and ignore the one that are already installed

so do a pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

then install manually individual packages

pkg_add -rfi

skipping out ones that already installed
which if your not certain which ones they are can be a bit of trial and error. and you might break pfsense a couple times before you get it right.

the easiest way to do this is I find
first do this pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

touch /etc/php_dynamodules/mssql

then

/etc/rc.php_ini_setup

it will then tell you what broken dependency you have install that one
then again
/etc/rc.php_ini_setup

it list the next broken dependency

install and repeat

a short cut to doing it is
pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
then just copy and paste the need dependency as they come up, from this list and adding it to this line
pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/
and then adding .tbz to the end
and enter

hope this helps you out..

Gertjan

Also thanks for your solution, sash99 !

This is actually when I did.
I discovered that freetds - libxml -libiconv - pkg-config are pretty standalone packages, so I installed them first.
Then , adding pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz doesn't break anything any more.
Followed to that:
touch /etc/php_dynamodules/mssql
and a
/etc/rc.php_ini_setup
and even a
/etc/rc.restart_webgui

a php -m informed me that the mssql was loaded:


apc
cgi-fcgi
...
mssql
...
zlib
zmq

/tmp/PHP_errors.log didn't mention any module errors error.

BUT : using mssql php functions still shows errors like 
[i]Fatal error: Call to undefined function mssql_connect() in /usr/local/www/aaa.php on line 7 [/i]
phpinfo() doesn't show any mssql support.

sash99

hi gertjan

perhaps it is a typo or a brain fart as people sometimes call it
but you did not install php52-mssql-5.2.13_3 when you did this

pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

it only lists what the software dependencies are

for this package this is how I would install it.
you might have to do a clean install twice. or use a second test machine for proofing
on the proofing machine do a
pkg_add -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz

(which will break pfsense. but that fine do not worry about it we are only using it for proofing)

when you do this it will install all the software and dependencies. but it will also list the ones that are already installed make note of which ones they are then on the test machine manually install the packages that are not installed

now on the test machine do this: ( i installed it on my machine to proof it for you )

pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
then a ( or vice versa does not mater)
pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
then a
/etc/rc.php_ini_setup
it will say missing dependency - libsybdb.so ( if you do a google search it tells you it is part of freetds)
then do a ( the package name I grabbed from the pkg_info used it for reference)
pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/freetds-msdblib-0.64_5,1.tbz
then do a
/etc/rc.php_ini_setup
no broken dependencies
touch /etc/php_dynamodules/mssql
/etc/rc.php_ini_setup
exit
the shell displays properly no listing of broken files mssql is properly installed

when i go to my vhost web server on my pfsense box and look at the php informational page it list it up and running

basically if you follow my method you should be able to install any php module you want with out breaking pfsense

good luck hopefully now it works for you

Gertjan

Thanks again for the info.

Ofcourse, I didn't use "pkg_info -r …." but "pkg_add -r", but a "pkg_info -r ...." is usefull because it lists this:

Dependency: pkg-config-0.23_1
Dependency: libiconv-1.13.1_1
Dependency: libxml2-2.7.7
Dependency: php52-5.2.13_3
Dependency: freetds-msdblib-0.64_5,1

I "pkg_add -r …" all of these first, except the one that will downgrade the installed version of PHP coming with pfSEnse, which is 5.2.17
Then I pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz so that the package php52-mssql comes in WITHOUT php52-5.2.13_3

From what I understood, if I do a
pkg_add -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
right away, pkg-config - libiconv - libxml2 - freetds-msdblib and php52-5.2.13_3 will have to get installed manually, which I did.
No errors like "libsybdb.so is missing", because freetds-msdblib was also installed.
…..

I discovered something.
Using php - the cli version, I had this
php -i | grep mssql


## <a name="module_mssql">mssql</a>

mssql.allow_persistentOnOn
mssql.batchsize00
mssql.charset_no value__no value_
mssql.compatability_modeOffOff
mssql.connect_timeout55
mssql.datetimeconvertOnOn
mssql.max_linksUnlimitedUnlimited
mssql.max_persistentUnlimitedUnlimited
mssql.max_procsUnlimitedUnlimited
mssql.min_error_severity1010
mssql.min_message_severity1010
mssql.secure_connectionOffOff
mssql.textlimitServer defaultServer default
mssql.textsizeServer defaultServer default
mssql.timeout6060

So, mssql support is enabled …. when asking php on the command line (cli version).
Not when I make a page like
/phpinfo.php

 phpinfo()
?>

Info from php - cli version:

PHP 5.2.17 with Suhosin-Patch 0.9.7 (cgi-fcgi) (built: Sep 20 2011 10:54:21)
Copyright 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2010 Zend Technologies
with Suhosin v0.9.27, Copyright (c) 2007, by SektionEins GmbH

The web version:

FreeBSD pfsense.brithotelfumel 8.1-RELEASE-p6 FreeBSD 8.1-RELEASE-p6 #0: Mon Dec 12 17:53:00 EST 2011 root@FreeBSD_8.0_pfSense_2.0-snaps.pfsense.org:/usr/obj./usr/pfSensesrc/src/sys/pfSense_SMP.8 i386
Build Date Sep 20 2011 10:53:14

So I figure is messed up again ….

sash99

Humm? I was only going by what you qouted

@Gertjan:

This is actually when I did.
I discovered that freetds - libxml -libiconv - pkg-config are pretty standalone packages, so I installed them first.
Then , adding pkg_info -r http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz doesn't break anything any more.
Followed to that:
touch /etc/php_dynamodules/mssql
and a
/etc/rc.php_ini_setup
and even a
/etc/rc.restart_webgui

like I said I installed mssql on my pfsense box it seems to work
from a clean install of pfsense on my computer
i installed
vhost and bandwidthd ( plus some extra php modules as this is my test machine I pre loaded)

then i installed your package you wanted on my box by doing this

pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/php52-mssql-5.2.13_3.tbz
then
/etc/rc.php_ini_setup
it said missing dependency - libsybdb.so

then
pkg_add -rfi http://ftp-archive.freebsd.org/pub/FreeBSD-Archive/old-releases/i386/8.1-RELEASE/packages/All/freetds-msdblib-0.64_5,1.tbz

then
/etc/rc.php_ini_setup

no broken dependencies

touch /etc/php_dynamodules/mssql
/etc/rc.php_ini_setup

then once I start vhost and check to see if it working it displays this in the php info
(but I will admit I have many extra php module install on this test pfsense computer
gd json, dom, mcrypt, mysql, PDO , pdo mysql, pdo sqlite, and zip. but perhaps it this is why it seems works on my computer and not yours but i doubt it..)

mssql

MSSQL Support enabled
Active Persistent Links 0
Active Links 0
Library version FreeTDS

Directive Local Value Master Value
mssql.allow_persistent On On
mssql.batchsize 0 0
mssql.charset no value no value
mssql.compatability_mode Off Off
mssql.connect_timeout 5 5
mssql.datetimeconvert On On
mssql.max_links Unlimited Unlimited
mssql.max_persistent Unlimited Unlimited
mssql.max_procs Unlimited Unlimited
mssql.min_error_severity 10 10
mssql.min_message_severity 10 10
mssql.secure_connection Off Off
mssql.textlimit Server default Server default
mssql.textsize Server default Server default
mssql.timeout 60 60

it seems to work for me, though I have no ms sql machine to prove that it actually working .

sash99

but I think I see where are differences are coming from, you're working within the pfsense web root folder. and I am working from with in the pfsense vhost web root folders. two completely different areas. perhaps installing them this way does not give pfsense main web root the added php modules. and most likely you will have to modify that region manually.. ( i have no clue where that region lies)

a couple of method to solve this :

an easy method would probably be to create a vhost create your webpage in there and create sys link to the area with in the pfsense web root folder giving your self permission to what ever you need editing by your web page.

or deconstruct one of pfsense packages ie: ntop and see where they are changing the php ini for the pfsense web root or simply keep hunting untill you find the php area for pfsense's web root

if I was to guess the area you probably need to look at is /usr/local/php5 probably extract the mssql.so from the tbz and copy it to the ext folder and then possible enable the extension in one of the php ini or config files in that same folder

Gertjan

Big progress ….

Believe it or not.
The error is now "Unable to connect to server: 192.168.1.4 in /usr/local/www/aaa.php on line 9 Erreur de connexion MSSQL"
which is ok for me.
This is just a matter of calling mssql_connect() with the right params.

The thing that made it work: I rebooted ....... ;D

edit: test results ...
This is the code: http://paste2.org/p/1938572
This is the ouput: http://paste2.org/p/1938560 (my SQL server is a Windows 2008 R2 SQL setup)

pfSense 2.0.1 connects to MSSQL .....

Thanks you both for the support :D

sash99

glad you got it working :)

took me 6 hrs to figure out how to get php modules to load into pfsense with out breaking it.

that's why i posted my howto's get them to install, so other people world not have the same problem that i had..

also for my "memory back up". because in a couple months when I need to do it again for some reason I will probably of forgot how to do it.. :P