<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Brian Kelley
»
Blocking Users by IP
49 posts, Page 5 of 5
««
«
1
2
3
4
5
Blocking Users by IP
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
SQLRNNR
Posted Tuesday, April 19, 2011 2:37 PM
SSCoach
Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733,
Visits: 12,332
Great article - thanks.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
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
dioscoredes
dioscoredes
Posted Wednesday, April 20, 2011 2:17 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:58 AM
Points: 27,
Visits: 116
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
K. Brian Kelley
K. Brian Kelley
Posted Wednesday, April 20, 2011 3:13 AM
Keeper of the Duck
Group: Moderators
Last Login: Yesterday @ 1:55 PM
Points: 6,584,
Visits: 1,789
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
dioscoredes
dioscoredes
Posted Wednesday, April 20, 2011 6:28 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:58 AM
Points: 27,
Visits: 116
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
K. Brian Kelley
K. Brian Kelley
Posted Wednesday, April 20, 2011 7:39 AM
Keeper of the Duck
Group: Moderators
Last Login: Yesterday @ 1:55 PM
Points: 6,584,
Visits: 1,789
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
Chris Metzger
Chris Metzger
Posted Wednesday, November 16, 2011 8:31 AM
Valued 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
K. Brian Kelley
K. Brian Kelley
Posted Wednesday, November 16, 2011 8:38 AM
Keeper of the Duck
Group: Moderators
Last Login: Yesterday @ 1:55 PM
Points: 6,584,
Visits: 1,789
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
Chris Metzger
Chris Metzger
Posted Wednesday, November 16, 2011 8:53 AM
Valued 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
K. Brian Kelley
K. Brian Kelley
Posted Wednesday, November 16, 2011 9:06 AM
Keeper of the Duck
Group: Moderators
Last Login: Yesterday @ 1:55 PM
Points: 6,584,
Visits: 1,789
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
« Prev Topic
|
Next Topic »
49 posts, Page 5 of 5
««
«
1
2
3
4
5
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
