시스템 관리_34 SQL Server Error 18456 Login failed for user 로그인 실패 오류 원인 분석 및 State 코드별 해결 가이드

# 1. 클라이언트 관점: sqlcmd 명령어로 접속 시도 및 오류 메시지 확인
$ sqlcmd -S 192.168.1.100 -U appuser -P "WrongPassword123!"
# Msg 18456, Level 14, State 1, Server SQLSRV01, Line 1
# Login failed for user 'appuser'.
# -> 클라이언트에는 보안상 항상 'State 1'로만 표시됨 (실제 State는 서버 에러로그에 기록됨)

# 2. 리눅스 환경: SQL Server ERRORLOG 파일 경로 확인 및 최근 로그인 실패 로그 추적
$ sudo tail -n 50 /var/opt/mssql/log/errorlog | grep -E "18456|Login failed"
# [출력 예시 - 암호 불일치 (State 8)]
# 2026-10-16 10:14:22.15 Logon       Error: 18456, Severity: 14, State: 8.
# 2026-10-16 10:14:22.15 Logon       Login failed for user 'appuser'. Reason: Password did not match that for the login provided. [CLIENT: 192.168.1.50]

# [출력 예시 - 계정 없음 (State 5)]
# 2026-10-16 10:15:01.32 Logon       Error: 18456, Severity: 14, State: 5.
# 2026-10-16 10:15:01.32 Logon       Login failed for user 'guest_user'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.1.50]

# [출력 예시 - 혼합 인증 모드(Mixed Mode) 미활성화 (State 58)]
# 2026-10-16 10:16:11.89 Logon       Error: 18456, Severity: 14, State: 58.
# 2026-10-16 10:16:11.89 Logon       Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: 192.168.1.50]

# 3. 윈도우 환경: PowerShell을 통해 ERRORLOG 내 18456 오류 필터링
# Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG" | Select-String "18456" -Context 0,1

# 4. 관리자 계정으로 sys.server_principals를 조회하여 계정 활성 및 잠금 상태 점검
$ sqlcmd -S localhost -U sa -P 'MyStrongPass!' -Q "
SELECT name, is_disabled, create_date, modify_date 
FROM sys.server_principals 
WHERE name = 'appuser';
"

#!/bin/bash
# diagnose_mssql_18456.sh - Parse SQL Server ERRORLOG and explain Error 18456 State Codes

ERRORLOG_PATH=${1:-"/var/opt/mssql/log/errorlog"}

echo "=========================================="
echo " [SQL Server Error 18456 State 진단기]"
echo " 대상 로그 경로: ${ERRORLOG_PATH}"
echo "=========================================="

if [ ! -f "$ERRORLOG_PATH" ]; then
    echo "  [오류] ERRORLOG 파일을 찾을 수 없습니다: ${ERRORLOG_PATH}"
    echo "  -> 사용법: sudo $0 [ERRORLOG 경로]"
    exit 1
fi

echo -e "\n[1] 최근 감지된 Error 18456 로그인 실패 내역 (최근 5건):"
RECENT_ERRORS=$(grep -n "Error: 18456" "$ERRORLOG_PATH" | tail -n 5)

if [ -z "$RECENT_ERRORS" ]; then
    echo "  [정상] 로그 파일 내에 Error 18456 기록이 없습니다."
    exit 0
fi

while read -r LINE; do
    LINE_NO=$(echo "$LINE" | cut -d':' -f1)
    LOG_TEXT=$(echo "$LINE" | cut -d':' -f2-)
    
    # State 코드 추출
    STATE_CODE=$(echo "$LOG_TEXT" | grep -oE "State: [0-9]+" | awk '{print $2}')
    # 다음 줄(이유 및 계정/클라이언트 IP 정보) 추출
    NEXT_LINE=$(sed -n "$((LINE_NO + 1))p" "$ERRORLOG_PATH")
    USER_NAME=$(echo "$NEXT_LINE" | grep -oE "user '[^']+'" | cut -d"'" -f2)
    CLIENT_IP=$(echo "$NEXT_LINE" | grep -oE "\[CLIENT: [^]]+\]")

    echo "------------------------------------------------------------------"
    echo "  발생 시각 및 로그 : $LOG_TEXT"
    echo "  대상 계정 / 클라이언트 : ${USER_NAME:-"알 수 없음"} / ${CLIENT_IP:-"[Local]"}"
    echo "  검출된 State 코드     : State ${STATE_CODE}"
    echo "  [원인 및 해결 가이드]:"

    case "$STATE_CODE" in
        2)
            echo "    -> [원인] 유효하지 않은 사용자 ID (User ID is not valid)."
            echo "    -> [조치] 로그인 계정 철자 오타를 확인하거나 계정을 새로 생성하십시오."
            ;;
        5)
            echo "    -> [원인] 존재하지 않는 로그인 계정 (User ID doesn't exist)."
            echo "    -> [조치] sys.server_principals 목록에서 계정 존재 여부를 확인하십시오."
            ;;
        6)
            echo "    -> [원인] Windows 도메인 로그인 이름 해석 실패."
            echo "    -> [조치] Active Directory 도메인 컨트롤러 연결 상태 및 계정명을 확인하십시오."
            ;;
        7)
            echo "    -> [원인] 계정이 비활성화(Disabled) 상태입니다."
            echo "    -> [조치] ALTER LOGIN [계정명] ENABLE; 명령으로 활성화하십시오."
            ;;
        8)
            echo "    -> [원인] 비밀번호 불일치 (Password did not match)."
            echo "    -> [조치] 입력한 패스워드를 재확인하거나 ALTER LOGIN으로 재설정하십시오."
            ;;
        11|12)
            echo "    -> [원인] 기본 데이터베이스(Default Database) 접근 권한 없음 또는 오프라인 상태."
            echo "    -> [조치] 계정의 Default DB 상태(ONLINE)를 확인하거나 ALTER LOGIN [계정명] WITH DEFAULT_DATABASE = [master]; 로 변경하십시오."
            ;;
        16)
            echo "    -> [원인] 로그인 계정에 DB 엔진 접속 권한이 거부됨 (CONNECT SQL Denied)."
            echo "    -> [조치] GRANT CONNECT SQL TO [계정명]; 명령을 실행하십시오."
            ;;
        38)
            echo "    -> [원인] 데이터베이스가 단일 사용자 모드(SINGLE_USER)로 설정되어 있어 이미 다른 세션이 선점함."
            echo "    -> [조치] DB를 MULTI_USER 모드로 변경하거나 선점 중인 세션을 정리하십시오."
            ;;
        58)
            echo "    -> [원인] 서버가 Windows 인증 전용으로 설정되어 SQL Server 인증을 거부함."
            echo "    -> [조치] 서버 인증 모드를 'SQL Server 및 Windows 인증 모드(Mixed Mode)'로 변경 후 서비스를 재기동하십시오."
            ;;
        *)
            echo "    -> [안내] 세부 원인 로그를 참조하십시오: $NEXT_LINE"
            ;;
    esac
done <<< "$RECENT_ERRORS"

echo -e "\n=========================================="
echo " [진단 완료]"
echo "=========================================="