dioscoredes (4/18/2011)
Simple, elegant and easy to follow. Thanks for the technique. Can I ask how we would get round NAT masking the true IP address - or I guess we simply couldn't - or maybe wouldn't want to anyway? This trigger would work within my organisation where un-NATed IPs are unicasted and but not from an outside network. Yup. That's just what I want. Cuts off opportunity of remote port 1433 admin though as ISPs do NAT, but I dont allow remote admin onto our servers anyway. As the article concludes, there are more complex security solutions available and something like RADIUS could be configured to allow secure remote acess to port 1433 if remote admin from an outside network was a real issue.
If they are using NAT, you have to do the access control from that point or earlier. Once you get to SQL Server, it only knows the IP it receives. I would personally prefer to this at the router level or at a firewall in between the router and SQL Server, as this causes SQL Server to expend CPU and memory on what is effectively a networking issue. However, if all you have to rely on is SQL Server, you have to do what you have to do.
K. Brian Kelley
@kbriankelley