Codeigniter database connection over SSH

Codeigniter database connection over SSH - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Codeigniter database connection over SSH (/showthread.php?tid=60867)

Codeigniter database connection over SSH - El Forum - 07-16-2014

[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

Codeigniter database connection over SSH - El Forum - 07-16-2014

[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/133ds3c53r/idea-add-wrapper-support-to-mysqli-connect

Codeigniter database connection over SSH - El Forum - 07-18-2014

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

Codeigniter database connection over SSH - El Forum - 08-04-2014

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

Codeigniter database connection over SSH - El Forum - 08-05-2014

[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?