Andrew Fraser DBA

Windows Server 2008 R2 Firewall for SQL Server and FTP

Windows Server 2008 R2 has a strict firewall by default.

It will not let you run FTP (client) from the server and get files from other servers/sites

It will also not let you run “SQL Server Management Studio” from your PC and connect that into the instance running on the server.

To fix this, apply the below changes are made:

1) Running FTP (client) from server command prompt will allow you to connect out to an FTP site, but any ‘ls‘ or ‘get‘ commands will hang.

To resolve this, first set firewall notification on:

Start > Administrative Tools > Server Manager > Configuration > Windows Firewall with Advanced Security > Windows Firewall Properties (also available with a right click) > click on the ‘Domain Profile’ tab – ‘settings’ section – ‘customize’ button > Change ‘Display a notification’ to ‘yes’ > click ‘ok’ twice for changes to take effect.

Now go to a command prompt window and start ftp client session and try ‘ls’. It will still hang, but this time a window will prompt if you want to unblock ftp client:

Click the ‘Allow Access’ button.

Now any new ftp client sessions will work ok. You can test that by opening a second command window and running ftp again.

At this stage, you can set the firewall notification back to ‘off’, if you want.

2) To allow “SQL Server Management Studio” connections from e.g. your PC into the server, add a rule for port 1433 to the database server firewall, with :-

Start > Administrative Tools > Server Manager > Configuration > Windows Firewall with Advanced Security > Inbound Rules > (right click) > New Rule

Rule Type: change to ‘Port’

‘Protocols and Ports’: Keep with ‘TCP’, but specify ‘Specific local ports’ – 1433

Accept defaults with the other screens, except give the rule a meaningful name and description.

Screenshots for this below.