“Login Failed” and using PowerShell to collect events from multiple SQL Server’s Errorlog

There is a common phenomenon of the requirement to capture failed login attempts to a database server. When a login attempt fails, it simply means that the login to a database server was unsuccessful. Some of the reasons for this include a mismatched password, a login that does not exist, or the default login database is offline or has been removed.

Although capturing “Login Failed” events can give a strong sense of security, it is equally as important to capture successful logins in order to understand the location and activities of those who have logged in. A failed login will never cause any data breaching issues, but a successful login can.

There are a few different ways a successful or unsuccessful login events can be captured, such as the following:

1. SQL Server Audit
2. Extended Event
3. SQL Profiler Trace
4. SQL Server Errorlog

Using SQL Errorlog:

This is one of the most commonly used and easy to implement mechanisms for capturing login events from a SQL Server instance. The SQL Server setting “Failed Login Only” can be configured for two separate reasons: to troubleshoot login failures and to understand if there were any unauthorized login attempts was made to the SQL Server Instance.

Although this particular feature exists for all SQL Server editions, the new feature introduces “Login failed” messages that are always included in in the new editions. This feature can aid with quicker audits and more efficient troubleshooting.

To find a failed login, use the system sp_readerrolrog stored procedure:

sp_readerrorlog 0, 1, 'Login failed' -- Querying current errorlog
sp_readerrorlog 1, 1, 'Login failed' -- Querying previous errorlog

Login failure message:

Some examples of login failure messages that can be found in the SQL Server Errorlog:

• Login failed for user 'AppUser'. Reason: Password did not match that for the login provided. [CLIENT: 15.201.210.170]
• Login failed for user 'MYDOMAIN\SalesSvc'. Reason: Could not find a login matching the name provided. [CLIENT: 101.20.104.45]
• Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: 110.110.211.110]
• Login failed for user 'ReportSvc'. Reason: Failed to open the explicitly specified database 'TestDB'. [CLIENT: 110.221.74.120]
• Login failed for user 'MYDOMAIN\HL7Svc'. Reason: Failed to open the explicitly specified database 'HL7ProdDB'. [CLIENT: <local machine>]
• Login failed for user 'TestUser'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

DEFAULT_DATABASE Login Parameter:

When we create a login in SQL Server, a default database name is always required. Up to SQL 2005, the default database could not be specified, so the ultimate login database became the master database. In SQL 2008 and up, the default database name can be specified with the “DEFAULT_DATABASE” clause. While this option enhances security, using this option unwisely or unexpectedly may cause application login failure.

An Example:

Let’s say that an application needs a database. A SQL or a Windows Login will be authorized to access the database and the login has been granted certain permissions for the DML operation. In this case, instead of the master database, the user database is used as the default database for the login.

Later on, suppose a few more databases have been created on the same instance of the SQL Server and the same login is used with a different set of permissions for each database.

Technically, there won’t be any issues until the default database is taken offline or removed from the server. Once this happens, all other applications will start failing to connect to the SQL Server with the following message:

Login failed for user '<Login Name>’. Reason: Failed to open the explicitly specified database '<User database>'. [CLIENT: <IP> or <local machine>]

Unavailability of default login database:

A SQL Server Login (SQL, Windows Domain Account, Windows Domain Group) needs a default database and if the default login database is removed, renamed, taken offline or single user mode is enabled, then the login will fail. While the user or application is connected to the SQL Server, the user will remain connected to the database server despite the database being taken offline during their session. However, once the user disconnects and attempts to login in again, then it will fail to connect.

To resolve the login issue, the following command will change the default login database to master:

-- Use any one of the following
ALTER LOGIN <Login Name> WITH DEFAULT_DATABASE = master
EXEC sp_defaultdb '<Login Name>', 'master'

Usages of default login:

When multiple applications or users need to connect to a SQL Server with the same login, it might be a good idea to use master database as the default login database. When a specific login needs to connect to a specific database, then the default database should be same user database.

