I'm trying to find a way to get remote connections from my Linux Desktop machine to a remote Linux box which is hosting a MySQL database, via SSH port 22, and then once connection is established, log in to the database port 3306 and have either an SSH and or SCP connection established so I can securely do edits and queries.
I am able to connect this way from my Windows machine to remote server using Navicat. Both the windows desktop and the server are set up this way. I just can't figure out how to get my Linux desktop and the remote Linux box to talk this way.
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
I tried connecting to the remote database in the same way I use Konqueror for FTP over SSH, i.e., by connecting via the fish protocol over port 22 -by enterning into OpenOffice Base for the server URL: fish://user@myservername.com:22 and the error that comes up in either OO Base or Calc said I must specify a port number after the : , which I did, and it still won't connect that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
_________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
On Mon, Mar 10, 2008 at 7:50 AM, Therese Trudeau mswotr@hotmail.com wrote:
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
_________________________________________________________________ Helping your favorite cause is as easy as instant messaging. You IM, we give. http://im.live.com/Messenger/IM/Home/?source=text_hotmail_join
On Mon, Mar 10, 2008 at 09:18:55AM -0400, Therese Trudeau enlightened us:
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
Matt
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username@domain.name.of.RemoteServer.
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect. _________________________________________________________________ Need to know the score, the latest news, or you need your Hotmail®-get your "fix". http://www.msnmobilefix.com/Default.aspx
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username@domain.name.of.RemoteServer.
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
Matt
----------------------------------------
Date: Mon, 10 Mar 2008 13:27:28 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need?
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username@domain.name.of.RemoteServer.
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
OK so you are saying to enter into Terminal, either: ssh -L 3307:localhost:3306 remote.db.server , OR enter into Terminal: ssh -L 3307:remote.db.server:3306 remote.db.server and substitute in my remote server address for "remote.db.server"
Once I do that, what should I enter into the OpenOffice Calc Spreadsheet Application Dialog box, in order to get the OpenOffice Calc local applicationwhich resides on my desktop machine to read the remote database?
_________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
On Mon, Mar 10, 2008 at 03:08:19PM -0400, Therese Trudeau enlightened us:
Date: Mon, 10 Mar 2008 13:27:28 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
> I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way.
> My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need? >
No, but what you could do is use an ssh tunnel to accomplish this. Something along the lines of "ssh -L 3307:database.example.net:3306 database.example.net"
This will tell your system to tunnel the local port 3307 through the database box, TO the database box on port 3306. From this point, after you connect via ssh with the above command, you can simply point your database software to the local 3307 port in plain text, and it'll travel over ssh to the remote box.
I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username@domain.name.of.RemoteServer.
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
OK so you are saying to enter into Terminal, either: ssh -L 3307:localhost:3306 remote.db.server , OR enter into Terminal: ssh -L 3307:remote.db.server:3306 remote.db.server and substitute in my remote server address for "remote.db.server"
Once I do that, what should I enter into the OpenOffice Calc Spreadsheet Application Dialog box, in order to get the OpenOffice Calc local applicationwhich resides on my desktop machine to read the remote database?
Correct, that *should* work.
Matt
----------------------------------------
Date: Mon, 10 Mar 2008 15:12:31 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 03:08:19PM -0400, Therese Trudeau enlightened us:
Date: Mon, 10 Mar 2008 13:27:28 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
>> I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way. > >> My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need? >> > > No, but what you could do is use an ssh tunnel to accomplish this. > Something along the lines of "ssh -L 3307:database.example.net:3306 > database.example.net" > > This will tell your system to tunnel the local port 3307 through the > database box, TO the database box on port 3306. From this point, after > you connect via ssh with the above command, you can simply point your > database software to the local 3307 port in plain text, and it'll > travel over ssh to the remote box. > > I've probably done a bad job explaining this, so I'll go get some more coffee.
Hehe THANKS Jim I need some too!
So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base?
Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase?
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username@domain.name.of.RemoteServer.
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
OK so you are saying to enter into Terminal, either: ssh -L 3307:localhost:3306 remote.db.server , OR enter into Terminal: ssh -L 3307:remote.db.server:3306 remote.db.server and substitute in my remote server address for "remote.db.server"
Once I do that, what should I enter into the OpenOffice Calc Spreadsheet Application Dialog box, in order to get the OpenOffice Calc local applicationwhich resides on my desktop machine to read the remote database?
Correct, that *should* work.
Sadly I can't get OpenOffice Calc to connect that way, I keep getting those “Illegal Connection Port Value 3307:3307” errors from Calc when I enter in “localhost:3307” into Calc open new database box. _________________________________________________________________ Shed those extra pounds with MSN and The Biggest Loser! http://biggestloser.msn.com/
----------------------------------------
From: mswotr@hotmail.com To: centos@centos.org Subject: RE: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible? Date: Mon, 10 Mar 2008 16:33:49 -0400
Date: Mon, 10 Mar 2008 15:12:31 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 03:08:19PM -0400, Therese Trudeau enlightened us:
Date: Mon, 10 Mar 2008 13:27:28 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
>>> I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way. >> >>> My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need? >>> >> >> No, but what you could do is use an ssh tunnel to accomplish this. >> Something along the lines of "ssh -L 3307:database.example.net:3306 >> database.example.net" >> >> This will tell your system to tunnel the local port 3307 through the >> database box, TO the database box on port 3306. From this point, after >> you connect via ssh with the above command, you can simply point your >> database software to the local 3307 port in plain text, and it'll >> travel over ssh to the remote box. >> >> I've probably done a bad job explaining this, so I'll go get some more coffee. > > Hehe THANKS Jim I need some too! > > So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), > then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base? > > Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase? >
You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username@domain.name.of.RemoteServer.
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
OK so you are saying to enter into Terminal, either: ssh -L 3307:localhost:3306 remote.db.server , OR enter into Terminal: ssh -L 3307:remote.db.server:3306 remote.db.server and substitute in my remote server address for "remote.db.server"
Once I do that, what should I enter into the OpenOffice Calc Spreadsheet Application Dialog box, in order to get the OpenOffice Calc local applicationwhich resides on my desktop machine to read the remote database?
Correct, that *should* work.
Sadly I can't get OpenOffice Calc to connect that way, I keep getting those “Illegal Connection Port Value 3307:3307” errors from Calc when I enter in “localhost:3307” into Calc open new database box.
I'm wondering if it's related to this problem - I was able yesterday to get OpenOffice 2.3 Calc to connect un securely just by using the default port 3306 – just did that as a one time test.
The problem I had when I did that though, was that when I open up a large table with 400 rows and maybe 25 columns in it (a table for products in an on line shopping cart), the scroll bars move through the table very slowly, and after a minute or two the table freezes up, as does OpenOffice, and I end up having to CtlAltDel and restart Open office.
From a different desktop machine running Windows 2000 however, I am able to connect to the very same remote database using Navicat via first tunneling through port 22 SSH and then connecting to port 3306 via the SSH tunnel, and I don't have this problem, I can scroll through and edit data very fast with no crashing. I'm wondering if the problem is with the way I have my Linux desktop configured?
_________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
Therese Trudeau wrote:
From: mswotr@hotmail.com To: centos@centos.org Subject: RE: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible? Date: Mon, 10 Mar 2008 16:33:49 -0400
Please edit your quotes, no need to quote the entire thread back in every email - just trim down to the required bits.
Please edit your quotes, no need to quote the entire thread back in every email - just trim down to the required bits.
OK Sorry :)
_________________________________________________________________ Climb to the top of the charts! Play the word scramble challenge with star power. http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan
on 3-10-2008 1:33 PM Therese Trudeau spake the following:
Date: Mon, 10 Mar 2008 15:12:31 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 03:08:19PM -0400, Therese Trudeau enlightened us:
Date: Mon, 10 Mar 2008 13:27:28 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
>>> I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way. >>> My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need? >>> >> No, but what you could do is use an ssh tunnel to accomplish this. >> Something along the lines of "ssh -L 3307:database.example.net:3306 >> database.example.net" >> >> This will tell your system to tunnel the local port 3307 through the >> database box, TO the database box on port 3306. From this point, after >> you connect via ssh with the above command, you can simply point your >> database software to the local 3307 port in plain text, and it'll >> travel over ssh to the remote box. >> >> I've probably done a bad job explaining this, so I'll go get some more coffee. > Hehe THANKS Jim I need some too! > > So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), > then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base? > > Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase? > You should enter localhost:3307. That connects to the local end of your SSH tunnel.
OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username-d7gQ9grfmP7uf9qcbqpnZGC4GVmHw8X4ZSToKVHwfNc@public.gmane.org
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
OK so you are saying to enter into Terminal, either: ssh -L 3307:localhost:3306 remote.db.server , OR enter into Terminal: ssh -L 3307:remote.db.server:3306 remote.db.server and substitute in my remote server address for "remote.db.server"
Once I do that, what should I enter into the OpenOffice Calc Spreadsheet Application Dialog box, in order to get the OpenOffice Calc local applicationwhich resides on my desktop machine to read the remote database?
Correct, that *should* work.
Sadly I can't get OpenOffice Calc to connect that way, I keep getting those �Illegal Connection Port Value 3307:3307� errors from Calc when I enter in �localhost:3307� into Calc open new database box. _________________________________________________________________
If there is a separate port dialog box, don't put the port in twice. IE... In serverurl put localhost and in port put 3307.
----------------------------------------
To: centos@centos.org From: ssilva@sgvwater.com Date: Mon, 10 Mar 2008 13:43:00 -0700 Subject: [CentOS] Re: SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
on 3-10-2008 1:33 PM Therese Trudeau spake the following:
Date: Mon, 10 Mar 2008 15:12:31 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 03:08:19PM -0400, Therese Trudeau enlightened us:
Date: Mon, 10 Mar 2008 13:27:28 -0400 From: hyclak@math.ohiou.edu To: centos@centos.org Subject: Re: [CentOS] SSH, SCP connections to remote MySQL Database using OpenOffice Possible?
On Mon, Mar 10, 2008 at 12:38:34PM -0400, Therese Trudeau enlightened us:
>>>> I can connect to the remote database via Linux desktop, it's just via an un encrypted connection through port 3306. For obvious reasons I don't want to do it that way. >>>> My question is, is OpenOffice Calc able to connect to remote databases via SSH and or SCP? If so, do I need a plug in for that, or need to configure calc to do that in some way? How would I do this, or which plug in(s) do I need? >>>> >>> No, but what you could do is use an ssh tunnel to accomplish this. >>> Something along the lines of "ssh -L 3307:database.example.net:3306 >>> database.example.net" >>> >>> This will tell your system to tunnel the local port 3307 through the >>> database box, TO the database box on port 3306. From this point, after >>> you connect via ssh with the above command, you can simply point your >>> database software to the local 3307 port in plain text, and it'll >>> travel over ssh to the remote box. >>> >>> I've probably done a bad job explaining this, so I'll go get some more coffee. >> Hehe THANKS Jim I need some too! >> >> So when I "ssh -L 3307:database.example.net:3306", am I doing this via terminal prior to connecting with Calc (or base), >> then start up base and enter in just the regular database.example.net into the "Server URL" field in Clac or base? >> >> Or do I need to enter "ssh -L 3307:database.example.net:3306" just once in to the "Server URL" FIeld from within OpenOffice Calc or VBase? >> > You should enter localhost:3307. That connects to the local end of your SSH > tunnel. OK this is what I tried - I opened up terminal on my local desktop machine, and I issued the command: ssh -L 3307:domain.name.of.DesktopLocalMySQLserver:3306 username-d7gQ9grfmP7uf9qcbqpnZGC4GVmHw8X4ZSToKVHwfNc@public.gmane.org
Then I started up OpenOffice Calc, clicked on File>New>Database, and in the area where it asks for the "ServerURL", I entered in: localhost:3307 and just below it where it asks for the port number I also entered in 3307 (the default is 3306).
The error I got was "Illegal Connection Port Value 3307:3307: and I could not connect.
I think what you really want is
ssh -L 3307:localhost:3306 remote.db.server
The host in the middle is relative to the end machine. You could also probably do
ssh -L 3307:remote.db.server:3306 remote.db.server
(for that matter, I don't see why you couldn't do ssh -L 3306:... as long as you don't have mysql running on your desktop)
OK so you are saying to enter into Terminal, either: ssh -L 3307:localhost:3306 remote.db.server , OR enter into Terminal: ssh -L 3307:remote.db.server:3306 remote.db.server and substitute in my remote server address for "remote.db.server"
Once I do that, what should I enter into the OpenOffice Calc Spreadsheet Application Dialog box, in order to get the OpenOffice Calc local applicationwhich resides on my desktop machine to read the remote database?
Correct, that *should* work.
Sadly I can't get OpenOffice Calc to connect that way, I keep getting those �Illegal Connection Port Value 3307:3307� errors from Calc when I enter in �localhost:3307� into Calc open new database box. _________________________________________________________________
If there is a separate port dialog box, don't put the port in twice. IE... In serverurl put localhost and in port put 3307.
OK Some progress anyway, by not putting in the port twice I got a connecrtion. But as before, when I connected un securely, OpenOffice freezes up and crashes after about one minute, whether or not it's through port 3306, or through 3307 & SSH. _________________________________________________________________ Shed those extra pounds with MSN and The Biggest Loser! http://biggestloser.msn.com/
On Mon, Mar 10, 2008 at 05:12:46PM -0400, Therese Trudeau enlightened us:
OK Some progress anyway, by not putting in the port twice I got a connecrtion. But as before, when I connected un securely, OpenOffice freezes up and crashes after about one minute, whether or not it's through port 3306, or through 3307 & SSH.
Sounds like an OOo problem, then. When I briefly tried testing it, there was a couple of driver options to connect to MySQL, you might try using the one you're not (IIRC one was ODBC and the other was JDBC).
Matt
Sounds like an OOo problem, then. When I briefly tried testing it, there was a couple of driver options to connect to MySQL, you might try using the one you're not (IIRC one was ODBC and the other was JDBC).
I'm using both the Java Runtime Environment jre1.6.0_05 and the sql-connector-java-5.0.8 - they both tested out OK after install. _________________________________________________________________ Shed those extra pounds with MSN and The Biggest Loser! http://biggestloser.msn.com/
Sounds like an OOo problem, then. When I briefly tried testing it, there was a couple of driver options to connect to MySQL, you might try using the one you're not (IIRC one was ODBC and the other was JDBC).
Ah HA! That might be the problem, I'm using the JDBC driver and I don't have the ODBC driver installed. Do you know where i could download the ODBC driver?
Or, is the ODBC driver the same as the sql-connector-java-5.0.8 driver? That one I do have; I set up OpenOffice to point to that one too as I did for the JDBC Driver.
_________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
On Tue, Mar 11, 2008 at 10:33:37AM -0400, Therese Trudeau enlightened us:
Sounds like an OOo problem, then. When I briefly tried testing it, there was a couple of driver options to connect to MySQL, you might try using the one you're not (IIRC one was ODBC and the other was JDBC).
Ah HA! That might be the problem, I'm using the JDBC driver and I don't have the ODBC driver installed. Do you know where i could download the ODBC driver?
Or, is the ODBC driver the same as the sql-connector-java-5.0.8 driver? That one I do have; I set up OpenOffice to point to that one too as I did for the JDBC Driver.
In a phrase: I have no idea :-)
It was just something I noticed when messing around with it. I've never actually had a need to hook OOo to a database. Asking on a OOo list/forum might be more fruitful.
Matt
On Tue, Mar 11, 2008 at 10:33 AM, Therese Trudeau mswotr@hotmail.com wrote:
Ah HA! That might be the problem, I'm using the JDBC driver and I don't have the ODBC driver installed. Do you know where i could download the ODBC driver?
yum search odbc
What you're looking for is the unixODBC package. In CentOS it includes an ODBC driver for MySQL as well.
Or, is the ODBC driver the same as the sql-connector-java-5.0.8 driver? That one I do have; I set up OpenOffice to point to that one too as I did for the JDBC Driver.
No, anything with "java" in the name is JDBC, not ODBC.
Google is your friend as well: http://www.google.ca/search?q=openoffice+odbc+linux
The first one is a tutorial on how to connect OOo to MySQL, quite old though. The second one is an article in Linux.com with some information on JDBC vs. ODBC.
In general, however, I agree with Matt that your problem is OOo related and asking on an OOo list would be more fruitful.
Filipe
yum search odbc
What you're looking for is the unixODBC package. In CentOS it includes an ODBC driver for MySQL as well.
Or, is the ODBC driver the same as the sql-connector-java-5.0.8 driver? That one I do have; I set up OpenOffice to point to that one too as I did for the JDBC Driver.
No, anything with "java" in the name is JDBC, not ODBC.
Google is your friend as well: http://www.google.ca/search?q=openoffice+odbc+linux
The first one is a tutorial on how to connect OOo to MySQL, quite old though. The second one is an article in Linux.com with some information on JDBC vs. ODBC.
In general, however, I agree with Matt that your problem is OOo related and asking on an OOo list would be more fruitful.
Filipe
Now why didn't I think of that?!? I will and if I find a solution I'll post it on this thread thanks. _________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
On Tue, Mar 11, 2008 at 11:51 AM, Therese Trudeau mswotr@hotmail.com wrote:
Now why didn't I think of that?!? I will and if I find a solution I'll post it on this thread thanks.
I'm ignoring the OT bits of this because for some reason this made me curious. Here are the findings. It *is* possible to do this with the OOo Calc in centos, but it takes a bit of muscle to get working.
The default ODBC drivers from the unixODBC package don't work. Period.
The jdbc driver will work, however it doesn't come by default, and seems to require Sun java (easiest by installing the jdk rpm from java.sun.com, then installing the sun-compat-java package from jpackage.org)
You can get the mysql-connector-java-5.0.8-1jpp package from jpackage.org, and this is the easy part. This package requires log4j, which is in the centos base repo so you'll need to install this as well. You'll also need jta, which unfortunately is non-free, and only available as an srpm from jpackage ( in the 1.6 non-free tree. I haven't seen it appear yet in 1.7).
This next bit assumes some minimal familiarity with building rpms. You'll have to change the cvs_version string from 1_0_1_B to 1_1 in the jta spec file, and download the jta class package from http://java.sun.com/products/jta/
I didn't bother to fix the documentation related portion of the spec, but rather cheated and built the rpm with '--without javadoc'
After you have sun java, jta, log4j, and the mysql-connector-java package installed, log out & log in again. This should put the sun java bits into your openoffice discovered path. We'll see here in a minute anyway.
Open up OOo calc, and go to Tools, then Options. Select the java menu item on the left. Make sure the sun java package appears in the list. Make sure the box at the top for 'use java' is checked, and make sure sun java is selected. Then choose 'Class Path', then 'Add archive'. Add the path '/usr/share/java/mysql-connector-java.jar'
Now save and exit. It'll tell you that you have to restart OOo Calc, and that's BS. You actually have to log out and log back in again for some insane reason I have yet to discover. This time around you should actually be able to connect to your database by selecting MySQL from the list, and jdbc as the connection type. This works with and without the ssh tunnel in testing here.
On Tue, 2008-03-11 at 22:24 -0400, Jim Perrin wrote:
On Tue, Mar 11, 2008 at 11:51 AM, Therese Trudeau mswotr@hotmail.com wrote:
Now why didn't I think of that?!? I will and if I find a solution I'll post it on this thread thanks.
I'm ignoring the OT bits of this because for some reason this made me curious. Here are the findings. It *is* possible to do this with the OOo Calc in centos, but it takes a bit of muscle to get working.
The default ODBC drivers from the unixODBC package don't work. Period.
The jdbc driver will work, however it doesn't come by default, and seems to require Sun java (easiest by installing the jdk rpm from java.sun.com, then installing the sun-compat-java package from jpackage.org)
You can get the mysql-connector-java-5.0.8-1jpp package from jpackage.org, and this is the easy part. This package requires log4j, which is in the centos base repo so you'll need to install this as well. You'll also need jta, which unfortunately is non-free, and only available as an srpm from jpackage ( in the 1.6 non-free tree. I haven't seen it appear yet in 1.7).
This next bit assumes some minimal familiarity with building rpms. You'll have to change the cvs_version string from 1_0_1_B to 1_1 in the jta spec file, and download the jta class package from http://java.sun.com/products/jta/
I didn't bother to fix the documentation related portion of the spec, but rather cheated and built the rpm with '--without javadoc'
After you have sun java, jta, log4j, and the mysql-connector-java package installed, log out & log in again. This should put the sun java bits into your openoffice discovered path. We'll see here in a minute anyway.
Open up OOo calc, and go to Tools, then Options. Select the java menu item on the left. Make sure the sun java package appears in the list. Make sure the box at the top for 'use java' is checked, and make sure sun java is selected. Then choose 'Class Path', then 'Add archive'. Add the path '/usr/share/java/mysql-connector-java.jar'
Now save and exit. It'll tell you that you have to restart OOo Calc, and that's BS. You actually have to log out and log back in again for some insane reason I have yet to discover. This time around you should actually be able to connect to your database by selecting MySQL from the list, and jdbc as the connection type. This works with and without the ssh tunnel in testing here.
---- for the record...I used the default OODBC drivers for postgres on both CentOS 4 and now Cent OS 5 but the postgres db I use is localhost. I mostly use the ODBC drivers from my Fedora desktop (not localhost obviously) and they work fine.
so when you state "The default ODBC drivers from the unixODBC package don't work. Period." I have to say that in my limited experience, they do indeed work with postgres. I don't generally use MySQL for anything other than special purpose stuff (horde/sqlgrey) and have little interest in reporting from them.
Craig
On Wed, Mar 12, 2008 at 12:37 AM, Craig White craigwhite@azapple.com wrote:
for the record...I used the default OODBC drivers for postgres on both CentOS 4 and now Cent OS 5 but the postgres db I use is localhost. I mostly use the ODBC drivers from my Fedora desktop (not localhost obviously) and they work fine.
so when you state "The default ODBC drivers from the unixODBC package don't work. Period." I have to say that in my limited experience, they do indeed work with postgres. I don't generally use MySQL for anything other than special purpose stuff (horde/sqlgrey) and have little interest in reporting from them.
Yeah, when I sent the email I was letting personal emotion get in my way because it was frustrating me. I realized shortly before falling asleep that I'd been doing this on my workstation, which has been around since god-knows-when, and has god-knows-what left on it. I think I'll stand up a test vm or second box with just the unixODBC bits and no other java remnants and retest with mysql. Hopefully this time I'll manage to keep my yap shut until I've figured it all out :-P
It *is* possible to do this with the OOo Calc in centos, but it takes a bit of muscle to get working... You'll also need jta, which unfortunately is non-free, and only available as an srpm from jpackage ( in the 1.6 non-free tree. I haven't seen it appear yet in 1.7).
You talking about two pieces of the Java Transaction API (JTA) at: http://java.sun.com/products/jta/index.html and http://jpackage.org/ Where is the 1.6 non free tree @ jpackage? I Could not find it. Is there any documentation for JTA install on jpackage.org site?
...then installing the sun-compat-java package from jpackage.org)
Where on http://jpackage.org/ is info/download for sun-compat-java? Could not find it on either the 1.6 or 1.7 tree.
This next bit assumes some minimal familiarity with building rpms. You'll have to change the cvs_version string from 1_0_1_B to 1_1 in the jta spec file, and download the jta class package from http://java.sun.com/products/jta/ I didn't bother to fix the documentation related portion of the spec, but rather cheated and built the rpm with '--without javadoc'
If you or someone could expand on this a bit, maybe procedural cli steps then I think I could take it from there.
I started this same thread over on a major OpenOffice Mail list & did not get any new info on this SSH database connect issue. Guess I'm pretty much out of luck unless I can solve this via info from this forum. _________________________________________________________________ Connect and share in new ways with Windows Live. http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008
On Wed, Mar 12, 2008 at 8:58 AM, Therese Trudeau mswotr@hotmail.com wrote:
Therese, I'm taking this off-list, so please see the other email.
-
Craig White -
Filipe Brandenburger -
Jim Perrin -
Karanbir Singh -
Matt Hyclak -
Scott Silva -
Therese Trudeau