homepage Welcome to WebmasterWorld Guest from 54.196.136.119
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / WebmasterWorld / Webmaster General
Forum Library, Charter, Moderators: phranque & physics

Webmaster General Forum

    
can a remote mySQL talk to a local SQL Server?
andyd273




msg:392138
 9:05 pm on Jun 15, 2005 (gmt 0)

our web hosting company has mySQL running on their website host (running apache) and our intranet has a SQL Server running on it.

What I would like to do is figure out someway that the two SQL databases can talk to eachother, so that I dont have to export the entire database and figure out all of the continuity issues that it would involve to make that work.

sadly, I dont even know enough about this to even know what question to ask, so if anyone has any ideas as to how I can make the local database stuff accessable from the web, that would be cool

 

rocknbil




msg:392139
 11:25 pm on Jun 15, 2005 (gmt 0)

YES, you can connect two mysql DB's, but not sure about the sql server, esp. if it's a windblows server (sorry, didn't see that part at first.) But you should be able to.

Check the documentation, this perl snippet is typed from memory so may be inaccurate but you get the idea:

local:

$dbh = DBI->connect(DBI:mysql:db_name", "login","pass");

remote:

$dbh = DBI->connect(DBI:mysql:db_name:remote_address", "login","pass");

Remote address can be a web address, IP, or computer name if it's on your internal network (but still remote.)

You would be wise to create a remote USER different than your local user, and assign only the permissions you need ot thart user (via grant statement.)

jatar_k




msg:392140
 11:37 pm on Jun 15, 2005 (gmt 0)

the same goes for php as well

from
[ca.php.net...]

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )

so

$link = mysql_connect('server.name.com', 'mysql_user', 'mysql_password');

you would need to able to connect to the one you are considering remote. If the code is running on the web site then your intranet server would be the remote. You would need to allow connections to your local server.

roldar




msg:392141
 12:13 am on Jun 16, 2005 (gmt 0)

Would such a setup be safe, or is the username/password being passed in the open?

jatar_k




msg:392142
 3:40 am on Jun 16, 2005 (gmt 0)

no, not in the open, normally you throw your user/pass in a file that is above the root of the site and then include the file and use the vars in the mysql_connect

Romeo




msg:392143
 1:13 pm on Jun 16, 2005 (gmt 0)

You probably can't let those databases talk directly, but you would need some application logic to do so, perhaps the remote application updating both databases simultaneously. Since I have never worked with an SQL-server, I don't know what protocol funtions whould be needed on your remote server, or if the SQL-Server can talk a common protocol like ODBC.

Some more points to think about:

-- To connect to a database from the outside of a box (on small systems it works inside: the application calls the database system locally within the same box), the database services ports have to be exposed to the outside of the box, which may -- depending on the environment (trusted local network versus the Internet) -- be a security risk to do so (risk of of being exploited by remote attackers, risk of the data stream being sniffed, including the session start with passwords probably sent in the clear).

-- Between your Intranet and the Internet may be a firewall to prevent connections from the outside into your intranet. To allow traffic from the outside into your intranet, your firewall admin whould have to do a NAT address translation for incoming connects to your intranet server and to poke more holes into the firewall for the SQL-server service ports involved (and he might perhaps be very unhappy to do this ...).

A circumvention, to lessen some of the above mentioned security exposures, could be to establish an ssh tunnel between the boxes (and through the firewall), and then let the databases/applications talk through that ssh tunnel. The ssh could be configured with appropriate keys on both sides and with user/password-login disabled ...

You should talk to your security folks about what exactly could be done in your situation, if your local server could be moved into a DMZ, if you could use a proxy within the DMZ, etc.

However, I won't do that. If the outer box, which is not even under your own control, would once be taken and owned by malicious attackers, your intranet is on risk.

Regards,
R.

dataguy




msg:392144
 3:58 pm on Jun 17, 2005 (gmt 0)

We use SQL Server for our database master, and MySQL on several web servers as slaves, which sounds like what you are doing. After a lot of work trying to connect them directly, we gave up and created a syncronizer in VB that pushes data updates from SQL Server to MySQL every 5 minutes.

It's not as complicated as it may sound, and it has worked flawlessly for months now. It allows us to take advantage of the best features of both products.

Another advantage is that we can use it to keep databases sync'ed across the net, so we can have multiple web hosts with duplicate copies of our web sites and databases, and the databases stay in sync.

Maybe this is what you should do....

andyd273




msg:392145
 5:05 pm on Jun 21, 2005 (gmt 0)

hmm, this has given me a lot of good things to think about. I do like that idea Dataguy. my vb database programming skills are a lil rusty, but I'm sure it shouldnt be to difficult to sync the 2. the real brain twister there is figuring out how to resolve database conflicts on the local side if the online side gets updated... always something to learn.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / WebmasterWorld / Webmaster General
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved