On one of my servers (Win 2012 R2), SQL Server 2012 is constantly under attack with the SA account being hit like 10 times per second with different passwords. I don't have an SA account but still I would like to stop this probing as its probably consuming resources. I keep checking the logs and blocking the offending IPs at firewall level manually, still I would like a better solution. SQL Server runs locally with IIS to serve its websites, I only need to connect to SQL Server remotely for database development with SSMS, so at first I thought setting up a VPN but not sure if this is going to play well with SSMS and other services like FTP. As in interim solution where can I stop SQL Server from being visible to the outside world at the firewall by disabling port 1433, or elsewhere? I can then enable/disable this when developing and perhaps just for my IP. Thanks.
-
You appear to be able to block IPs in the Windows firewall; I'm sure closing ports should also be available as an option, no?– mustaccioCommented Aug 17, 2015 at 23:52
-
I have closed the SQL port and that stopped all the probing by I have to remember to enable my IP in all the time (dynamic IP) for development. Isn't there a tool that looks at the windows log files and on those events that show a SQL probe, add the IPs to the firewall to block them automatically?– Nelson PiresCommented Aug 17, 2015 at 23:58
-
May be there is such a tool, but then 1) you may want to clarify what you ask in the question itself and 2) it's not a SQL Server problem; you may have better chance asking about it at Server Fault– mustaccioCommented Aug 18, 2015 at 0:03
-
You could use a port forwarder and proxy to hit the dev box from the same ip. You could RDP in to that network to do work. You can automate the IP settings and only have it off while you're working then have it turn back on. You can set it up as part of something like a octopus/jenkins system like you do dev. There's many options, do any of those work for you?– Ali Razeghi - AWSCommented Aug 18, 2015 at 1:08
2 Answers
SQL Server runs locally with IIS to serve its websites
The main problem I see is having sql server and IIS running on the same server. IIS serves website and SQL Server should be on a different server and only allowed connection from IIS to SQLServer.
There are things that you can do to mitigate the problem:
- Change SQL Server port away from default of 1433 - I prefer static port. (this requires restart of sql server). Check my answer here for more details.
- Disable SQL Browser service and hide your sql server instance, so that attempts to find named or default instances is alleviated.
- Configure your firewall to block/restrict IP ranges that you think are the source of attacks.
- Rename
SAto something unusual e.g.DoNotUseThisLoginand Disable it. Use windows authentication ONLY. - Run Microsoft SQL Server 2012 Best Practice Analyzer to fix anythings highlighted.
- Make sure you uninstall any unneeded components and only the ones that are minimally needed are installed.
- You can implement a poor man's solution LOGON Trigger to limit connection rights to sql server.
-
With SQL server on a different server I experience lag in web page responsiveness as it will have to request data further than locally, so its a no for me right now. I will try the port change away from the default, maybe that will help with minimal side effects, also the logon trigger script is a great idea and will test fully soon. For now, disabled the firewall inbound rule for SQL server and that's doing a great job with minimal inconvenience. Thanks. Commented Aug 19, 2015 at 14:17
So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except for only through the web interfaces of your applications, correct?
If so, then is not adding an explicit rule in the Windows Firewall on this server to allow only the private IP address ranges to connect via local network on SQL Server ports not acceptable? I assume you trust your internal IP addresses on this port and don't suspect those as the authentication attempts with malicious intent?
Allow the [localhost IP] IIS apps to authenticate to SQL (may need to check site bindings, etc. and/or setup internal DNS pointers too).
On your external side, you should only be allowing the port 80 or 443 to port forward/NAT to the IIS server on those ports only and not all ports too it I would think -- stop at corporate firewall too I guess and only allow the web app ports to go to that server from the external interface with your firewall appliance proxies, etc.
