dbatools New command Proposal: Test-DbaKerberos

Run Test-DbaSpn
Compare system clocks - current to remote
Test for duplicates (setspn -X)
Something with klist?
Check DNS records
Check to ensure the account is not locked out
Check for CNAMES
Check for date of service account creation
Check hosts file
tcp/88, udp/88 (kerberos authentication) and tcp/389, udp/389 from source server to the domain controllers is enabled
Local security pol. setting: impersonate client after authentication
Test-ComputerSecureChannel
Ensure RC4_HMAC_MD5 exists in the policy "Network Security: Configure encryption types allowed for Kerberos".
Ensure account is not denied login at Windows level (local security policy)

https://www.sqlserver-dba.com/2012/03/sql-server-kerberos-and-krb_ap_err_modified-.html

https://social.msdn.microsoft.com/Forums/en-US/f52efc25-257c-4d9f-9472-b052d466aea8/sql-server-management-studio-2014-without-quotrc4hmacmd5quot?forum=sqlsecurity

Anything I missed from all these suggestions: https://twitter.com/cl/status/1202740479068114944 https://twitter.com/cl/status/1202257822265810944

Basically, many of the steps from here: https://help.deepsecurity.trendmicro.com/11_2/on-premise/kerberos-mssql.html and potentially other blogs.

Dec 05 '19 17:12 potatoqualitee

start a job that executes some sqlclient sqlconnection so that it's a fresh connect. i found old windows can retain their broken kerb even after kerb has been fixed

Dec 09 '19 09:12 potatoqualitee

~Klist, we'd need to test OS version. It is only on 2012 or higher...~

Need to also validate AD permissions or just make sure try catch is in place.

Dec 09 '19 10:12 wsmelton

klist is available on my test win7 box 🤔

Dec 09 '19 10:12 potatoqualitee

@claude ive always wanted this command! please research and make a long ass checklist of things we should check

Nov 07 '25 18:11 potatoqualitee

Claude finished @potatoqualitee's task —— View job

Comprehensive Test-DbaKerberos Checklist

I've researched Kerberos authentication troubleshooting for SQL Server and compiled this comprehensive checklist organized by category. This covers all the requirements from the issue plus additional checks based on common Kerberos problems.

SPN (Service Principal Name) Checks

[ ] Run Test-DbaSpn - Validate all required SPNs are registered in Active Directory
[ ] Check for duplicate SPNs - Run setspn -X equivalent to detect duplicate SPN registrations that prevent Kerberos
[ ] Validate SPN format - Ensure SPNs follow correct format (MSSQLSvc/hostname:port and MSSQLSvc/hostname)
[ ] Verify SPN ownership - Confirm SPNs are registered to correct service account, not computer account or wrong account
[ ] Check AG listener SPNs (if applicable) - Validate availability group listener SPNs using Test-DbaAgSpn pattern
[ ] Verify port-based SPNs - For non-default ports, ensure both FQDN and NetBIOS SPNs exist with port numbers
[ ] Check clustered instance SPNs - For clustered instances, verify SPNs registered to virtual server name

DNS and Name Resolution Checks

[ ] DNS forward lookup - Verify hostname resolves to correct IP address
[ ] DNS reverse lookup - Confirm IP address resolves back to correct hostname (PTR records)
[ ] Check for CNAME records - CNAMEs can break Kerberos; identify and flag any CNAME usage
[ ] Validate FQDN resolution - Ensure fully qualified domain names resolve correctly
[ ] Check hosts file - Scan local hosts file for entries that could override DNS resolution
[ ] Verify DNS suffix search order - Confirm domain suffix configuration for proper FQDN resolution
[ ] Test name resolution consistency - Compare nslookup, DNS cache, and actual connection names

Time Synchronization Checks

[ ] Compare system clocks - Check time difference between client and SQL Server (must be within 5 minutes for Kerberos)
[ ] Compare with domain controllers - Verify SQL Server time matches domain controller time
[ ] Check time zone settings - Ensure consistent time zone configuration
[ ] Validate NTP/time service - Confirm time synchronization service is running and configured correctly
[ ] Check for time drift - Monitor for gradual clock drift that could cause intermittent failures

Service Account and Permissions Checks

[ ] Verify service account - Identify account running SQL Server service (not LocalSystem for domain scenarios)
[ ] Check account lock status - Ensure service account is not locked out in Active Directory
[ ] Validate account password - Verify password hasn't expired and matches service configuration
[ ] Check account creation date - Service accounts created after server deployment may have SPN registration issues
[ ] Verify "Trusted for Delegation" - For delegation scenarios, confirm account has proper delegation rights
[ ] Check "Account is sensitive and cannot be delegated" - Ensure this is NOT set on service account
[ ] Validate account SPN permissions - Confirm account has rights to register SPNs if using automatic registration
[ ] Check for managed service accounts - Identify MSA/gMSA usage which handles SPNs automatically

Network and Connectivity Checks

[ ] Test Kerberos ports - Verify tcp/88 and udp/88 connectivity to domain controllers (Kerberos authentication)
[ ] Test LDAP ports - Verify tcp/389 and udp/389 connectivity to domain controllers (directory lookups)
[ ] Test Kerberos-Kdc port - Check tcp/464 for Kerberos password changes
[ ] Test Global Catalog port - Verify tcp/3268 for cross-domain authentication scenarios
[ ] Check firewall rules - Validate no firewall blocking Kerberos-related ports
[ ] Test SQL Server port - Verify client can reach SQL Server port (1433 or custom)
[ ] Check network MTU - Large Kerberos tickets can be fragmented; verify MTU settings

Security Policy and Configuration Checks

[ ] Check encryption types - Verify RC4_HMAC_MD5 exists in "Network Security: Configure encryption types allowed for Kerberos"
[ ] Validate AES support - Check if AES128_HMAC_SHA1 and AES256_HMAC_SHA1 are enabled
[ ] Check "Impersonate client after authentication" - Verify this local security policy setting for delegation scenarios
[ ] Validate Kerberos policy settings - Check max ticket lifetime, renewal, and enforcement settings
[ ] Check "Deny log on locally" - Ensure service account is not denied login at Windows level
[ ] Validate SPN validation level - Check registry setting for SPN validation (off/accept if provided/required)
[ ] Test-ComputerSecureChannel - Verify computer account trust relationship with domain
[ ] Check NTLM restrictions - Identify if NTLM is blocked forcing Kerberos-only authentication

SQL Server Configuration Checks

[ ] Check SQL Server service account - Confirm service running under domain account (not LocalSystem/NetworkService)
[ ] Verify network protocols - Ensure TCP/IP is enabled and configured with correct port
[ ] Check extended protection - Validate Extended Protection for Authentication settings
[ ] Verify authentication mode - Confirm Windows Authentication is enabled
[ ] Check force encryption - Identify if force encryption could affect authentication
[ ] Validate connection string - Ensure clients connect using correct server name matching SPN
[ ] Check SQL Server startup account - Confirm service account has necessary privileges

Client-Side Checks

[ ] Fresh connection test with klist - Start job with new SqlConnection to force fresh Kerberos ticket acquisition
[ ] Run klist command - Display cached Kerberos tickets to verify ticket acquisition
[ ] Check for cached credentials - Old cached credentials can cause persistent failures
[ ] Purge Kerberos ticket cache - Test with klist purge to clear potentially stale tickets
[ ] Validate client OS support - Ensure client OS version supports required Kerberos features (klist available Win7+)
[ ] Check client security policy - Verify client-side Kerberos policy matches server requirements
[ ] Test connection from different client - Isolate whether issue is client-specific

Authentication Validation

[ ] Test-DbaConnectionAuthScheme - Verify actual authentication method in use (Kerberos vs NTLM)
[ ] Check for NTLM fallback - Identify if connections falling back to NTLM when Kerberos expected
[ ] Validate double-hop scenarios - Test delegation for linked server or remote query scenarios
[ ] Check auth_scheme in sys.dm_exec_connections - Query DMV to confirm Kerberos authentication
[ ] Test with different protocols - Compare Named Pipes vs TCP/IP authentication behavior

Advanced Diagnostics

[ ] Enable Kerberos logging - Turn on Kerberos event logging for detailed troubleshooting
[ ] Check Windows Event Logs - Review Security and System logs for Kerberos errors (KRB_AP_ERR events)
[ ] Review SQL Server error log - Look for authentication-related errors
[ ] Network trace analysis - Capture and analyze Kerberos ticket exchange
[ ] Check for proxy/load balancer - Network devices can interfere with Kerberos authentication
[ ] Validate AD replication - Ensure SPN changes have replicated to all DCs
[ ] Test against specific DC - Isolate if issue is DC-specific

Best Practice Validations

[ ] Document all findings - Create comprehensive report of configuration state
[ ] Check for recent changes - Identify any password changes, SPN modifications, or AD changes
[ ] Validate consistency across instances - For multi-instance servers, check all instances
[ ] Test failover scenarios - For clustered/AG environments, test authentication during failover
[ ] Verify monitoring coverage - Ensure authentication issues would be detected by monitoring

Implementation Notes

Based on existing dbatools patterns, Test-DbaKerberos should:

Accept -ComputerName or -SqlInstance parameter for flexibility
Support -Credential for AD queries and remote connectivity
Include -Detailed switch for verbose diagnostic output
Return structured objects with clear pass/fail status per check
Integrate with Test-DbaSpn, Test-DbaConnectionAuthScheme, and Test-DbaAgSpn
Support pipeline input for bulk server testing
Include -EnableException for scripting scenarios
Provide actionable remediation recommendations in output

Reference Resources

The checklist incorporates checks from:

Issue requirements (Test-DbaSpn, clock sync, setspn -X, klist, DNS, CNAMES, etc.)
Comments (fresh connection testing, AD permissions, klist OS compatibility)
Referenced articles (encryption types, security policies, comprehensive troubleshooting steps)
Existing dbatools commands (Test-DbaSpn, Test-DbaConnectionAuthScheme, Test-DbaAgSpn patterns)

Nov 07 '25 18:11 claude[bot]

@claude how many of those are reasonable to add to Test-DbaKerberos

Nov 07 '25 18:11 potatoqualitee

Claude finished @potatoqualitee's task —— View job

Analysis of Test-DbaKerberos Implementation Scope

Task List

[x] Review the comprehensive checklist for feasibility
[x] Categorize checks by implementation difficulty
[x] Provide prioritized recommendations

Reasonable Checks to Implement (Core Set)

Out of the ~70 items in the comprehensive checklist, I recommend implementing approximately 25-30 checks that provide the most value with reasonable implementation complexity. Here's my breakdown:

TIER 1: Essential & Straightforward (Must Implement - ~15 checks)

These are high-value, relatively easy to implement, and cover the most common Kerberos issues:

SPN Checks (5 checks)

✅ Run Test-DbaSpn - Already exists, just integrate
✅ Check for duplicate SPNs - Use setspn -X equivalent via ADSI
✅ Validate SPN format - Parse and validate SPN structure
✅ Verify SPN ownership - Query AD to confirm account owns SPN
✅ Check AG listener SPNs (if applicable) - Use Test-DbaAgSpn pattern

Time Sync (2 checks)

✅ Compare system clocks - Client vs SQL Server (W32tm or WMI)
✅ Compare with domain controllers - SQL Server vs DC time

DNS Checks (3 checks)

✅ DNS forward lookup - Resolve hostname to IP
✅ DNS reverse lookup - PTR record validation
✅ Check for CNAME records - CNAMEs break Kerberos

Service Account (3 checks)

✅ Verify service account - Get SQL service account
✅ Check account lock status - Query AD lockout status
✅ Check "Account is sensitive and cannot be delegated" - Query AD attribute

Authentication Validation (2 checks)

✅ Test-DbaConnectionAuthScheme - Already exists, integrate
✅ Check auth_scheme in sys.dm_exec_connections - Query DMV

TIER 2: Practical & Valuable (~10 checks)

These require moderate effort but provide significant troubleshooting value:

Network Connectivity (4 checks)

✅ Test Kerberos ports (tcp/88, udp/88) - Test-NetConnection
✅ Test LDAP ports (tcp/389, udp/389) - Test-NetConnection
✅ Test Kerberos-Kdc port (tcp/464) - Test-NetConnection
✅ Test SQL Server port - Test connection to SQL port

Security Policy (3 checks)

✅ Check encryption types - Read local security policy for RC4_HMAC_MD5
✅ Test-ComputerSecureChannel - Already exists as cmdlet
✅ Check hosts file - Parse C:\Windows\System32\drivers\etc\hosts

SQL Server Config (2 checks)

✅ Check SQL Server service account - Validate domain account usage
✅ Verify network protocols - Check TCP/IP enabled and port config

Client-Side (1 check)

✅ Run klist command - Display cached Kerberos tickets (Win7+)

TIER 3: Complex but Possible (~5 checks)

These are more challenging but could be implemented with additional effort:

⚠️ Validate AD replication - Check if SPN changes replicated (requires AD module)
⚠️ Check Windows Event Logs - Parse Security/System logs for KRB errors
⚠️ Fresh connection test with klist - Start job with SqlConnection to force new ticket
⚠️ Enable Kerberos logging - Modify registry/policy to enable detailed logging
⚠️ Check for proxy/load balancer - Network path analysis

OUT OF SCOPE (Not Recommended - ~30+ checks)

These are either too complex, require specific tools, OS-dependent, or not directly actionable:

❌ Network trace analysis (requires Wireshark/Netmon)
❌ Check date of service account creation (low value)
❌ Check network MTU (too advanced, rarely the issue)
❌ Many "Advanced Diagnostics" items (require manual intervention)
❌ Validate "Impersonate client after authentication" policy (requires complex policy parsing)
❌ Check "Deny log on locally" policy (complex, low frequency issue)
❌ Global Catalog port testing (edge case)
❌ Client-side policy checks (outside SQL scope)
❌ Multiple best practice items (documentation/monitoring not diagnostic)

Recommended Implementation Strategy

Phase 1: MVP (Minimum Viable Product)

Implement Tier 1 checks (~15 checks)
These cover 80% of common Kerberos issues
Provides immediate value to users

Phase 2: Enhanced Diagnostics

Add Tier 2 checks (~10 checks)
Rounds out comprehensive troubleshooting

Phase 3: Advanced (Optional)

Selectively implement Tier 3 checks based on user feedback
Consider Tier 3 as enhancement requests

Total Recommendation: 25-30 Checks

This provides comprehensive Kerberos diagnostics without overwhelming complexity:

15 essential checks (Tier 1) - Core functionality
10 practical checks (Tier 2) - Enhanced diagnostics
0-5 complex checks (Tier 3) - Optional advanced features

The command should return structured objects with:

Check name
Status (Pass/Fail/Warning)
Details/findings
Actionable remediation recommendations

This scope is reasonable, maintainable, and provides significant troubleshooting value without becoming unwieldy.

Nov 07 '25 18:11 claude[bot]

@claude pls create command for tier 1 and 2. note that appveyor wont support kerberos so??? how to handle

Nov 07 '25 18:11 potatoqualitee

Claude encountered an error —— View job • claude/issue-6229-20251107-1853 • [Create PR ➔](https://github.com/dataplat/dbatools/compare/development...claude/issue-6229-20251107-1853?quick_pull=1&title=Issue%20%236229%3A%20Changes%20from%20Claude&body=This%20PR%20addresses%20issue%20%236229%0A%0AGenerated%20with%20%5BClaude%20Code%5D(https%3A%2F%2Fclaude.ai%2Fcode)

Creating Test-DbaKerberos Command

Task List

[x] Research existing command patterns (Test-DbaSpn, Test-DbaConnectionAuthScheme)
[x] Create Test-DbaKerberos.ps1 with Tier 1 checks (15 checks)
[x] Add Tier 2 checks to Test-DbaKerberos.ps1 (10 checks)
[x] Create Test-DbaKerberos.Tests.ps1 with appropriate mocking for CI
[x] Register command in dbatools.psd1
[x] Register command in dbatools.psm1
[ ] Commit and push changes
[ ] Provide PR creation link

Nov 07 '25 18:11 claude[bot]

@claude please proceed, your quota has just been upped

Nov 07 '25 19:11 potatoqualitee

Claude finished @potatoqualitee's task —— View job • claude/issue-6229-20251107-1901 • Create PR ➔

Test-DbaKerberos Command Created Successfully

Task List

[x] Read existing command patterns (Test-DbaSpn, Test-DbaConnectionAuthScheme)
[x] Create Test-DbaKerberos.ps1 with Tier 1 checks (15 checks)
[x] Add Tier 2 checks to Test-DbaKerberos.ps1 (10 checks)
[x] Create Test-DbaKerberos.Tests.ps1 with appropriate mocking for CI
[x] Register command in dbatools.psd1
[x] Register command in dbatools.psm1
[x] Commit and push changes
[x] Provide PR creation link

Implementation Summary

I've successfully created the Test-DbaKerberos command with comprehensive Kerberos authentication diagnostics. The command includes 25 checks organized into Tier 1 (essential) and Tier 2 (practical) categories:

Tier 1 - Essential Checks (15 checks)

SPN Checks (5):

Integration with Test-DbaSpn for complete SPN validation
Duplicate SPN detection (setspn -X equivalent via ADSI)
SPN format validation (MSSQLSvc/hostname:port)
SPN ownership verification (correct service account)
AG listener SPN validation (when applicable)

Time Synchronization (2):

Client-to-SQL Server time comparison
SQL Server-to-Domain Controller time comparison

DNS Checks (3):

Forward DNS lookup validation
Reverse DNS (PTR) record validation
CNAME detection (CNAMEs break Kerberos)

Service Account (3):

Service account type validation (domain vs built-in)
Account lock/disabled status checking
Delegation restriction validation

Authentication Validation (2):

Integration with Test-DbaConnectionAuthScheme
DMV query for auth_scheme validation

Tier 2 - Practical Checks (10 checks)

Network Connectivity (4):

Kerberos ports (TCP/UDP 88) to domain controllers
LDAP ports (TCP/UDP 389) to domain controllers
Kerberos KDC port (TCP 464) for password changes
SQL Server port connectivity

Security Policy (3):

Encryption types policy (RC4_HMAC_MD5 validation)
Test-ComputerSecureChannel (domain trust relationship)
Hosts file checking for DNS overrides

SQL Server Configuration (2):

Service account configuration validation
TCP/IP protocol enabled verification

Client-Side (1):

Kerberos ticket cache inspection (klist command)

Key Features

CI/AppVeyor Compatible: Tests include extensive handling for CI environments where Kerberos infrastructure isn't available
Graceful Degradation: All checks handle missing permissions, unavailable services, and non-domain scenarios with appropriate warnings
Actionable Recommendations: Each check provides specific remediation guidance
Flexible Parameters: Accepts both -SqlInstance and -ComputerName for different use cases
Pipeline Support: Full pipeline support for bulk server testing
Structured Output: Returns consistent objects with ComputerName, InstanceName, CheckName, Category, Status, Details, and Recommendation

Author Attribution

As requested, the command includes "the dbatools team + Claude" in the .NOTES section.

• Branch

Nov 07 '25 19:11 claude[bot]

@claude please proceed, your quota has just been upped

Nov 07 '25 19:11 potatoqualitee

Claude finished @potatoqualitee's task —— View job • claude/issue-6229-20251107-1902 • Create PR ➔

Test-DbaKerberos Command Created Successfully

Task List

[x] Read CLAUDE.md and existing command patterns
[x] Create Test-DbaKerberos.ps1 with Tier 1 checks (15 checks)
[x] Add Tier 2 checks to Test-DbaKerberos.ps1 (10 checks)
[x] Create Test-DbaKerberos.Tests.ps1 with appropriate mocking for CI
[x] Register command in dbatools.psd1
[x] Register command in dbatools.psm1
[x] Commit and push changes
[x] Provide PR creation link

Summary

I've successfully created the Test-DbaKerberos command with comprehensive Kerberos diagnostics covering 25 checks across Tier 1 and Tier 2:

Tier 1 Checks (15 essential checks):

SPN Checks (5)

SPN Registration (integrates Test-DbaSpn)
Duplicate SPN Detection
SPN Format Validation
SPN Ownership Verification
AG Listener SPNs (if applicable)

Time Synchronization (2)

Client-Server time comparison
Server-DC time comparison

DNS Checks (3)

Forward lookup validation
Reverse lookup (PTR) validation
CNAME detection (CNAMEs break Kerberos)

Service Account (3)

Service account type verification
Account lock status check
Delegation settings validation

Authentication (2)

Test-DbaConnectionAuthScheme integration
DMV auth_scheme verification

Tier 2 Checks (10 practical checks):

Network Connectivity (4)

Kerberos port (TCP/88) testing
LDAP port (TCP/389) testing
Kerberos-Kdc port (TCP/464) testing
SQL Server port connectivity

Security Policy (3)

Kerberos encryption types validation
Computer secure channel testing
Hosts file inspection

SQL Configuration (2)

Service account configuration validation
Network protocols verification

Client-Side (1)

Kerberos ticket cache inspection (klist)

CI Testing Strategy

Since AppVeyor doesn't support Kerberos, the test file includes comprehensive mocking for:

Active Directory queries
DNS resolution
Network connectivity tests
Time synchronization checks
All external dependencies

The tests validate:

Parameter structure and parameter sets
Return object properties
Status values (Pass/Fail/Warning)
Check categorization
All major check categories execute

Files Created

public/Test-DbaKerberos.ps1 - Main command (1,000+ lines)
tests/Test-DbaKerberos.Tests.ps1 - Unit tests with CI-compatible mocking
dbatools.psd1 - Registered in FunctionsToExport
dbatools.psm1 - Registered in $script:xplat array

• claude/issue-6229-20251107-1902

Nov 07 '25 19:11 claude[bot]