Introduction

SQL Server auth fails when mixed mode disabled or password incorrect. This guide provides step-by-step diagnosis and resolution.

Symptoms

Typical error output:

bash
Error: Windows Server operation failed
Check Event Viewer for details: eventvwr.msc
Run as Administrator for troubleshooting

Common Causes

  1. 1.Service account does not have required permissions
  2. 2.Firewall or network configuration blocking access
  3. 3.Resource exhausted or configuration corrupted
  4. 4.Certificate or authentication configuration issue

Step-by-Step Fix

Step 1: Check Current State

powershell
# Check service status (PowerShell)
Get-Service -Name ServiceName
# View event logs
Get-EventLog -LogName System -Newest 20
# Test connectivity
Test-NetConnection -ComputerName server -Port 3389

Step 2: Identify Root Cause

powershell
# Check event logs
Get-WinEvent -LogName System -MaxEvents 50
# Verify service configuration
Get-WmiObject -Class Win32_Service -Filter "Name='ServiceName'"
# Test network connectivity
Test-NetConnection -ComputerName target -Port 3389

Step 3: Apply Primary Fix

```powershell # Primary fix: Check and restart service # Check service status Get-Service -Name ServiceName

# Grant logon as service right secpol.msc -> Local Policies -> User Rights Assignment

# Restart service Restart-Service -Name ServiceName -Force ```

Step 4: Apply Alternative Fix

```powershell # Alternative: Repair via PowerShell # Check event log for errors Get-WinEvent -FilterHashtable @{LogName='System'; Level=2} -MaxEvents 10

# Repair service sc.exe delete ServiceName # Reinstall or reregister service

# Check dependencies Get-Service -Name ServiceName -RequiredServices ```

Step 5: Verify the Fix

powershell
Get-Service -Name ServiceName
# Status should be Running
Test-NetConnection -ComputerName localhost -Port <port>
# Should show TcpTestSucceeded : True

Common Pitfalls

  • Not running PowerShell as Administrator
  • Forgetting to check Event Viewer for detailed errors
  • Ignoring service dependencies
  • Not testing firewall rules after changes

Best Practices

  • Always run management tools as Administrator
  • Regular backup of server configuration
  • Monitor Event Viewer for warning signs
  • Keep Windows and services updated
  • Windows Service Failed
  • Active Directory Error
  • RDP Connection Failed
  • IIS Application Pool Error