What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

create sensor to monitor sql logins

Votes:

0

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



Best Answer

Accepted Answer

Votes:

0

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

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



10 Replies

Votes:

0

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

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

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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




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.