Welcome Guest, Not a member yet? Register   Sign In
Codeigniter database connection over SSH
#1

[eluser]clustersblue[/eluser]
I saw several post regarding this topic (as per reference) on trying to connect to the database using SSH library specifically phpseclib, but those were already a year old without resolutions. This might be related to them on most part but I decided to open up new thread for my concern.

I wanted to connect to a MySQL server on a linux machine using codeigniter's own database library. Normally, I can use CI database library directly without a problem. But this time, I was told that I need to authenticate first with the server before MySQL authentication. Indeed, when I tried it this is what I got:

Code:
Unable to connect to your database server using the provided settings
Filename: D:\xampp\htdocs\controller\sys\database\DB_driver.php
Line Number: 124


I tried to connect to the database using MySQL Workbench and I noticed that I can connect to it using Standard TCP/IP over SSH connection method.

On my research I can do SSH connection in PHP using libraries. I found phpseclib as it is most talk about and I did able to successfully created a connection using that library. Below is my example code.

Code:
set_include_path(get_include_path() . PATH_SEPARATOR . APPPATH . 'libraries/phpseclib');
include(APPPATH . 'libraries/phpseclib/Net/SSH2.php');

            $ssh = new Net_SSH2(SSH_SERVER);
            $login = $ssh->login(SSH_USERNAME, SSH_PASSWORD);
                if (!$login) {
                    exit('Login Failed');
                }

Now, after verifying that it was successful I tried to load my database group (in Blue), thinking it will inherit the connection but it failed.

Code:
set_include_path(get_include_path() . PATH_SEPARATOR . APPPATH . 'libraries/phpseclib');
            include(APPPATH . 'libraries/phpseclib/Net/SSH2.php');

            $ssh = new Net_SSH2(SSH_SERVER);
            $login = $ssh->login(SSH_USERNAME, SSH_PASSWORD);
                if (!$login) {
                    exit('Login Failed');
                }
            
           [color=blue] $this->db = $this->load->database('local_dss_db',TRUE);[/color]

And my database config:

Code:
$db['local_dss_db']['hostname'] = "xxx.xxx.xxx.xxx:22";
$db['local_dss_db']['username'] = SSH_USERNAME;
$db['local_dss_db']['password'] = SSH_PASSWORD;
$db['local_dss_db']['database'] = "SSH_DB";
$db['local_dss_db']['dbdriver'] = "mysqli";
$db['local_dss_db']['dbprefix'] = "";
$db['local_dss_db']['pconnect'] = TRUE;
$db['local_dss_db']['db_debug'] = TRUE;
$db['local_dss_db']['cache_on'] = FALSE;
$db['local_dss_db']['cachedir'] = "cache";
$db['local_dss_db']['char_set'] = "utf8";
$db['local_dss_db']['dbcollat'] = "utf8_general_ci";

I got this error instead. So, clearly it didn't inherited the connection.

Code:
Unable to connect to your database server using the provided settings
Filename: D:\xampp\htdocs\controller\sys\database\DB_driver.php
Line Number: 124


I'm not sure if my assumption above is correct or is there another way to do this. I wish I could make it work for I need to use GroceryCRUD that relies on CI native database library. I'm open to any other way as long as I will be able to use both GC and CI.

Any help, suggestions, references are much appreciated.


-thanks


Reference to other blog related to this:
http://ellislab.com/forums/viewthread/237873/#1058767
http://ellislab.com/forums/viewthread/138094/#681151
http://ellislab.com/forums/viewthread/93924/#475457
#2

[eluser]Unknown[/eluser]
phpseclib lets you do SSH but you can't tunnel SQL through SSH without either writing your own pure PHP SQL client or modifying mysql(i)_connect.

More info:

http://grokbase.com/t/php/php-internals/...li-connect
#3

[eluser]clustersblue[/eluser]
allisdust,

I'll take your post as my lead... Thanks for sharing... I'll post my solution as soon as I have it.
#4

[eluser]clustersblue[/eluser]
I was not able to successfully implement this settings, so we decided to re-configure our server to not to have bind-address in my.cnf file. Still working to make it work.
#5

[eluser]CroNiX[/eluser]
What is the reason for needing to use ssh? That will be very slow. It can take a few seconds to establish a connection.

Have you tried using 'localhost' as the db server once you've established a ssh connection, since you're actually "on" the remote server at that point?

Is mysql set up to allow connections from your IP?




Theme © iAndrew 2016 - Forum software by © MyBB