Connecting to MS-SQL from Pfsense 2.2.1

kejianshi

Yeah - Its really better to leave the firewall unmolested if possible. I usually run as many of these sorts of services in a VM and leave pfsense alone.

Its really better for security.

mrvahid9

Thank you kejianshi and doktornotor.

I wasn't aware of the 2.2.2 release. So, I will give it a try. Unfortunately, Using a separate VM is not an option.

Regards,

mrvahid9

Hi dears,

I've just installed version 2.2.2 of pfsense. Just after installing "php55-mssql5.5.23", my webgui has been broken. Although php 5.5.23 is preinstalled on pfsense 2.2.2, this package tries to reinstall it.

Do you know what should i do ?
Can i install this package without reinstalling php ?
Is there any switch/option available to stop reinstalling package dependencies ?

ps: is use this command :==> pkg install php55-mssql5.5.23

Thanks,
Vahid

kejianshi

Yes - You should wipe your box, reinstall pfsense, restore your old config and then put your miscellaneous services on a different box or VM.

Gertjan

I guess mrvahid9 tries to use the PHP extenion "mssql" (not "mysql") on the PHP that is used on pfSense, so he can access an MSSQL server to obtain user authentication info.
True, that worked before.

Btw: when reinstalling, do not just re-import the config.xml. If this file contains info about: "install FreeBSD package mssql" then remove that info before feeding it to pfSense.

I guess its still possible to obtain the correct version of "MSSQL php55-mssql5.5.23" (if one exists).

mrvahid9

Dear Gertjan,
Thats true, I want to connect to mssql from pfsense to get some info. here is what i've done:

pkg update
pkg install unixODBC
pkg install freeTDS
pkg install php55-mssql-5.5.23

I connected to mssql by doing some configuration for freeTDS . Everything is fine til the first reboot.
I've attached two screenshots. First one is the message it shows when rebooting from the shell, Second, is the message appears into my webgui.

What do you think?

reboot.png_thumb

webgui.png_thumb

kejianshi

So this is more about learning how to hack the box than to use it then?

No wonder you can't use a SQL install in a VM. haha.

doktornotor

Sir. You are breaking your firewall by installing unsupported PHP versions. Stop doing this, period. The entire configuration is built around PHP. There is a custom-built PHP module for this as well – which, guess what, does NOT exist with the FreeBSD PHP package.

This is really ridiculous. This is a firewall, not a general purpose box for random crap like your Microsoft SQL connector for god knows what.

Gertjan

Way back, MSSQL PHP could be added easily.
If I remember well, the packages like FreeTDS were needed.

I had a choice to make back then: Our clients (a hotel) should only have Capitive Portal access when they were "checked in". The hotel CMS ran on a huge MSSQL database (which ran on Windows 2008 R2 - it still does).
(Plan B: parsing a MSSQL table, cloning it to (another) a MYSQL server - and use a FreeRadius server to bridge pfSense using FreeRadius to this MySQL server ….. well ...... No.)

Just adding MSSQL (php55-mssql-5.5.23) support doesn't break, neither upgrade or change the installed PHP (pfSense) version - it just ads MSSQL support.
As you all can see (http://your-pfsense-box/phpinfo.php) the PHP version of pfSense ( 5.5.23) has full MySQL support build in- that was different some versions ago. Take note : we are talking about the same firewall here - it has MySQL support now ;)
Quit normal that MSSQL is missing (it's another boat-load of code).
Btw : checkout the phpinfo(): it says "MS SQL Frank M. Kromann" so this PHP version could accept MSSQL after the module (package) has been installed.

As always: it can be done, and should be redone when upgrading (so people upgrade less often : this is a security issue !!)
I can't think about how this could involve a security issue other then that.
Keep in mind : more code means alway : more bugs.

Today, I shifted back to a more static user login : room number and a fixed password for everybody with the help of the pfSense local user manager.
Because: I do not sell "Internet time" like we do not bill for water, electricity, video channels, swimming pool usage, car stationing and phone usage in our hotel.
I just want to keep the kids out :)

mrvahid9

Dears,

I think what I am trying to do, is not considered as a security issue. I just want to be able to connect to mssql from my firewall to be able to query the Users database of the company. Which part of this process is awkward?

Database systems are pretty matured and ubiquitous these days. So, why are you calling this "ridiculous"?

Beside, when connecting to databases like SQLite is inherent in Pfsense, so why not MSSQL ?

–---------------------
Gertjan,
As I have posted here, just after installing PHP55-MSSQL-5.5.19 library, these strange notifications are appeared inside my Webgui.
maybe I have to choose another solution like using python or C/C++ for this purpose?

doktornotor

@mrvahid9:

just want to be able to connect to mssql from my firewall to be able to query the Users database of the company. Which part of this process is awkward?

Probably the one where someone had the awesome idea to put users into MSSQL instead of LDAP – which is what the remaining 99.99999% of mankind uses for this purpose.

mrvahid9

Doktornotor,

I'm facing a legacy system with database-centric design. need to read some information from there. BTW, thank you for your help.

As a conclusion, for other people probably facing the same issue, THERE IS CURRENTLY NO WAY TO CONNECT TO MSSQL FROM PHP.

kejianshi

What kind of system?

mrvahid9

A LAN Accounting Suite.