dioscoredes (4/19/2011)
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.
This all assumes you're using default instances listening on the default port of tcp/1433. 🙂 And this solution is generally why I recommend that named instances be configured with static ports, so that ACLs can be put in place. If named instances are using dynamic ports and you put an ACL in place to the current port, SQL Server may switch to a different port on next startup and then no one gets in.
As far as slammer is concerned, it propagated by the SQL Server Listener service, which is udp/1434. The catch with UDP/1434 is it's what clients use to determine what port a named instance is on since there is no standard port for a named instance. So if you have named instances you must either continue to permit udp/1434 or you have to configure static ports on the SQL Server and tell everyone to connect via *servername*,*port* instead of ServerName\InstanceName.
K. Brian Kelley
@kbriankelley