3

To study some specific customer issues, I have a copy of their production database running on a separate machine. The database has CLR assemblies and some complex legacy code to call external services through triggers and stored procedures.

I would like to prevent accidental calls of the external production services without having to inspect all the triggers one by one or disabling all the triggers.

What would be the easiest way to block all outgoing network requests of an SQL server instance and how do I test that the block is really active, so I can safely work with the database without any danger of sending data to external services?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JustAMartin
  • 13,165
  • 18
  • 99
  • 183
  • 2
    Why not put the instance on its own server and just firewall the server? No telling what things it runs via xp_cmdshell and external processes. – AlwaysLearning Nov 19 '19 at 09:23
  • Considering all you have is a copy of their database, and no access to their server, not sure what you *can* do. It's up the the server owner to ensure the security of their network, not you. if they've added custom behaviour to the application (which your question reads like they have) it's up to whomever designed those changes to ensure they are secure as well; not the vendor of the application (which I assume is yourself). An application is only as secure as a Vendor makes it ***if*** the client doesn't modify it, and if a client does they need to support it and make it secure. – Thom A Nov 19 '19 at 09:55
  • @Larnu At this point it's not about security. It's about debugging some issues using a copy of their production database deployed onto a standalone server. The server machine itself must have Internet access because I connect to it remotely and it also has some web services installed. It's just the database triggered outgoing requests that I want to block to avoid accidentally sending a request to an external 3rd party service while I investigate the issues. – JustAMartin Nov 19 '19 at 10:15
  • How about putting a firewall to block all the connections that are initialled for the production database (it's IP address or connection value) and then try to call read request to the production database and check if it is returning the value or giving out error netowrk not reachable. – Linkon Nov 19 '19 at 10:20
  • @AlwaysLearning Unfortunately I cannot completely disconnect the server from the network because I connect to it remotely and it has some web services installed and also some outgoing calls from the web services which I want to leave functional. It's just the SQL server database that I want to completely isolate from the network. I could use firewall to block it but I'm not quite sure how to block entire SQL server instance considering that it might have multiple processes running with different names and maybe even as Windows Services and I don't know all of their names. – JustAMartin Nov 19 '19 at 10:20
  • @Linkon It's actually reversed - I have to check if any trigger called from the database has no access to the external 3rd party services, while at the same time leaving the server machine networking fully functional (because I need RDP connection and a bunch of other outgoing connections working on it). So, I would need to firewall all outgoing traffic on SQL server processes only, but the problem is that SQL server is a complex beast running in multiple processes and services and I'm not sure how to define firewall rules to block them all. – JustAMartin Nov 19 '19 at 10:29
  • 1
    You can use the Windows firewall to block all outgoing requests from the SQL Server service process. You can use the services list to discover the service name of the instance. This is, realistically speaking, the only option you have available -- you can't block requests per assembly or per database because, beyond knowing if the assembly is "safe" (which it undoubtedly is not in your case, as the APIs would be blocked) SQL Server doesn't know or care what code is running in the assembly. Isolation is per machine and process, not per database. – Jeroen Mostert Nov 19 '19 at 10:48
  • 1
    If the outgoing services being called are unique to the assembly, a "poor man's firewall" is to add entries for these machines to the `hosts` file pointing back to 127.0.0.1 (assuming your assemblies use names and not IP addresses). This, of course, is another global solution to a local problem and it won't work if they're used by other databases. If you want safety, isolation is the only proper strategy. If that means dedicating more resources to a new machine, consider dedicating those resources. TANSTAAFL. – Jeroen Mostert Nov 19 '19 at 10:50
  • Note that "it needs access to the Internet because I connect remotely" is not really an issue; it suffices to block all *outgoing* connections. By default the firewall already blocks all incoming connections save those you allow. (Using a machine where other services are installed that need access is, of course, a problem, but that seems avoidable.) – Jeroen Mostert Nov 19 '19 at 10:57
  • @JeroenMostert Yes, that would be a good workaround. Unfortunately the database is old and large and it would be very cumbersome to hunt for all external IP addresses hardcoded in those triggers and CLRs and whatever, so I hoped there is some simpler way to tell SQL server itself to forbid any outgoing TCP/UDP connection. – JustAMartin Nov 19 '19 at 10:59
  • I would not trust Windows Firewall to do this, even blocking the SQL Server process. I've seen too many stupid applications over the years that instantiate COM/ActiveX components from T-SQL (e.g.: MSXML2) and those can be proxying through services or other processes to access the internet. – AlwaysLearning Nov 19 '19 at 11:01
  • @JustAMartin: No, there isn't. SQL Server doesn't contain any firewalling code itself. – Jeroen Mostert Nov 19 '19 at 11:03

0 Answers0