Block remote connection on SQL server and allow only local connection

Question

I am seeing multiple failed login attempts in my SQL server logs in production. I believe someone is trying to login using the "sa" credentials. I have disabled "sa" login. My SQL server is on the internet (public IP), I want to create a firewall rule which will allow only local connections i.e. my APP server on LAN.

I opened up windows firewall and added a inbound rule which says that allow connection on port 1433 only from a particular IP address (my app server ip) and don't allow remote connections.

But now, my app server is only not able to connect to sql server.

Any ideas?

Answer 1

OK, this is fixable. If you go to your Firewall controls on the server (Control Panel- Administrative Tools), you'll see the list of all your inbound firewall rules.

In that list you should be able to find SQL Server on port 1433. Double click to open that rule, then go to the "Scope" tab. In there you'll find boxes to stipulate which Local IPs are allowed through the wall, and a box for Remote IPs allowed through the wall. Using that, and talking to your network admin, you should be able to come up with a list of valid IPs (or maybe a IP wildcard like 191.100.100.*) that will block unwanted intrusions.

Obviously, though, having a SQL Server exposed to the outside world is a no-no, but I'm guessing you're not responsible for that.