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.

December 23, 2010

  • The command line FTP client is hanging when I attempt to get a file…or even ls a directory. I followed the steps above but it’s still hanging. The Windows Security Alert dialog (on which the File Transfer Program could be ‘unblocked’) never appears. I’ve confirmed ‘Display a notification’ is enabled on all three profile tabs. Rebooting hasn’t helped. It even hangs if I disable the Windows Firewall altogether! I’m stumped. Any ideas?

  • Thanks for that – problem part solved.
    For the remainder:
    I’m creating the FTP script in sql which I save in a file on the sql server. I can’t make the server run it from sql. Something like this works for other activities:
    set @cmd=’xp_cmdshell “FTP -n -s:FTPscript.txt” ‘
    but not this time. What am I missing?

  • Leave a Reply

    Your email address will not be published. Required fields are marked *