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?

Solution 1

I fixed this by entering the entire IP range of the local subnet in the allowed address and I was able to access DB server from my APP server and blocking all other remote connections.

Solution 2

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.

Solution 3

Short of the using outside methods such as network firewalls and ACLs, you can use Windows Firewall on the local machine. I've found just opening the port to be troublesome - plus it does not limit unwanted traffic coming in through that port. I've had success using Program Rules in Windows Firewall.

Start by creating a new inbound rule in Windows Firewall with Advanced Security.

1. In the New Inbound Rule Wizard, select the "Program" radio button and click Next.
2. On the next screen, select "This program path:" and enter the location for SQL Server on the machine - for example, %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
3. Select "Allow the connection" and click Next.
4. Make no changes on the Users step, click Next.
5. Leave the default settings on the Profile page, click Next.
6. On the Name page, enter "SQL Server Connectivity (Program Rule)", and click Finish on the wizard.

Find the rule in the Inbound Rules tab. Open the Properties dialog for the rule and go to the Scope tab. You can stipulate what server IPs, subnets, etc. of computers you want to access the box in the Remote IP address box.

Solution 4

Sorry if i'm late to the party,

but i had the same case i already tried some commons solutions like above. Then i figured my issue, lately i've been hardening my server and closed TLS 1.0, unfortunately it's been used by sql server. So i reactive TLS 1.0 and check on services MSSQLSERVER then it work. Check link : enter link description here

Thank you.

View more solutions