Blocking Users by IP

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

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 1 of 5

1 2 3 4 5 »»»

Blocking Users by IP

Rate Topic

Display Mode

Topic Options

Author

Message

K. Brian Kelley

Posted Monday, March 09, 2009 12:07 AM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790

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

Dugi

Posted Monday, March 09, 2009 6:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:48 AM
Points: 1,252, Visits: 3,367

Very nice and short explanation about User - IP security! Simple code here I like it!
Cool

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #671399

jjohnson-988578

Posted Monday, March 09, 2009 6:40 AM

Forum 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

jjohnson-988578

Posted Monday, March 09, 2009 7:05 AM

Forum 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

Galina Ignatenko

Posted Monday, March 09, 2009 7:11 AM

Forum 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

RK-842125

Posted Monday, March 09, 2009 7:16 AM

Forum 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

Ken Shapley

Posted Monday, March 09, 2009 8:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 1:59 PM
Points: 26, Visits: 143

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

K. Brian Kelley

Posted Monday, March 09, 2009 8:42 AM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:13 PM
Points: 6,584, Visits: 1,790

Ken Shapley (3/9/2009)

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

Steve Jones - SSC Editor

Posted Monday, March 09, 2009 8:55 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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

RBarryYoung

Posted Monday, March 09, 2009 11:59 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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 »

49 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions