|can a remote mySQL talk to a local SQL Server?|
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
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:
$dbh = DBI->connect(DBI:mysql:db_name", "login","pass");
$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.)
the same goes for php as well
resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )
$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.
Would such a setup be safe, or is the username/password being passed in the open?
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
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.
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....
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.