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

Article Comments

hallo,

you could try with an eventlog sensor.


Jun, 2011 - Permalink

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.


Jun, 2011 - Permalink

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...


Jun, 2011 - Permalink

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!


Jun, 2011 - Permalink

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'.


Jun, 2011 - Permalink

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.


Jun, 2011 - Permalink

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?


Jun, 2011 - Permalink

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

exec spLoginAttempts

Jun, 2011 - Permalink

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.


Jun, 2011 - Permalink

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.


Jun, 2011 - Permalink