MySQL question for Web Devs

[anxious_ant]

I’m trying to pull data from remote MySQL server into SQL. The first stage is successful as I’ve managed to use. MySQL ODBC to connect.

However I’m unable to open the connection to view the data in SMSS using Linked Server option.

From the error message it looks like remote connection needs to be enabled. Found something in StackOverflow about this so forwarded it to the person managing all this. Basically involves some changes to the MySQL config file.

He came back and said it’s not possible to do that as it will open up remote connection for everyone that knows the IP, without need for authentication.

Is this true?

[xeny]

Has the remote server not got a firewall so it can restrict MySQL access to just your server's IP ?

[anxious_ant]

xeny said:

Has the remote server not got a firewall so it can restrict MySQL access to just your server's IP ?

He was asking for my public IP so I believe there must be a firewall restriction by IP. It’s Azure server so bit tricky getting the IP but the ODBC connector helpfully shows that in the error message.

From his last email I think he was trying to say if he turns on remote connection on MySQL it will allow anyone to remote in without authentication.

I’m provided by a login with password which I’ve used in the ODBC connector and it connects. However to use linked server in SQL I also need remote connection on server side. Unless I’m mistaken ...

In summary I’m trying to connect using Linked Server in SQL to a remote MySQL db.

[mattley]

anxious_ant said:

He came back and said it’s not possible to do that as it will open up remote connection for everyone that knows the IP, without need for authentication.

Is this true?

I can't say for sure that the authentication bit is right, but regardless you are asking them to expose the database directly and without providing a fixed IP no is the correct answer.

[anxious_ant]

mattley said:

I can't say for sure that the authentication bit is right, but regardless you are asking them to expose the database directly and without providing a fixed IP no is the correct answer.

Cheers I've convinced him to create a firewall restriction.

Future note for anyone trying to connect to mySQL DB from SQL DB via Linked Server:
1) Check the version of driver used 32/64 bit. It must match the server
2) There's a high chance that the Schema won't be available due to version and setup on mySQL. Use openquery instead to query the data
3)mySQL Workbench is immensely helpful

Bit of a learning curve but got there in the end