Configuring SQL Server to listen on a specific port

26. March 2015 10:17 by Chris in ForTheRecod, IT Pro  //  Tags:   //   Comments (0)

Looking at our SQL Server 2008R2 box, which was exposed to the internet on the default sql server port of 1433, I noticed in the event log we were getting sporadic brute force attacks by bots on various SQL Server user accounts. A quick google indicates that this configuration is just not on. You are just asking for such trouble. I didn't set this box up by the way. I don't think ;) There are articles out there with several recommendations but the key one is move to a different port. The MSDN article on this topic isn't perfectly clear and it does depend on your network setup - configured IP addresses and the like. So here is my quick guide on the matter.

In SQL Server config manager go to to TCP/IP in network config for the server instance and change the TCP port settings to another number - the top end is about 49000 and a few are reserved. Google/ Bing is your friend. The slightly confusing bit, for me, was which to reconfigure port forwarding entries to reconfigure - networking isn't my day job. IP6 looked like the correct option. That didn't work when changed, service restarted and connecting from management studio from another machine - the default port was still working. As I had SQL 2012 on the box as well installed on a different port I compared settings and all its ports were set to the same bar 1 so I did similarly for 2008R", in fact configuring them all to the new port.

I then check management studio again, connecting to the default port on the local network to the machine name and it failed. I tried on the configured port - servername,portno - and that timed out as well BUT I figured this was firewall security. I added an inbound entry for that port in the firewall for TCP and whiel I was there I disabled the existing rule for 1433. Tried again and bingo, I was in. All good.

Next step was to check the sub-domain forwarding on the LAN router set up to direct connections from the internet to the box where the sql server instance resides. Not particularly recalling the config detail at the I hoped this would 'just work', but it didn't ;( SQL Server 'actively refused' the connection. Checking the router config I was reminded that it was port forwarding that was configured for the external static IP so of course this was set to 1433 and had to be modified as well. Then, ... it worked. Joy. The one final test I need to get to is to make sure that connections *actually* external to my LAN can access the SQLServer through the new port. But there is no urgent requirement to do so and I have these notes to return to now if needs must.

I then also cleared all those application event log entries for the brute force attacks and will keep a closer eye on the logs from now on.

This is recorded here in case I need it again but, you never know, it may be of use to someone else!

However, I may not need it again as

a) I'm moving which might mean discarding the current static IP (though I may need to keep that for other work reasons actually), and

b) I should really be using Azure by now anyway!

 

 

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About the author

I am Dr Christopher Sully (MCPD, MCSD) and I am a Cardiff, UK based IT Consultant/ Developer and have been involved in the industry since 1996 though I started programming considerably earlier than that. During the intervening period I've worked mainly on web application projects utilising Microsoft products and technologies: principally ASP.NET and SQL Server and working on all phases of the project lifecycle. If you might like to utilise some of the aforementioned experience I would strongly recommend that you contact me. I am also trying to improve my Welsh so am likely to blog about this as well as IT matters.

Month List