#Requires -Version 5.1
<#
    JbTecWiz Support Centre -- generated fix script

    Fault : SQL Server error 18456 -- login failed for user
    Fix   : Read the state, then fix the login
    Source: https://jbtecwiz.com/support/srv-sql-18456

    Run as     : SQL Server Management Studio or sqlcmd
    Expect     : 30 minutes
    Risk       : medium
    Reversible : yes

    WHEN THIS IS THE RIGHT FIX
    Any 18456. Always read the state first -- it turns this from guesswork
    into one action.

    HOW TO UNDO IT
    ALTER LOGIN ... DISABLE, or DROP LOGIN, reverses anything created here.

    This script walks the fix one step at a time and asks before each
    one. Steps with no command are things you do yourself -- it prints
    those and waits. Run with -DryRun to print without executing.

    --------------------------------------------------------------------
    NO WARRANTY - USE AT YOUR OWN RISK

    This script is provided by JbTecWiz as-is and with no warranty of any
    kind, express or implied. You run it entirely at your own risk.

    JbTecWiz accepts no liability for any loss or damage arising from its
    use, including but not limited to data loss, downtime, or configuration
    changes that turn out to be wrong for your system.

    You are responsible for reading this script before running it, for
    satisfying yourself that it suits the machine in front of you, and for
    having a working backup first. Some steps cannot be undone.
    --------------------------------------------------------------------
#>

[CmdletBinding()]
param(
    # Print every step and command without running anything.
    [switch]$DryRun,
    # Do not ask before each step. Read the script first if you use this.
    [switch]$Unattended
)

$ErrorActionPreference = 'Stop'
$script:Failed = 0

function Write-Rule { param([string]$Text)
    Write-Host ''
    Write-Host ('-' * 70) -ForegroundColor DarkGray
    if ($Text) { Write-Host $Text -ForegroundColor Cyan }
}

function Show-Prose { param([string]$Text, [string]$Colour = "Gray")
    if (-not $Text) { return }
    $words = $Text -split "\s+"; $line = "  "
    foreach ($w in $words) {
        if (($line.Length + $w.Length) -gt 74) { Write-Host $line -ForegroundColor $Colour; $line = "  " }
        $line += "$w "
    }
    if ($line.Trim()) { Write-Host $line -ForegroundColor $Colour }
}

function Invoke-Step {
    param(
        [int]$Number, [string]$Do, [string]$Why,
        [scriptblock]$Command, [switch]$Manual, [string]$Shell = "powershell"
    )
    Write-Rule "  Step $Number of 6"
    Show-Prose $Do "White"
    if ($Why) { Write-Host ""; Show-Prose $Why "DarkGray" }

    if ($Manual) {
        Write-Host ''
        Write-Host '  -> Do this yourself, then press Enter to carry on.' -ForegroundColor Yellow
        if (-not $Unattended -and -not $DryRun) { [void](Read-Host) }
        return
    }

    Write-Host ''
    foreach ($l in ($Command.ToString().Trim() -split "`n")) {
        Write-Host ("    " + $l.Trim()) -ForegroundColor Green
    }
    Write-Host ''

    if ($DryRun) { Write-Host "  (dry run -- not executed)" -ForegroundColor DarkGray; return }
    if (-not $Unattended) {
        $a = Read-Host "  Run this step? [Y]es / [S]kip / [Q]uit"
        if ($a -match "^[Qq]") { Write-Host "  Stopped at your request."; exit 0 }
        if ($a -match "^[Ss]") { Write-Host "  Skipped." -ForegroundColor DarkGray; return }
    }
    try { & $Command }
    catch {
        $script:Failed++
        Write-Host ("  Step $Number failed: " + $_.Exception.Message) -ForegroundColor Red
        Show-Prose "The rest of the fix may depend on this. Read the write-up before carrying on." "Red"
        if (-not $Unattended) {
            $c = Read-Host "  Carry on anyway? [y/N]"
            if ($c -notmatch "^[Yy]") { exit 1 }
        }
    }
}

Write-Rule
Write-Host '  SQL Server error 18456 -- login failed for user' -ForegroundColor White
Write-Host '  Read the state, then fix the login' -ForegroundColor Cyan
Write-Host ''
Write-Host '  Risk: medium   Reversible   30 minutes'
Write-Host ''
Show-Prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.' 'DarkYellow'
Write-Rule

if (-not $Unattended -and -not $DryRun) {
    $go = Read-Host '  Ready? [y/N]'
    if ($go -notmatch "^[Yy]") { Write-Host "  Nothing was changed."; exit 0 }
}

Invoke-Step -Number 1 -Do 'Read the server''s own error log, which is the only place the real state appears.' -Why 'The client is told State 1 on purpose, so an attacker cannot tell ''no such user'' from ''wrong password''. The server log has no such restriction.' -Command {
    cmd.exe /c 'EXEC xp_readerrorlog 0, 1, N''Login failed'';'
}

Invoke-Step -Number 2 -Do 'Confirm the server accepts SQL logins at all. Mode 1 is Windows-only, 2 is mixed.' -Command {
    cmd.exe /c 'SELECT SERVERPROPERTY(''IsIntegratedSecurityOnly'') AS WindowsAuthOnly;'
}

Invoke-Step -Number 3 -Do 'If it is Windows-only and you need a SQL login, switch to mixed mode -- this needs a service restart.' -Command {
    cmd.exe /c 'EXEC xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''Software\Microsoft\MSSQLServer\MSSQLServer'', N''LoginMode'', REG_DWORD, 2;'
}

Invoke-Step -Number 4 -Do 'Check the login exists and is not disabled or locked out.' -Command {
    cmd.exe /c 'SELECT name, is_disabled, LOGINPROPERTY(name,''IsLocked'') AS IsLocked, LOGINPROPERTY(name,''IsExpired'') AS IsExpired FROM sys.server_principals WHERE type IN (''S'',''U'',''G'');'
}

Invoke-Step -Number 5 -Do 'Enable and unlock as needed.' -Command {
    cmd.exe /c 'ALTER LOGIN [appuser] ENABLE; & ALTER LOGIN [appuser] WITH PASSWORD = ''NewStrongPassword'' UNLOCK;'
}

Invoke-Step -Number 6 -Do 'For state 11 or 12, the Windows account authenticated but has no SQL login -- create one.' -Command {
    cmd.exe /c 'CREATE LOGIN [EXAMPLE\AppService] FROM WINDOWS;'
}

Write-Rule "  Confirm it worked"
Show-Prose 'The application connects and the log records a successful login.' 'White'
Write-Host ''
if (-not $DryRun) {
    cmd.exe /c 'EXEC xp_readerrorlog 0, 1, N''Login succeeded'';'
}

Write-Rule
if ($script:Failed -gt 0) {
    Write-Host ("  Finished with " + $script:Failed + " failed step(s).") -ForegroundColor Yellow
    Show-Prose 'Read the full write-up at https://jbtecwiz.com/support/srv-sql-18456' 'Yellow'
} else {
    Write-Host '  Finished.' -ForegroundColor Green
}
Write-Host ''
Show-Prose 'To undo: ALTER LOGIN ... DISABLE, or DROP LOGIN, reverses anything created here.' 'DarkGray'
Write-Rule