----- Find all logins and default database
SELECT p.[name] login_name,
       p.type_desc,
       p.default_database_name,      
	   DATABASEPROPERTYEX(p.default_database_name, 'Status') AS database_status
FROM   sys.server_principals p
WHERE p.type_desc IN ('WINDOWS_LOGIN','SQL_LOGIN','WINDOWS_GROUP')  

-- Change default login database to master
-- Use any one of the following
ALTER LOGIN <Login Name> WITH DEFAULT_DATABASE = master
EXEC sp_defaultdb <Login Name>, 'master'

Collecting login failure event:

It is often required to collect login failure events and preserve them for analysis. There are several methods available to do this. Following is a PowerShell script to specifically collect the “Login Failed” events from one or more SQL Server instances and then preserve those events in a SQL Server Database.

The script uses a checkpoint method so that the event will not be collected twice.

PowerShell Script:

<##########################################################
-- Table to export login faulure event from PowerShell
CREATE TABLE [dbo].[EventTbl](
	[EventID] [bigint] IDENTITY(1,1) NOT NULL,
	[EventDate] [datetime] NULL,
	[EventType] [varchar](50) NULL,
	[EventSource] [varchar](100) NULL,
	[EventText] [varchar](8000) NULL
) ON [PRIMARY]
GO

##########################################################>

#Clear-Host

# Database server to export login failure events 
$database = 'TestDB'
$server = 'SHBMAIN\SQL2019'
$table = 'dbo.EventTbl'

[String[]]$sqlInstanaces = "SHBMAIN\SQL2008","SHBMAIN\SQL2019","SHBMAIN\SQL2017"
[Int]$nInitialDays = 15
[DateTime]$InitialStart = $(Get-Date).AddDays(-$nInitialDays)

# Load the checkpoint file
$PSScriptRoot = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
$path = "$PSScriptRoot"
$CheckPointFile = $path + "\Checkpoint.txt"

# Retrieve last scan date/time from Checkpoint file
if (! (Test-Path $CheckPointFile)) # Checkpoint file not found
{
	$InitialStart | Set-Content -Force $CheckPointFile
	$LastCheckpoint = $InitialStart
	Write-Host 'Checkpoint file does NOT exist'
	Write-Host "Last checkpoint: $LastCheckpoint"
}
else # the Checkpoint file does exist 
{
	[DateTime]$LastCheckpoint = Get-Content -Force $CheckPointFile
	Write-Host 'Checkpoint file does exist'
	Write-Host "Last checkpoint : $LastCheckpoint"
}

# Next check point date and time
$NextCheckpointTime = Get-Date
$NextCheckpointTime | Set-Content -Force $CheckPointFile

Write-Host "Next Checkpoint: $NextCheckpointTime"

$match    =  'Login failed'
$notmatch  =  'without errors|found 0 errors'

# multiple filter for other events
# $match    =  'Login failed|requests taking longer|is full'
# $notmatch  =  'without errors|found 0 errors|backup'

# $result =@()
foreach ($sqlInstanace in $sqlInstanaces)
{
	# create an empty object
    $obj = "" | select Date, Source, ServerInstance, Text

    # Collect errorlog and prepare for insert process
	$obj = Get-SqlErrorLog -ServerInstance $sqlInstanace `
            -After $LastCheckpoint -Before $NextCheckpointTime `
            -ErrorAction SilentlyContinue `
	| ? { ($_.Text -match $match) -and ($_.Text -notmatch $notmatch) } | `
        select Date, Source, ServerInstance, @{ n = "Text"; e = { $_.Text.Replace("'", "''") } }
	
    # if object is not empty
	if ($obj)
	{
        Write-Host "Number of records: $($obj.count). Exporting log for $sqlInstanace to SQL Server $server"
		$obj | ForEach-Object {
			Invoke-Sqlcmd `
			-Database $database -ServerInstance $server `
			-Query "insert into $table VALUES ('$($_.Date)','$($_.Source)','$($_.ServerInstance)','$($_.Text)') "
		}
	}
	# $result += $obj			
}

# $result |ft

Executing PowerShell Script:

Querying table: