Much preferring the hardware security appliance approach, here are a few comments on securing SQL server using firewalls/router ACLs. The discussion is dependent on the user population and what they are trying to do. Usually this divides into users writing SQL adhoc queries, application users and administrators. There are many ways of cooking up appliance security - here is our (unauthenticated connection) take on it (only tcp ACLs shown for brevity). Note that a permit ACL for a server immediately excludes all traffic for which there is no matching ACL.
Our policy is:-
- that all SQL ad hoc query users must work from known internal network segments and from registered hosts. In the example below (CISCO IOS) all my SQL ad hoc users are on network 10.1.1.255 as it happens. The inbound ACL on the SQLServer segment could look something like this:-
access-list 101 permit tcp host 10.1.1.0 0.0.0.255 host 172.16.1.1 eq 1433
- The app server is on a separate machine on a different LAN segment from the SQLServer. This ACL allows just the app server host only from 10.5.6.255 network
access-list 101 permit tcp host 10.5.6.23 0.0.0.255 host 172.16.1.1 eq 1433 log
- SQLServer admins - we allow remote desktop within the corporate LAN from known hosts, filtered in router ACLs. Some IPSec as well.
Looking at the CISCO literature on protecting SQLServers they are at pains to explain how to protect a SQLServer from propagating various worms eg Slammer in outbound ACLs - seems prretty essential to me but we havent sorted this out yet.
Depending on your relationship with the network team, corporate protocols and desire to have your desk still in place on Monday morning, you could put a firewall into the local wall jack, configure the WAN port to look exactly like the SQLServer NIC, mainly by crafting the MAC address on the WAN side NIC and silently manage your own local firewall solution. At this point, shifting the well known service port from 1433 with port forwarding on the appliance, is another step to consider.
Men who wish to know about the world must learn about it in its particular details. - Heraclitus