Monday, March 31, 2014

TSQL : Login Failures

Looking at a migrated server, I found the SQL Logs were full of login failures. I wanted to retrieve the list of IP addresses involved and hence import the log messages into SQL itself. The script below is how I achieved this.
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SQLErrorlog]'))
BEGIN
CREATE TABLE [dbo].[SQLErrorlog](
    [LogDate] [datetime] NULL,
    [ProcessInfo] [varchar](10) NULL,
    [Text] [varchar](1000) NULL,
 [ID] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE NAME = 'PK_SQLErrorlog' AND Type = 'PK')
 BEGIN
 ALTER TABLE [dbo].[SQLErrorlog] ADD CONSTRAINT PK_SQLErrorlog PRIMARY KEY CLUSTERED (ID) ON [PRIMARY]
 END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE NAME = 'NCI_SQLErrorLog_Text')
 BEGIN
 CREATE NONCLUSTERED INDEX [NCI_SQLErrorLog_Text] ON [dbo].[SQLErrorlog] ([Text] ASC) ON [PRIMARY]
 END

INSERT INTO [dbo].[SQLErrorlog]
-- All Login Failures
EXEC sp_readerrorlog 0,1, 'Login Failed' 
-- Could put all Error log entries in the table like this
--EXEC sp_readerrorlog 0,1

-- Retrieve Client that failed to connect
SELECT    [LogDate]
  , REPLACE(RIGHT(Text,1+LEN(Text)-CHARINDEX('CLIENT:',Text)),']','') AS Client 
FROM [dbo].[SQLErrorlog] 
WHERE [Text] LIKE 'Login Failed%'

Links -
MSSQLTips : Reading the SQL Server log files using TSQL
MSDN : How to archive your sql error logs into a table

No comments: