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 Tuesday, April 19, 2011 2:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
Great article - thanks.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1095963
Posted Wednesday, April 20, 2011 2:17 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:12 AM
Points: 27, Visits: 117
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.


Men who wish to know about the world must learn about it in its particular details. - Heraclitus
Post #1096145
Posted Wednesday, April 20, 2011 3:13 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, 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 #1096164
Posted Wednesday, April 20, 2011 6:28 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:12 AM
Points: 27, Visits: 117
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.



Men who wish to know about the world must learn about it in its particular details. - Heraclitus
Post #1096266
Posted Wednesday, April 20, 2011 7:39 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, 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 #1096330
Posted Wednesday, November 16, 2011 8:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:23 AM
Points: 50, Visits: 88
I know this is an older thread but it is the BEST article I have found that somewhat covers what I'm attempting to do. Now I freely admit I am NOT a SQL expert so I am coming to those that are for some help. And if there is a book or something out there that would help me I am more than willing to purchase it.

Scenario: Platform with multiple web/app servers. Two SQL Servers that host multiple SQL instances (one per web/app server). Direct RDP access to the SQL Servers is restricted even within the network (so Admins have access but regular Support folks will use SSMS from the web/app servers if they need to get in to the application's databases).

What I want to do is restrict each instance so it is accessible only from its associated web/app server and from the localhost (ie. someone from one web/app server cannot use SSMS to connect to a SQL instance that doesn't belong to that web/app server). They will need to be able to use 3 accounts from each web/app server to reach the SQL Server instance - SA and two specific user accounts (although I could eliminate SA use - it would be really unnecessary in this scenario as they could use the two SQL user accounts).

Restricting at the vLAN firewall, between the web/app and SQL vLANs, is not an option (vCloud makes that too difficult) so it will need to be at the SQL level. I would like to use the whitelist option with an IP address table because it sounds like that will be the best option (not necessarily the easiest - but security is the goal not ease of use).

I'm hoping to get a specific example of how to do this as time is of the essence (the platform is almost ready for go-live and deployment and I want to have this in place before then if possible). Any assistance is VERY much appreciated!!

Chris
Post #1206815
Posted Wednesday, November 16, 2011 8:38 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
Don't do this from within SQL Server. Instead, use an IPSEC policy at the OS level.

How to block specific network protocols and ports by using IPSec

This talks about how to do it from the command-line. You can use the GUI to do so. In that case, execute mmc from Start | Run and then add the IPSEC policy add-in. Specifically restrict the TCP ports the SQL Servers are listening on to the IP addresses that should be able to connect.


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 #1206834
Posted Wednesday, November 16, 2011 8:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:23 AM
Points: 50, Visits: 88
Thanks! So I assume I would need to use the SQL Server Config Manager to specify what port each instance uses correct (looks like there is a how to article in the SQL 2008 Books Online library)? And if so then I should be able to use the server IP and port# with my app (it is a .NET-based app) instead of servername\instancename and can then turn the SQL Browser off (maybe)? Then use the policies to restrict inbound traffic per web/app server to each SQL instance port - correct?

Sorry if I sound like an idiot but at least I admit when I need to go to someone else for answers. Thanks for the help!!
Post #1206866
Posted Wednesday, November 16, 2011 9:06 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
christopher.metzger (11/16/2011)
Thanks! So I assume I would need to use the SQL Server Config Manager to specify what port each instance uses correct (looks like there is a how to article in the SQL 2008 Books Online library)? And if so then I should be able to use the server IP and port# with my app (it is a .NET-based app) instead of servername\instancename and can then turn the SQL Browser off (maybe)? Then use the policies to restrict inbound traffic per web/app server to each SQL instance port - correct?

Sorry if I sound like an idiot but at least I admit when I need to go to someone else for answers. Thanks for the help!!


All correct.


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 #1206881
Posted Friday, November 8, 2013 3:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 5,018, Visits: 10,538
K. Brian Kelley (4/15/2011)
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).


Nice article, thanks.
Too bad sometimes we end up doing things in the "wrong" place because we don't get assistance from those who could do it in the "right" place.
I could accept this solution as a temporary workaround while the network guys do their homework, but I would definitely avoid it as permanent solution.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1512580
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse