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 12345»»»

Blocking Users by IP Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 12:07 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Today @ 7:50 AM
Points: 6,624, Visits: 1,874
Comments posted to this topic are about the item Blocking Users by IP

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 #671256
Posted Monday, March 9, 2009 6:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:32 AM
Points: 1,260, Visits: 3,423
Very nice and short explanation about User - IP security! Simple code here I like it!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #671399
Posted Monday, March 9, 2009 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 24, 2009 1:19 PM
Points: 2, Visits: 22
Great article.

Question: Is it possible to use this to block repeated login attempts that fail (i.e., someone/app trying to login into my server over and over)? So for example, block the IP if there are 3+ login failures.

Thanks.
Post #671408
Posted Monday, March 9, 2009 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 24, 2009 1:19 PM
Points: 2, Visits: 22
I think I found the answer... Use the article + the xp_readerrorlog system proc...

Cool!
Post #671433
Posted Monday, March 9, 2009 7:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 11:26 AM
Points: 6, Visits: 12
Great article!
I used similar technique in ASP/MS SQL web environment about 5 years ago, but I saved some resources and authenticated user's IP in ASP code rater than in MS SQL. I did it pretty much the same way, by comparing user's IP with what stored in the table as allowable range(s) of user IPs. It seemed to be a good method for consortium sites where site licence rater then individual logins are used and maintained.

Thanks, Galina.
Post #671437
Posted Monday, March 9, 2009 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 23, 2011 10:23 AM
Points: 6, Visits: 60
Hi,

Will it work on SQL Server Express Edition also?

Regards
RK
Post #671443
Posted Monday, March 9, 2009 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:12 PM
Points: 26, Visits: 172
Nice trick.

Can you add some real life examples of where this was implemented? And the details of why it was absolutely necessary? And, were all other alternatives for handling security considered before implementing this solution?

Post #671535
Posted Monday, March 9, 2009 8:42 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Today @ 7:50 AM
Points: 6,624, Visits: 1,874
Ken Shapley (3/9/2009)
Nice trick.

Can you add some real life examples of where this was implemented? And the details of why it was absolutely necessary? And, were all other alternatives for handling security considered before implementing this solution?



A real world example was given in the article. If you see someone trying to connect as sa from a web server in the DMZ, that's usually a good sign (given good application design) that an attacker has compromised the web server and is launching an attack against the SQL Server. Now given that the web server is in the DMZ, it shouldn't be on the domain, which means you have to drop back to SQL Server logins. Now most folks configure their IDS/IPS to alert only. Meaning that attacks against SQL Server based logins would not be blocked by the IDS/IPS.

As to examples of where this kind of thing was implemented, if you search the forums, here, MSDN, and Stack Overflow (and on twitter, as I believe @BugBoi was implementing based on application name ~ tweets were around March 3).


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 #671572
Posted Monday, March 9, 2009 8:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
Excellent job, Brian, and a great solution. Wish we had a good one for 2000, but having this handy would be nice for blocking people making attempts to crack security or overload the server.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #671589
Posted Monday, March 9, 2009 11:59 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 9,294, Visits: 9,484
Great article, Brian. Is the Client_Host ip information still available about a session later on, or would I need to store it from a Logon Trigger like yours if I needed that?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #671820
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse