Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Blocking Users by IP Expand / Collapse
Author
Message
Posted Friday, April 15, 2011 2:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 08, 2013 5:04 AM
Points: 10, Visits: 154
this seems like a lot of effort and increased overhead on your SQL database, for something that can be handled much more efficiently at the transport layer


If in your situation you can handle this much more efficiently at the transport layer, then you should. But if you can't (in a large organisation your empire as DBA may stop where your database ends, and you don't have control over the transport layer), then here is a cool idea for adding IP-based restrictions. YMMV.
Post #1093977
Posted Friday, April 15, 2011 8:24 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
jinlye (4/15/2011)
this seems like a lot of effort and increased overhead on your SQL database, for something that can be handled much more efficiently at the transport layer


If in your situation you can handle this much more efficiently at the transport layer, then you should. But if you can't (in a large organisation your empire as DBA may stop where your database ends, and you don't have control over the transport layer), then here is a cool idea for adding IP-based restrictions. YMMV.


Exactly. I wrote the article in response to a question I received where the DBA was not getting support from the networking folks. My typical recommendation (check my blog) is to try and do this with a firewall between SQL Server and everything else (not the OS, but a separate device).


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1094182
Posted Sunday, April 17, 2011 6:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
Thank you for a great article, Brian.
Nice to see the logon triggers used in a real and useful situation although you explicitly stated that this may not be applied to all situations.

Explanations were very clear and simple to understand yet interesting to read.

Best regards,



Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1094675
Posted Monday, April 18, 2011 2:32 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:58 AM
Points: 27, Visits: 116
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.

Men who wish to know about the world must learn about it in its particular details. - Heraclitus
Post #1094777
Posted Monday, April 18, 2011 7:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
Eric Hobbs (4/15/2011)
Hopefully I'm not being dim here, but this seems like a lot of effort and increased overhead on your SQL database, for something that can be handled much more efficiently at the transport layer. In general, if you want to ensure that connections to a server are only made by a specific IP address, then you set the network card up to only accept connections from a specific IP, address, or you use a firewall, since this is more robust at detecting IP spoofing ect... by all means, limit the access of the security principle, but creating a trigger for each time a connection is made seems like a really bad idea.


over the years i've found the Windows firewall to be flaky at best in trying to created rules to let some traffic in

this is also a good way to ensure security for internal traffic as well by blocking development and others from SQL servers if they don't need to be there


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1094945
Posted Monday, April 18, 2011 8:46 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1095030
Posted Monday, April 18, 2011 8:48 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
alen teplitsky (4/18/2011)
over the years i've found the Windows firewall to be flaky at best in trying to created rules to let some traffic in

this is also a good way to ensure security for internal traffic as well by blocking development and others from SQL servers if they don't need to be there


I've seen the Windows firewall to be flaky on occasion. However, I make it a practice of not relying on it for production systems. If I have to do filtering at the OS level, I'm going to use an IPSEC policy instead. That way I can have more granular control and can force encryption of the network traffic if I need to.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1095032
Posted Tuesday, April 19, 2011 6:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:30 PM
Points: 1,264, Visits: 721
Eric Hobbs (4/15/2011)
Hopefully I'm not being dim here, but this seems like a lot of effort and increased overhead on your SQL database, for something that can be handled much more efficiently at the transport layer. In general, if you want to ensure that connections to a server are only made by a specific IP address, then you set the network card up to only accept connections from a specific IP, address, or you use a firewall, since this is more robust at detecting IP spoofing ect... by all means, limit the access of the security principle, but creating a trigger for each time a connection is made seems like a really bad idea.

In my case I want to make sure that application is connected to the database only from application server with specific login. Firewall wouldn't work here as users with other logins allowed to connect from different network. It gives you more flexibility. I think there could be another level of filtering added as well - Application Name.
Post #1095553
Posted Tuesday, April 19, 2011 7:45 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:58 AM
Points: 27, Visits: 116
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
Post #1095635
Posted Tuesday, April 19, 2011 8:03 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1095654
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse