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