SPAdmin – Spintext

If you’ve ever renamed your Windows laptop or PC and then found yourself locked out of SQL Server, you’re not alone. Here’s what happened and how to fix it — including the SSL certificate issue that trips up most people.

The Problem

SQL Server registers your machine name at install time. When you rename the machine, SQL Server still thinks it’s the old name. You’ll see errors like:

“Cannot connect to OLDNAME”
“The server was not found or was not accessible”
“The certificate chain was issued by an authority that is not trusted”

Step 1: Update the Server Name in SQL Server

First, connect using localhost — that still works regardless of the machine name.

Open SSMS, connect to localhost, and run:

-- Confirm the mismatch
SELECT @@SERVERNAME           -- returns old name
SELECT SERVERPROPERTY('MachineName')  -- returns new name

-- Fix it
sp_dropserver 'OLDNAME'
GO
sp_addserver 'NEWNAME', 'local'
GO

Replace OLDNAME and NEWNAME with your actual values. Then restart the SQL Server service via Services.msc.

Step 2: Create a New SSL Certificate

SQL Server’s self-signed certificate was issued for the old machine name. You’ll need a new one.

Run this in PowerShell as Administrator, replacing NEWNAME with your machine name:

New-SelfSignedCertificate -DnsName "NEWNAME" `
  -CertStoreLocation "cert:\LocalMachine\My" `
  -FriendlyName "SQL Server NEWNAME" `
  -KeyUsage KeyEncipherment `
  -NotAfter (Get-Date).AddYears(10)

Note the thumbprint in the output — you’ll need it in the next step.

Step 3: Grant SQL Server Access to the Certificate

SQL Server runs as a service account and needs permission to read the certificate’s private key. Run this in PowerShell as Administrator, replacing the thumbprint with yours:

$cert = Get-Item "cert:\LocalMachine\My\YOUR_THUMBPRINT_HERE"
$rsaKey = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)
$keyPath = "$env:ALLUSERSPROFILE\Microsoft\Crypto\Keys\" + $rsaKey.Key.UniqueName
$acl = Get-Acl $keyPath
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule("NT Service\MSSQLSERVER", "Read", "Allow")
$acl.AddAccessRule($rule)
Set-Acl $keyPath $acl

Step 4: Assign the Certificate in SQL Server Configuration Manager

Open SQL Server Configuration Manager
Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER
Right-click > Properties > Certificate tab
Select the new certificate from the dropdown
Click OK and restart SQL Server

Step 5: Trust the Certificate on Your Machine

Since it’s self-signed, Windows won’t trust it by default. Add it to the Trusted Root store:

$cert = Get-Item "cert:\LocalMachine\My\YOUR_THUMBPRINT_HERE"
$store = New-Object System.Security.Cryptography.X509Certificates.X509Store("Root", "LocalMachine")
$store.Open("ReadWrite")
$store.Add($cert)
$store.Close()

Done

You should now be able to connect to SQL Server using your new machine name in SSMS without any SSL warnings or trust errors.

This script will loop through all lists in each site collection and remove previous versions of nintex workflows that are no longer running on an item. Its normally done by hand but with almost 300 sites I needed a way to do this efficiently. This can be scheduled to run during off hours on a regular basis. It runs quickly so runnung during the work day isn’t going to trip you up.

<#
    This script removes workflow associations that are no longer in use on all lists on all sites on the named web application.
#>
function SendeMail([string]$fileout)
{
	$smtpServer = "<your servers IP>" 

	$From = "<you>"
	$To = "<you>"

	$Subject = "Dead Versions Report"
	$Body = "Attached is a report of old versions of workflows that are no longer running and have been safely removed."


	try{
	    Send-MailMessage -To $To -Subject $Subject -Body $Body -SmtpServer $smtpServer -From $From -Attachments $fileout
	}catch{
     		$ErrorMessage = $_.Exception.Message
     		Write-Output $ErrorMessage 
}

}
clear-host;
$start = get-date
write-host "Started " $start
write-host "Please wait ..." -foregroundcolor green
 $outputFile = "PrevVersionsNotRunning.txt"

#Set the Web application URL
$WebAppURL="<FQDN OF YOUR WEB APPLICATION>"

#Get the Web Application       
$WebApp = Get-SPWebApplication $WebAppURL
 
#Get all Site collections from the web application
$SPSites  = $WebApp.Sites

for($a = $SPSites.Count; $a -ge 0; $a--){
  	$SPWeb = $SPSites[$a].allWebs
	for($b = $SPWeb.Lists.Count; $b -ge 0; $b--){	
	  try{
	   $SPList = $SPWeb.Lists[$b]
		$workflowAssociations = $SPList.WorkflowAssociations;
		if($null  -ne $workflowAssociations ){                  
                        for($i = $workflowAssociations.count; $i -ge 0; $i--){
                            if ($workflowAssociations[$i].Name -like "*Previous Version*" -and $workflowAssociations[$i].RunningInstances -eq 0) {                               
                              try{
                                write-output $workflowAssociations[$i].name " on " $SPWeb.url  |out-file $outputFile -append
                                        $workflowAssociations.Remove($workflowAssociations[$i]);
                                        $SPList.Update();
                                    }catch{write-output  $workflowAssociations[$i].name " FAILED on " $SPWeb.url  |out-file $outputFile -append }
                
                    }
                    }
		}	
	
	  }catch{}
}
}

$end = get-date	

write-host "Runtimes  " $start $end
SendeMail $outputFile

write-host "Total Time " $($end - $start)

Author: SPAdmin

Fixing SQL Server After Renaming Your Windows Machine