Netgate Discussion Forum
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Search
    • Register
    • Login

    Connecting to MS-SQL from Pfsense 2.2.1

    webGUI
    4
    17
    7.2k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • M
      mrvahid9
      last edited by

      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.

      1 Reply Last reply Reply Quote 0
      • K
        kejianshi
        last edited by

        Is there a possibility of installing the services you need in a separate VM?

        1 Reply Last reply Reply Quote 0
        • D
          doktornotor Banned
          last edited by

          PHP is at 5.5.23 with 2.2.2 release. Other than that, you should stop installing crap on your FW.

          1 Reply Last reply Reply Quote 0
          • K
            kejianshi
            last edited by

            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.

            1 Reply Last reply Reply Quote 0
            • M
              mrvahid9
              last edited by

              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,

              1 Reply Last reply Reply Quote 0
              • M
                mrvahid9
                last edited by

                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

                1 Reply Last reply Reply Quote 0
                • K
                  kejianshi
                  last edited by

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

                  1 Reply Last reply Reply Quote 0
                  • GertjanG
                    Gertjan
                    last edited by

                    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).

                    No "help me" PM's please. Use the forum, the community will thank you.
                    Edit : and where are the logs ??

                    1 Reply Last reply Reply Quote 0
                    • M
                      mrvahid9
                      last edited by

                      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
                      reboot.png_thumb
                      webgui.png
                      webgui.png_thumb

                      1 Reply Last reply Reply Quote 0
                      • K
                        kejianshi
                        last edited by

                        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.

                        1 Reply Last reply Reply Quote 0
                        • D
                          doktornotor Banned
                          last edited by

                          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.

                          1 Reply Last reply Reply Quote 0
                          • GertjanG
                            Gertjan
                            last edited by

                            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 :)

                            No "help me" PM's please. Use the forum, the community will thank you.
                            Edit : and where are the logs ??

                            1 Reply Last reply Reply Quote 0
                            • M
                              mrvahid9
                              last edited by

                              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?

                              1 Reply Last reply Reply Quote 0
                              • D
                                doktornotor Banned
                                last edited by

                                @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.

                                1 Reply Last reply Reply Quote 0
                                • M
                                  mrvahid9
                                  last edited by

                                  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.

                                  1 Reply Last reply Reply Quote 0
                                  • K
                                    kejianshi
                                    last edited by

                                    What kind of system?

                                    1 Reply Last reply Reply Quote 0
                                    • M
                                      mrvahid9
                                      last edited by

                                      A LAN Accounting Suite.

                                      1 Reply Last reply Reply Quote 0
                                      • First post
                                        Last post
                                      Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.