Connecting MSSQL Server to MySQL Server

[tommythejoat]

We have a fairly large cPanel/WHM system at our co-location center that serves the public with several web sites using Joomla, vBulletin and other open source systems.

Our enterprise system is on our main campus and the two systems have a dedicated VPN connection between them. We have a subnet of 10.100.100.nnn on the main campus and 10.200.200.nnn at the co-location center.

Several applications including vBulletin need access to information on the enterprise mssql system. I got the ODBC driver from MySQL to connect the two database systems together.

The problem I have run into is that I cannot get the ODBC driver to connect to the MySQL system. The ODBC driver installed easily but when I try to configure the system DSN on the Windows system I get 10060 error code that it could not get a connection to the remote system.

When I dig into the port assignment interfaces, etc. I am confused by the eth1:cpn interfaces. MySQL is listening on 0.0.0.0 which I thought meant it would see the request coming in. I created an account with all privileges, which I am using to make the connection and I listed the 10.100.100.15 address of my MSSQL server in WHM. The ip address 10.200.200.19 is on eth1:cp3 and I use it in the connection.

I have tried lots of variation on the ip addresses but I always get the same error message. Is anyone familiar with what might cause this problem?

If you can help but need more information please ask. We own all these systems and I have full access to everything but I am about 350 miles away and use Putty or WHM to work on the cPanel/WHM system. I have a vpn connection to the other system.

 

[cPanelMichael]

Hello

You will need to ensure FreeTDS is installed on the cPanel server. The following threads may be helpful:

Definitive FreeTDS installation instructions
MSSQL support
Warning: mssql_connect() [function.mssql-connect]

Thank you.

 

[tommythejoat]

I do not understand how I would use Free TDS if it were installed.

I have code running on the Windows system that need to fetch data from the MySQL server on my Centos/WHM system. How will PHP support for MSSQL on the Centos system help? If I could get the ODBC driver on the windows system to configure and attach to the MySQL database I can do the job.

I downloaded and installed the Windows ODBC driver from the MySQL support site. They did not mention needing any third part extensions to work.

 

[cPanelMichael]

Thank you for clarifying. You are correct that PHP support for MSSQL on the cPanel server will not be helpful. I was under the impression you were connecting to MSSQL on the Windows server from the cPanel server. I now see you are connecting to MySQL on the cPanel server from a remote server running Windows. Unfortunately, it's difficult to troubleshoot an issue with the Windows server as it relates to cPanel. Have you ensured the remote IP address is added as an additional access host for MySQL in "WHM Home » SQL Services » Additional MySQL Access Hosts"?

Thank you.

 

[tommythejoat]

The reason I was focusing on the port assignments is the the connection to the centos system running MySQL is through a different ethernet interface. MySQL says it is listening on 0.0.0.0 but I have read elsewhere that MySQL can only listen on a single real ip address even if it is set up as promiscuous.

I do not know what tools to use from the Internet or the LAN to probe the 3303 port and what I should see.

If I just telnet to 3306 from the LAN it acts like a loopback instead of responding with anything I recognize.

eth0 is associated with the external ip address and seems to be working OK

 

[cPanelMichael]

It's normal for MySQL to run under 0.0.0.0. EX:

# netstat -lnp | grep :3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1663/mysqld

This issue sounds more like a problem with the server making the connection to MySQL. Have you checked to rule out any issues with firewall or router rules on either machine?

Thank you.

 

[tommythejoat]

The MySQL machine is in a co-location center. The MSSQL server is on a router that is connected to the router at the co-location center with a hardwired VPN connection. The tech support people tell me that all ports are open on the VPN connection and each of those routers.

Here at home I have a VPN to the router in the room with the MSSQL server. That router is managing 10.100.100.nnn and the other router is managing 10.200.200.nnn. Both locations have firewall facing the Internet. The servers for the Internet have connections to the firewall with the ip address published on the Internet.

Once I connect my VPN from home I can telnet to 10.200.200.19 (the MySQL Server) and see loop-back behavior on port 3306. I get the same behavior for port 22 (SSH) also. If I use Putty and SSH to the same machine via its Internet connection everything works fine and I can log on etc.

 

[cPanelMichael]

Unfortunately, it's difficult to troubleshoot the potential issues with the network or connectivity outside of the cPanel environment. You may need to consult with a qualified system administrator that has experience with this type of configuration.

Thank you.