Connecting to MS-SQL from Pfsense 2.2.1
-
Hi guys,
I am trying to run a php script which needs to use ms sql server on pfsense 2.2. I read an explanation in this forum which was too old (freebsd 8).
Here is what i've done :1. Installing UnixOdbc , FreeTDS from freebsd pkg.
2. Enabling mssql_connect by installing php55-mssql-5.5.23.But the problem here is since the installed php version is 5.5.19, installing "php55-mssql-5.5.23" will break the webgui (because it upgrades the installed php to 5.5.23).
How can i overcome this issue? There is no "php55-mssql-5.5.19" available as far as i have searched.
Thank you in anticipation.
-
Is there a possibility of installing the services you need in a separate VM?
-
PHP is at 5.5.23 with 2.2.2 release. Other than that, you should stop installing crap on your FW.
-
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.
-
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,
-
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 -
Yes - You should wipe your box, reinstall pfsense, restore your old config and then put your miscellaneous services on a different box or VM.
-
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).
-
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.23I 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?
-
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.
-
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.
-
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 :) -
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? -
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.
-
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.
-
What kind of system?
-
A LAN Accounting Suite.
