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:21 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, Visits: 265
Good job, Brian. I will definitely use this.


- Paul

http://paulpaivasql.blogspot.com/
Post #671850
Posted Monday, March 9, 2009 12:24 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, 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 #671853
Posted Monday, March 9, 2009 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:05 AM
Points: 4, Visits: 36
Hey I ran this and my DB is blocked. Help me out my system giving myself error
Post #671855
Posted Monday, March 9, 2009 12:35 PM
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
It should
Post #671870
Posted Monday, March 9, 2009 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:05 AM
Points: 4, Visits: 36
Any resolution???? I am not good in DB.. Plz help....
Post #671884
Posted Monday, March 9, 2009 12:57 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
er.imran.it (3/9/2009)
Any resolution???? I am not good in DB.. Plz help....


If you are logged on to the same computer as SQL Server, then use SQLCMD with the following flags:

SQLCMD -A -E

This forces SQLCMD to connect to the DAC as your Windows account. From there you should be able to drop or disable the trigger as given in the article.


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 #671893
Posted Monday, March 9, 2009 1:03 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
K. Brian Kelley (3/9/2009)
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.

Hah! That's great, Brian. I've seen this question (how can to get a session's IP address) asked many times without hearing an answer. Thanks.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #671896
Posted Monday, March 9, 2009 1:06 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 7:57 AM
Points: 1,110, Visits: 1,148
er.imran.it (3/9/2009)
Any resolution???? I am not good in DB.. Plz help....


Probably shouldn't have run the code then.
Post #671900
Posted Monday, March 9, 2009 4:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 12:25 PM
Points: 26, Visits: 173
This is good stuff. I'm learning. It's got me thinking how an attacker could ompromise our web servers and launch an attack against the SQL Server.
Post #672023
Posted Friday, March 20, 2009 5:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:51 PM
Points: 173, Visits: 430
Thanks for the post Brian.

However, I am stuck with few issues. Hope you could throw some light on them and clarify .

1. I executed the Step to insert <local machine< into ValidIP table and still i am not able to connect from my local machine, bcoz of trigger execution.

2. I changed the trigger to capture the @IP value in troubleshooting the above said issue. Below is my code:

CREATE TRIGGER tr_logon_CheckIP
ON ALL SERVER
FOR LOGON
AS
DECLARE @IP NVARCHAR(15),@SqlCmd NVARCHAR(100);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
INSERT INTO dbo.ValidIP (IP) VALUES (@IP);
BEGIN
IF IS_SRVROLEMEMBER('sysadmin') = 1
BEGIN

IF NOT EXISTS(SELECT IP FROM DBAWork.dbo.ValidIP WHERE IP = @IP)
ROLLBACK;
END;
END;
GO

But this is not inserting any @IP value in the ValidIP table. Does triggers have constraint of not inserting any value into the tables in execution.

3. If @IP value can't be inserted into the table ,as desired, is there any other way to see the output of EventData() or IP later.

Thanks in advance!!
Vampire


--In 'thoughts'...
Lonely Rogue
Post #680275
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse