Blocking Users by IP

Question

Post reply

Blocking Users by IP

K. Brian Kelley

SSC Guru

Points: 114642
More actions
March 9, 2009 at 12:07 am

#97355

Comments posted to this topic are about the item Blocking Users by IP
K. Brian Kelley
@kbriankelley

Viewing 15 posts - 1 through 15 (of 53 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

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/[/url]

jjohnson-988578 SSC Enthusiast Points: 150 More actions · Answer 2

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.

jjohnson-988578 SSC Enthusiast Points: 150 More actions · Answer 3

I think I found the answer... Use the article + the xp_readerrorlog system proc...

Cool!

Galina Ignatenko SSC Enthusiast Points: 118 More actions · Answer 4

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.

RK-842125 SSC Journeyman Points: 78 More actions · Answer 5

Hi,

Will it work on SQL Server Express Edition also?

Regards

RK

Ken Shapley SSC-Addicted Points: 458 More actions · Answer 6

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?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

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
@kbriankelley

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 8

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

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?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul Paiva SSCrazy Points: 2237 More actions · Answer 10

Good job, Brian. I will definitely use this.

- Paul

http://paulpaivasql.blogspot.com/

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

RBarryYoung (3/9/2009)
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?

It's also available in sys.dm_exec_connections in the column client_net_address.

K. Brian Kelley
@kbriankelley

er.imran.it SSC Rookie Points: 42 More actions · Answer 12

Hey I ran this and my DB is blocked. Help me out my system giving myself error

Galina Ignatenko SSC Enthusiast Points: 118 More actions · Answer 13

Galina Ignatenko

SSC Enthusiast

Points: 118

March 9, 2009 at 12:35 pm

#956323

It should

er.imran.it SSC Rookie Points: 42 More actions · Answer 14

Any resolution???? I am not good in DB.. Plz help....