Create local SSL Certificate, add it to personal and public stores + ACL for SQL Server instance

Create-SqlCertificateforsecuredconnection.ps1

# Parameters
$hostname = $env:COMPUTERNAME
$subjectName = "CN=$hostname"
$certFriendlyName = "SQL Server SSL Cert - $hostname"
$certStoreLocation = 'Cert:\LocalMachine\My'
$sqlServiceAccount = "NT SERVICE\MSSQLSERVER"  # Change if using named instance
$pfxPath = "C:\certs\$dnsName.pfx"
$cerPath = "C:\certs\$dnsName.cer"
$pfxPassword = ConvertTo-SecureString -String "devpass123" -Force -AsPlainText

# Ensure output folder exists
New-Item -ItemType Directory -Force -Path "C:\certs" | Out-Null

# Create the certificate
$cert = New-SelfSignedCertificate `
    -Subject $subjectName `
    -DnsName $hostname `
    -CertStoreLocation $certStoreLocation `
    -KeyExportPolicy Exportable `
    -KeySpec KeyExchange `
    -KeyLength 2048 `
    -Provider "Microsoft RSA SChannel Cryptographic Provider" `
    -NotAfter (Get-Date).AddYears(5) `
    -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.1,1.3.6.1.5.5.7.3.2") # Server Authentication EKU

# Set friendly name
$cert.FriendlyName = $certFriendlyName

Write-Host "`n✅ Certificate created with thumbprint:" $cert.Thumbprint

Write-Host "➡ Exporting to PFX: $pfxPath"
Export-PfxCertificate `
  -Cert "cert:\LocalMachine\My\$($cert.Thumbprint)" `
  -FilePath $pfxPath `
  -Password $pfxPassword | Out-Null

# Import into LocalMachine\Root (Trusted Root Certification Authorities)
Import-PfxCertificate -FilePath $pfxPath -CertStoreLocation "Cert:\LocalMachine\Root" -Password $pfxPassword

Write-Host "`n✅ Imported Certificate to trusted root - thumbprint:" $cert.Thumbprint

# Grant Read access on the private key to SQL Server
$keyPath = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
$fullPath = "$env:ProgramData\Microsoft\Crypto\RSA\MachineKeys\$keyPath"

# Apply ACL
$acl = Get-Acl $fullPath
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($sqlServiceAccount, "Read", "Allow")
$acl.AddAccessRule($rule)
Set-Acl $fullPath $acl

Write-Host "`n✅ Granted private key read access to service account:" $sqlServiceAccount