April 15, 2011 at 2:56 am
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.
April 15, 2011 at 8:24 am
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
@kbriankelley
April 17, 2011 at 6:12 pm
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/MCSE/MCSA
April 18, 2011 at 2:32 am
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.
April 18, 2011 at 7:39 am
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.:w00t:
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
April 18, 2011 at 8:46 am
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
April 18, 2011 at 8:48 am
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 inthis 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
@kbriankelley
April 19, 2011 at 6:21 am
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.:w00t:
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.
April 19, 2011 at 7:45 am
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.
April 19, 2011 at 8:03 am
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
April 19, 2011 at 2:37 pm
Great article - thanks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2011 at 2:17 am
When a sqlserver client app connects to the default instance of the sql server the connection goes to the TCP port 1433. However I agree the named instance port is dynamic but may not require a different approach.
When a connection to a named instance of the sql server is made, the connection goes to the sql browser service which listens on the UDP port 1434 (so we can control access to this UDP port and therefore prevent the handoff to the named instance port ever being requested). The sql browser service replies to the client with port no. on which the named instance of the sql server listens and then the client is redirected and connects to the port no. provided by sql server service.
I do not know why Microsoft cannot pursuade CISCO to write a "SQLServer FIXUP" which would solve the problem of dynamic ports (we have this in the Oracle world as well).
If 1434/udp is closed to others, the only way in for forbidden users now is a hack on the dynamic port which is beyond my skills and knowledge. I would only hope that if my servers are reasonably difficult to get into an intruder would look elsewhere for his kicks. Omitting the "eq" directive in the ACL makes the scope "all ports" and would put the router back in control of who is accessing the sqlserver and from where but may be too general (all ports) for individual ips passed by the filter, unless the sqlserver was correctly hardened.
April 20, 2011 at 3:13 am
dioscoredes (4/20/2011)
When a connection to a named instance of the sql server is made, the connection goes to the sql browser service which listens on the UDP port 1434 (so we can control access to this UDP port and therefore prevent the handoff to the named instance port ever being requested). The sql browser service replies to the client with port no. on which the named instance of the sql server listens and then the client is redirected and connects to the port no. provided by sql server service.I do not know why Microsoft cannot pursuade CISCO to write a "SQLServer FIXUP" which would solve the problem of dynamic ports (we have this in the Oracle world as well).
If 1434/udp is closed to others, the only way in for forbidden users now is a hack on the dynamic port which is beyond my skills and knowledge. I would only hope that if my servers are reasonably difficult to get into an intruder would look elsewhere for his kicks. Omitting the "eq" directive in the ACL makes the scope "all ports" and would put the router back in control of who is accessing the sqlserver and from where but may be too general (all ports) for individual ips passed by the filter, unless the sqlserver was correctly hardened.
Two things here. First, a named instance doesn't have to use a dynamic port. You can configure it to use a static port. Not only does this permit you to build your ACLs, but it also means you can have an AD admin build the SPNs and not configure the SQL Server service account to be able to update the SPN information dynamically in AD (which requires delegation of permissions).
Second, if the dynamic port is exposed (you allow a connection to it), it's trivial to figure out what port SQL Server is listening on. nmap will do that, and has switches to do so stealthily to try and avoid IDS/IPS. What you're effectively doing is a port scan + fingerprinting of the service that responds on that port. SQL Server is fairly easy to pick up. Now, if your SQL Server is exposed to the Internet and I don't have much to worry about with respect to IDS/IPS, while you're not low-hanging fruit, you're only one step up from it.
K. Brian Kelley
@kbriankelley
April 20, 2011 at 6:28 am
Here is the scenario:-
access-list 101 permit tcp host 10.5.6.23 0.0.0.255 host 172.16.1.1 ## all portnumbs admitted ##
The server is exposed to packets from a single ip.
An nmap user must deploy packets with a host sender address of 10.5.6.23 (easy enough), based on prior footprinting experience where the 'from' server has been broken into and researched - or similar. I smell acrid burning toast in this company scenario way beyond primary SQLServer vulnerabilities.
The weakness you seek to exploit are readily addressed by available security devices and services - Network VPNd end to end, CISCO switch at client with MAC address filter, RADIUS remote access authentication, scan and probe detector appliances, - to mention but a few.
Anything one man can create another can take apart. The trick is to make it sufficiently hard to take apart to act as a deterrent. The discussion should embrace the effort required to commit an exploit and the motivation to do it. If my NASA server holds data on the next moon shot I am worried. If it is the local playground maintenance system I may sleep at night. Its not just low hanging fruit but the VALUE of the fruit that determines risk and impact and how much is to be spent on protection.
Kevin Mitnick's book on social engineering is enlightening. Network based hacking expoints are often elegant and informative but most commercial motivated hacks are based on what information human beings haemorrhage and on the weaknesses of human behaviour. This does worry me.
April 20, 2011 at 7:39 am
That's why I indicated if you permit access to it. And remember, attacking servers isn't just about what they contain with respect to data. It's also to see what other information might be valuable on it (service accounts w/ passwords, etc.) or to use it as a new launching platform to get around ACLs. If you watch good pen-testers at work, they are looking to get one system, any system, and they'll slowly use that system to attack others. Why do they do this? Because this is how attackers work if they want to go deeper.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply