Restrict remote SQL access to specific IP(s)

Hello,

Does MySQL and SQL Server have a way to allow remote access, but only through a specific IP or set of IPs?

The situation is we have a two servers (on separate networks), one which is secured and mostly internal-only, and another which is an external web server. We would like to integrate their login and are exploring our options. One method would simply be to query the secure database, but we’d like to still keep it secure.

Thanks.

Does the internal/secure server have a web server? Creating a script which would verify the credentials might be the most secure approach.

If this is not possible, you can easily restrict access to the SQL server per login by modifying your GRANT statement. i.e.

GRANT ALL PRIVILEGES ON * . * TO  'ryan'@'IP OF EXTERNAL SERVER'

(though I wouldn’t recommend doing a GRANT ALL).

Another approach would be using Firewall Rules, but that is not something that can be addressed in a reply without more knowledge of what the servers are operating on.

Thanks.

Those are actually all the ideas that we came up with as well, so it’s good to see we’re not crazy.

A little more about the systems:

  • one is a windows server, running custom software and it’s database. It’s mostly internal with a few public areas. This database is the one we’d communicate with.
  • the other is linux, running almost completely public, with it’s own database.

The appproaches we plan to explore are:

  • limiting database access (it only needs to do select on two tables and maybe update, insert).
  • having basically api calls to get the data

I’m in favor of the former. I’m thinking if we create a user with select only permissions on the table(s) and another which has only insert, update we’d be pretty good. If we limited it through a firewall or something to only the one ip, even better.

The problem with an api that I foresee is that it’s on a different domain, so we’d have to use a GET query string which is less secure in a sense.

What do you think about that plan?

You should handle this at the Firewall. Before the connection even gets to the server. Block all connection on the required port unless it comes from so and so. Then it does not matter if the database itself supports this. There is also less issues from exploiting any weakness in the DBs authication. A better approch would be to create a secure VPN tunnel between the servers. Then you could access the DB as if it was local.

I thought about doing a VPN tunnel. Can those be set up cross-platform? (Linux connecting to Windows). If I’m not mistaken, the Windows server is already running a VPN (some iteration of Cisco if I’m not completely off base).

Part of the tricky part is that the software on the Windows machine is actually developed by a contractor and I don’t have access to edit it directly, so anything we do that involves a change on their end has to be rock solid.

Thanks.

What OS the computers are running should be of no consequence because the VPN should be created at the edge of the network. Most cases the router/firewall that connects your network to the Internet. You want the firewall and or VPN setup as close to the edge as possible.