MultiCloud Classroom notes 27/Apr/2026

# ============================================================
# SQL Server 2022 Developer/Express Setup Script
# ============================================================

# Set execution policy
Set-ExecutionPolicy Bypass -Scope Process -Force

# ── 1. Install Chocolatey ────────────────────────────────────
[System.Net.ServicePointManager]::SecurityProtocol = `
    [System.Net.ServicePointManager]::SecurityProtocol -bor 3072
Invoke-Expression ((New-Object System.Net.WebClient).DownloadString(
    'https://chocolatey.org/install.ps1'))

# Reload PATH so choco is available immediately
$env:PATH = [System.Environment]::GetEnvironmentVariable("PATH","Machine") + ";" +
            [System.Environment]::GetEnvironmentVariable("PATH","User")

# ── 2. Skip pending-reboot checks ───────────────────────────
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" `
    -Recurse -ErrorAction SilentlyContinue
Remove-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" `
    -Recurse -ErrorAction SilentlyContinue
Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" `
    -Name "PendingFileRenameOperations" -ErrorAction SilentlyContinue

# ── 3. Install SQL Server 2022 Developer Edition ─────────────
choco install sql-server-2022 `
    --params '"/INSTANCENAME=SQLEXPRESS /INSTANCEID=SQLEXPRESS /SECURITYMODE=SQL /SAPWD=Welcome@12345 /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS"' `
    -y --no-progress

# ── 4. Install sqlcmd (modern standalone tool) ───────────────
# Option A – via Chocolatey (Microsoft's official package)
choco install sqlcmd -y --no-progress

# Option B – also installs ODBC Driver 18 (needed by sqlcmd)
choco install sqlserver-odbcdriver -y --no-progress

# Reload PATH again so sqlcmd is found in this session
$env:PATH = [System.Environment]::GetEnvironmentVariable("PATH","Machine") + ";" +
            [System.Environment]::GetEnvironmentVariable("PATH","User")

# ── 5. Enable TCP/IP via registry ───────────────────────────
# FIX: Use correct registry path (check your actual SQL version folder name)
$tcpPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp"

# Enable TCP globally
Set-ItemProperty -Path $tcpPath -Name "Enabled" -Value 1 -ErrorAction SilentlyContinue

# Set port on IPAll sub-key
$ipAllPath = "$tcpPath\IPAll"
Set-ItemProperty -Path $ipAllPath -Name "TcpPort"        -Value "1433"
Set-ItemProperty -Path $ipAllPath -Name "TcpDynamicPorts" -Value ""   # clear dynamic port

# ── 6. Restart SQL Server service ───────────────────────────
# FIX: Express instance service name is MSSQL$SQLEXPRESS, not MSSQLSERVER
Restart-Service -Name "MSSQL`$SQLEXPRESS" -Force -ErrorAction Stop
Start-Sleep -Seconds 8   # give the engine a moment to come up

# ── 7. SQL Browser – auto-start and start ───────────────────
# FIX: Removed duplicate Set-Service line
Set-Service  -Name "SQLBrowser" -StartupType Automatic
Start-Service -Name "SQLBrowser" -ErrorAction SilentlyContinue

# ── 8. Firewall rules ───────────────────────────────────────
New-NetFirewallRule -DisplayName "SQL Server TCP 1433" `
    -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow -ErrorAction SilentlyContinue
New-NetFirewallRule -DisplayName "SQL Server Browser UDP 1434" `
    -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow -ErrorAction SilentlyContinue

# ── 9. Enable sa login via sqlcmd ───────────────────────────
# FIX: Use Windows Auth (-E) for first-time SA setup; SA may be locked initially
$saSetupSql = @"
ALTER LOGIN [sa] ENABLE;
ALTER LOGIN [sa] WITH PASSWORD = 'Welcome@12345';
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote access', 1;
RECONFIGURE;
"@

# Try Windows auth first (most reliable right after install)
sqlcmd -S "localhost\SQLEXPRESS" -E -Q $saSetupSql

# Verify sa login works
sqlcmd -S "localhost\SQLEXPRESS" -U sa -P "Welcome@12345" -Q "SELECT @@VERSION, GETDATE();" -l 10

# ── 10. SQL Agent – auto-start and start ────────────────────
# NOTE: SQL Agent is NOT included in Express edition.
# Only run this block if you installed Developer/Standard/Enterprise.
$agentSvc = Get-Service -Name "SQLAgent`$SQLEXPRESS" -ErrorAction SilentlyContinue
if ($agentSvc) {
    Set-Service  -Name "SQLAgent`$SQLEXPRESS" -StartupType Automatic
    Start-Service -Name "SQLAgent`$SQLEXPRESS"
} else {
    Write-Warning "SQL Server Agent not found – it is not available in Express edition."
}

Write-Host "`n✅ Setup complete. Connect with: sqlcmd -S localhost\SQLEXPRESS -U sa -P 'Welcome@12345'" -ForegroundColor Green