Help connecting to a remote MySQL server...

[chicken]

This situation is a bit difficult to explain, but basically I am trying to connect to a separately hosted MySQL server from a Windows server but I'm running into difficulty getting the connection string right.

• Server 1: Where the main page resides, this server normally uses MSSQL
  I have FTP access and Remote Desktop access to this server but no installation rights for the ODBC driver.
  -
• Server 2: Secondary site with a MySQL database containing some info I need to reference
  I have FTP and normal web-based control over this one with full admin rights right up to the reseller account above the site-admin.

I make pages for Server 1 on a regular basis, but now I need to access some info from Server 2's SQL database for a page on Server 1.

Server 2 is set up to allow remote SQL access to Server 1's IP (I have tested this using my IP and MySQL Query Browser to connect and it works).

My problem seems to be the connection string. I can't seem to get it right because either Server 1 doesn't have the MySQL ODBC driver installed or Server 2 isn't responding to the only requests Server 1 seems to work with because they're for a MSSQL connection.

This seemed to be the most promising:

Provider=MS Remote;
Remote Provider=sqloledb;
Data Source=webxx.extendcp.co.uk;
Server=webxx.extendcp.co.uk;
Port=3306;
Database=webxx-ppd;
Uid=xxxxxx;
Pwd=xxxxxxx;

But brought back:

RDSServer.DataFactory error '80070005'

RDS Server Error: The server has denied access to the default RDS Handler used to access this page. See the Server Administrator for more information about server security settings.

Give me actual SQL to do and I'm fine but I'm not too hot on the details of the connections so any suggestions would be very helpful!

[aidanjt]

You need the MySQL ODBC driver installed on server 1, you also might check if server 1 is accepting connections from server 2 with a mysql client or GUI tools on that machine.

[TheAnimus]

Whats the app/platform your trying too connect from?

also remeber that mysql is an utter waste of space of a server, and like clamidia is spreading vastly un-controlled and too far with the 'less aware' generations. It also suddenly becomes insainly expensive when you get into really big DB teritory. Its dog slow, and barely has any features. I can't stand it. There are so many better FREE and free to use packages on the market. Why the hell use it?

It just fails on all grounds.

[ajbrun]

It's late and I'm tired, so forgive me if I misunderstand .

Try this test your connection info and get a correct connection string at the end of it:

* Create a new file somewhere with a .udl extension, then open it.
* Select the appropriate DB connection in the providers tab.
* Enter your server information, username and password in the connection tab.
* Select the database from the list.
* Click test connection. Assuming your information is correct, it should connect fine.
* Click OK.
* Open your .udl file in wordpad/notepad/similar. This should show you your connection string.

[aidanjt]

Originally Posted by TheAnimus

Whats the app/platform your trying too connect from?

also remeber that mysql is an utter waste of space of a server, and like clamidia is spreading vastly un-controlled and too far with the 'less aware' generations. It also suddenly becomes insainly expensive when you get into really big DB teritory. Its dog slow, and barely has any features. I can't stand it. There are so many better FREE and free to use packages on the market. Why the hell use it?

It just fails on all grounds.

That is utter clap.. O'Reilly media, Ticketmaster, Redhat, Forrester Research, Google, etc, all have really tiny databases I'm sure. And few sites are faster than Google, too.

[TheAnimus]

yes because google use mysql. Only they don't.

They use a highly modified version, for only some tasks. So that means it must be the dogs bollocks dosen't it?

Ticketmaster site is hardly a good example of a well run smooth fast website is it?

If the best adovacy you can come up with is someones using it? how do you know which is the best, all vendor DBs are used, even column store and datawarehouse packages, that dosen't mean there good.

[aidanjt]

Originally Posted by TheAnimus

yes because google use mysql. Only they don't.

They use a highly modified version, for only some tasks. So that means it must be the dogs bollocks dosen't it?

No, they write tools to work with the engine, they don't have a 'highly modified version', nor do they need to. And they use it a lot.

Originally Posted by TheAnimus

Ticketmaster site is hardly a good example of a well run smooth fast website is it?

Works fine for me, graphics are a tad slow to load, everything else is fine, not that it has anything to do with the database, unless they load all their graphics from database, which they don't. Their site hosts a lot of data, it's another example of why your sweaping statement is false.

Originally Posted by TheAnimus

If the best adovacy you can come up with is someones using it? how do you know which is the best, all vendor DBs are used, even column store and datawarehouse packages, that dosen't mean there good.

Nice strawman there, very good. I didn't give arbitrary names of companies that use it, I gave names of big companies with very big data storage needs for their sites.

[b0redom]

Originally Posted by aidanjt

That is utter clap.. O'Reilly media, Ticketmaster, Redhat, Forrester Research, Google, etc, all have really tiny databases I'm sure. And few sites are faster than Google, too.

and Yahoo...

[b0redom]

Anyway, returning to the matter at hand....

How are you trying to connect? PHP?

[chicken]

Wow, sorry to have started a DB Server fight! Heheh! Thanks for all the replies anyway guys!

In reply to some of the questions:

I'm using ASP and don't have a choice of PHP.

I'd love to install the ODBC driver but I can't install anything from that login. I have tried to do it manually but although the driver registering exe reported a success it doesn't work, I think this is to do with the files being outside the default folder, or possibly it just needs a reboot (which I can't do). I'd ask the host for help but they barely help us at the best of times and currently there are some high-up politics going on regarding that that I really don't want to get involved in.

I know the MySQL server accepts remote connections from predefined IP addresses as I have tested it from my own PC.

I just thought it would be fairly straight forward to do this, I guess not.
Maybe I should just leave it for now and get looking into better hosting... Has anyone here used Rackspace?



(Going to investigate ajbrun's idea before I totally give in though...)

[TheAnimus]

If the ODBC driver dosen't exist on the box, and you don't have rights to install it... your not going to have a nice time.