New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


create sensor to monitor sql logins

Votes:

0

Your Vote:

Up

Down

Hi

I want to monitor when someone logins to the server as administrator and when someone tries to login to our SQL server by trying to guess the sa account or admin account some guessing an account.

Cheers

login server-administrator sql-server-login

Created on Jun 27, 2011 1:03:50 PM by  braveboy (0) 1



Best Answer

Accepted Answer

Votes:

0

Your Vote:

Up

Down

The can be done with system stored procedure xp_readerrorlog

Step 1

Create a new stored procedure using the following code:

CREATE PROCEDURE spLoginAttempts
AS
BEGIN
  DECLARE @temp Table (LogDate datetime, ProcessInfo nvarchar(20), Text nvarchar(100))
  INSERT INTO @temp
  EXECUTE xp_readerrorlog 0, 1, 'Login failed', 'administrator'

  SELECT COUNT (*) FROM @temp
  WHERE Logdate > DATEADD(HOUR, -24, GETDATE())
END

This wil return the the number of failed login attempts for the administrator over the last 24 hours.

Step 2

For this to work, you need to enable "Login auditing" on your sql-server. To enable "Login auditing" follow the steps below:

  • Start SQL Server management Studio.
  • Connect to your SQL server.
  • Right click your server in the 'object explorer' pane and select 'Properties'.
  • Select the 'Security' page and determine when login auditing should take place.

Step 3

Add a new Custom XML sensor

We can now add a Microsoft-SQL sensor that runs the Stored Procedure (SQL expression = exec spLoginAttempts) and check for the return value.

Created on Jun 28, 2011 12:53:05 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,135) 3 4

Last change on Jun 29, 2011 7:12:03 PM by  Torsten Lindner [Paessler Support]



10 Replies

Votes:

0

Your Vote:

Up

Down

hallo,

you could try with an eventlog sensor.

Created on Jun 27, 2011 7:03:06 PM by  Aurelio Lombardi [Paessler Support]



Accepted Answer

Votes:

0

Your Vote:

Up

Down

The can be done with system stored procedure xp_readerrorlog

Step 1

Create a new stored procedure using the following code:

CREATE PROCEDURE spLoginAttempts
AS
BEGIN
  DECLARE @temp Table (LogDate datetime, ProcessInfo nvarchar(20), Text nvarchar(100))
  INSERT INTO @temp
  EXECUTE xp_readerrorlog 0, 1, 'Login failed', 'administrator'

  SELECT COUNT (*) FROM @temp
  WHERE Logdate > DATEADD(HOUR, -24, GETDATE())
END

This wil return the the number of failed login attempts for the administrator over the last 24 hours.

Step 2

For this to work, you need to enable "Login auditing" on your sql-server. To enable "Login auditing" follow the steps below:

  • Start SQL Server management Studio.
  • Connect to your SQL server.
  • Right click your server in the 'object explorer' pane and select 'Properties'.
  • Select the 'Security' page and determine when login auditing should take place.

Step 3

Add a new Custom XML sensor

We can now add a Microsoft-SQL sensor that runs the Stored Procedure (SQL expression = exec spLoginAttempts) and check for the return value.

Created on Jun 28, 2011 12:53:05 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,135) 3 4

Last change on Jun 29, 2011 7:12:03 PM by  Torsten Lindner [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Gerard you are the man that's a great recommendation!

Could I be cheeky and ask about the first part of my question too....how to check when someone logs in to each of my servers....

Thank you so much gonna try that later...

Created on Jun 28, 2011 1:08:09 PM by  braveboy (0) 1



Votes:

0

Your Vote:

Up

Down

In Step 2 Enable 'Login auditing' for 'Both failed and succesful logins'

next, execute xp_readerrorlog only with parameters 0, 1

EXECUTE xp_readerrorlog 0, 1

and look at what it returns. Use the string parameters to filter out the results you are looking for.

Have fun!

Created on Jun 28, 2011 1:22:30 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,135) 3 4



Votes:

0

Your Vote:

Up

Down

Hi

I'm getting this error when I run the stored procedure:

Msg 156, Level 15, State 1, Procedure spLoginAttempts, Line 3 Incorrect syntax near the keyword 'DECLARE'.

Created on Jun 29, 2011 8:11:59 AM by  braveboy (0) 1



Votes:

0

Your Vote:

Up

Down

I think you are getting this error when you try to create the stored procedure.

This is because the BEGIN - END declaration was missing in my original post. :-(

I updated it yesterday, now showing the correct syntax.

Created on Jun 29, 2011 2:45:20 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,135) 3 4



Votes:

0

Your Vote:

Up

Down

Great that worked, but I've added the sensor now in the SQL Expression row I've added: exec LoginAttempts but getting a sensor Alarm this is obviously incorrect as a SQL Expression?

Created on Jun 29, 2011 6:39:45 PM by  braveboy (0) 1



Votes:

0

Your Vote:

Up

Down

If you used the code above, the name of the stored procedure is spLoginAttempts so it should be:

exec spLoginAttempts

Created on Jun 29, 2011 7:06:05 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,135) 3 4



Votes:

0

Your Vote:

Up

Down

I'm getting Status: down and Message as Timeout. I 've got the database as Master is that correct? I've put the instance name in which is correct.

Created on Jun 29, 2011 8:33:07 PM by  braveboy (0) 1



Votes:

0

Your Vote:

Up

Down

The database should be the database in which you created the stored procedure.

Also make sure to use credentials that have sufficient rights to execute the stored procedure.

Created on Jun 30, 2011 5:07:39 PM by  PRTG Tools Family [prtgtoolsfamily.com] (13,135) 3 4



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.