Auditing SQL Server Systems - Logins:
Auditing SQL Server Systems for Login Failures is a very common item on every DBA's task list.
To view the current SQL Server configuration for Login Auditing, in SQL Server Management Studio connect to the Instance, right click the Instance Name and select Properties, then select Security, then Login Auditing. There are four options available for configuration:
#1. None - No Login Auditing/Logging.
#2. Failed Logins Only - Failed logins will be Audited/Logged, Successful Logins will be ignored.
#3. Successful Logins Only - Successful logins will be Audited/Logged, Failed Logins will be ignored.
#4. Both Failed and Successful Logins - All Login will be Audited/Logged.
If you select Successful Logins it can, on some Systems, generate extensive Login Events entered and create a large log file. Make sure to monitor that activity over time.
To View the Current SQL Server Log File and filter on Failed Logins:
EXEC sp_readerrorlog 0, 1, 'Login failed'
2017-06-28 15:41:50.290 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x]
To view the code for sp_readerrorlog, execute the T-SQL code below:
sp_helptext 'sp_readerrorlog'
create proc sys.sp_readerrorlog(
@p1 int = 0,
@p2 int = NULL,
@p3 nvarchar(4000) = NULL,
@p4 nvarchar(4000) = NULL)
as
begin
if (not is_srvrolemember(N'securityadmin') = 1)
begin
raiserror(15003,-1,-1, N'securityadmin')
return (1)
end
if (@p2 is NULL)
exec sys.xp_readerrorlog @p1
else
exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end
Resources:
|
|
|
|
|
|