Skip to main content
A
ACS LLC
Inspiring
August 12, 2018
Answered

Create CF database connection to MS SQL on VPS in private network

  • August 12, 2018
  • 3 replies
  • 1093 views

CF 2016

MS SQL 2008

Windows 2008 Server

I am trying to move my MS SQL database to a separate VPS which will sit within a private network.

The new VPS server has been given the internal IP of 192.168.1.1 and the CF server has the internal IP of 192.168.1.2, these are on secondary adapaters, they also have their public IP's.

I mapped a drive from the CF server to this new VPS database server and I was able to remotely browse the hard drive, so I know the connection between the two is functioning.

I then went to the CF admin to try and connect to a test database, but I'm getting nowhere, all I get are timeouts trying to connect to the MS SQL

I had made sure that SQL Server Browser service is running on the new database VPS

Remote connections is also checked within MS SQL

I also tried creating a new user within SQL and then using those credentials to connect.

I've made sure the ports 1433 and 1433 on TCP and UDP are both open on the firewall for both servers.

When adding the SERVER value in the connection settings within the CF admin, I've added the IP number, I've also tried //serverip, I've tried with port 1433 and also tried removing the port number.

Does anybody have any ideas as to what I'm missing?

Thanks.

    This topic has been closed for replies.
    Correct answer ACS LLC

    I found the solution and it was simple!

    It was the Windows firewall. I shut it off and it started working. The default settings were blocking the port 1433. I thought it was already open but it looks like when I had shut down external access on to the SQL on the CF server I must have removed the setting, not that I remember.

    I turned the firewall back on, it timeout out.

    All I had to do was add an incoming rule on TCP port 1433 and done, working using the secondary virtual adaptor on 192.168.0.1

    That was a lot of time for such as simple issue!

    I shall mark this as the correct answer

    Thanks for all the input.

    3 replies

    A
    ACS LLCAuthor
    Inspiring
    August 12, 2018

    The way it's working is that through my ISP I can create a private network, then add servers to that network.

    The subnet address is 192.168.0.0.with subnet mask of 255.255.255.0

    It creats a new virtual network card within the VPS. I then add an IP to each of the VPS that I added to the private network. Making the DB box 192.168.0.1 and the original CF server 192.168.0.2

    Now I did manage to do a drive mapping on the CF server to the DB VPS C drive using //192.168.0.1/c , so that did show me that it's accessible and communicating across that 192.168.0.x range.

    So this begs the question why ODBC won't work over it when I'm able to map to a drive using the same IP.

    I took a look at that link, I wasn't crystal clear as to what IP was mapped to which IP, but I'm still thinking that there must be a way to make this work without hitting that command prompt

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    August 13, 2018

    I really think you're going to need to look at that route add info from my last post. I don't think you're going to get around it. But don't be afraid of the command line!

    Drive mapping (aka Windows Networking) and database connections don't work the same way. TCP/IP networking is fairly complicated, as it would have to be to incorporate the entire world as it does. With most database connections, you connect to a TCP socket (IP address and port) on the target machine, and for that to work, your machine has to be able to figure out how to talk to the target machine through a gateway or router if necessary. With Windows Networking, the option exists to broadcast to all machines in the network segment to find your target, I think. (My MCSA was a long time ago, so I apologize in advance for any error there.) There are a whole bunch of ports used for Windows Networking, and some of those are nonroutable (NetBIOS over TCP/IP, a variety of TCP and UDP ports in the 137-139 range more or less) and others are routable (DirectHost or whatever it's called, TCP/445), etc. Anyway, it's complicated, but it's designed to work well in small networks ... like you have with your internal VPS.

    Finally, it seems like your network provider should also be able to sort this out very quickly. It's not a CF problem, it's a generic networking problem. In my experience, demonstrating that it's not CF-specific should help you get them to resolve it for you. Networking is a completely separate field. It's useful to know what you can, but it's sensible for you to get professional assistance to get problems resolved as soon as possible.

    Dave Watts, Fig Leaf Software

    Dave Watts, Eidolon LLC
    A
    ACS LLCAuthorCorrect answer
    Inspiring
    August 13, 2018

    I found the solution and it was simple!

    It was the Windows firewall. I shut it off and it started working. The default settings were blocking the port 1433. I thought it was already open but it looks like when I had shut down external access on to the SQL on the CF server I must have removed the setting, not that I remember.

    I turned the firewall back on, it timeout out.

    All I had to do was add an incoming rule on TCP port 1433 and done, working using the secondary virtual adaptor on 192.168.0.1

    That was a lot of time for such as simple issue!

    I shall mark this as the correct answer

    Thanks for all the input.

    A
    ACS LLCAuthor
    Inspiring
    August 12, 2018

    The mapped drive is on the new database server. I switched on drive sharing and then went to the CF server and connected to the database server.

    It just demonstrated that I was indeed able to connect to the database server from the CF server, using the CF server to read the hard drive of the database server., but you're right it did not demonstate that CF was able to communicate.

    On a Google search I'd read that SQL Server Browser had to be running, I've tried both ways anyway.

    If I try to make an ODBC connection from the CF server to the DB server, it hangs for a while and eventually times out.

    Mark

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    August 12, 2018

    One thing that looks odd to me is the internal IP address on the database server, which looks more like what you'd find on an internal gateway. It seems pretty clear what's going on - your database clients are attempting to use the external IP address to connect to the database server. I think the fix is to add manual route entries to your route table. This might help.

    How to Add a Static TCP/IP Route to the Windows Routing Table

    Dave Watts, Fig Leaf Software

    Dave Watts, Eidolon LLC
    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    August 12, 2018

    When you said you mapped a drive from the CF server to the DB server, do you mean that the mapped drive is on the CF server, or that it's on the DB server? If it's the first, it doesn't really tell you much about connectivity from the CF server to the DB server, just the other way around.

    The only inbound port you need open is TCP/1433 on the database server. You also don't need SQL Server Browser.

    What happens if you try to make an ODBC connection from the CF server to the DB server?

    Dave Watts, Fig Leaf Software

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices