Change the port, goes the theory, and you make life more difficult for a hacker to get at your systems. It might not prevent a determined attacker, who just starts with Port 1 and goes on to 9999 or whatever the theoretical maximum is, but it will put off the casual browser. Discussion here.
It isn't difficult to do, although if you have lots of servers it can be a bit boring. Here's a quick Summary:
- Log onto server and run SQL Server Configuration manager to set new port; restart services.
- Turn on firewall and allow access to that new port.
- In SSMS registered servers, specify Servername,Port e.g. SQL2012Dev,4321
Here's a step by step procedure with a little bit more detail.
1) First of all check which port is currently in use:
exec master..xp_readerrorlog 0, 1, N'Server is listening on'
GO
The answer will probably be 1433, but just check that your predecessor hasn't done this exercise for you already!
2) Log on to the server using Remote Desktop Connection
3) Run SQL Server Connection Manager. There are a couple of places where it specifies the port number. First select SQL Native Client 11.0 Configuration / Client protocols / RCP/IP
– and set the default port to your chosen value:
Next one is SQL Server Network Configuration / Protocols / tcp/ip / ip addresses. There are six ports - set them all to your chosen value.
Your changes will not work until you stop and restart the SQL Server Services:
4) The next step is to modify the Windows Firewall so that it allows your chosen port to get through the firewall. On the Administrative Tools menu, select Server Manager, and then Configuration and Windows Firewall. The Firewall should be on, but if it isn't you had better turn it on.
This is what it looks like if the Firewall is OFF:
Right click on Windows Firewall on the left of the screen, and select Properties from the pull down menu that appears. If the Firewall State is Off, set it to On (recommended) for each of the three profile tabs.
This is what it looks like when the Firewall is ON:
5) Select the Inbound Rules from the menu on the left hand side. SQL Server is probably close to the top, but you may need to sort the columns.
In the Properties, select the Protocols and Ports tab, and specify the port of your choice.
That was the Inbound Rules - now repeat that for the Outbound Rules.
6) Now go back to SQL Server Management Studio, and edit the Registered Server information. It doesn't need much, just enter a comma after the server name and then the new port number
e.g. SQL2012Dev,4321
7) And then just to be sure, log on to that server and go back to the code we started with:
exec master..xp_readerrorlog 0, 1, N'Server is listening on'
GO
The answer should be the new port number.
