Category: Uncategorized

# Confirmed working for Windows versions of SQL Server 2016-2019
function GetSqlServerProductKey($InstanceName="MSSQL13.MSSQLSERVER") {
    $localmachine = [Microsoft.Win32.RegistryHive]::LocalMachine
    $defaultview = [Microsoft.Win32.RegistryView]::Default
    $reg = [Microsoft.Win32.RegistryKey]::OpenBaseKey($localmachine, $defaultview)
    $key = "SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceName\Setup"
    $encodedData = $reg.OpenSubKey($key).GetValue("DigitalProductID")
    $reg.Close()
 
    try {
        $binArray = ($encodedData)[0..66]
        $productKey = $null
 
        $charsArray = "B", "C", "D", "F", "G", "H", "J", "K", "M", "P", "Q", "R", "T", "V", "W", "X", "Y", "2", "3", "4", "6", "7", "8", "9"
 
        $isNKey = ([math]::truncate($binArray[14] / 0x6) -band 0x1) -ne 0;
        if ($isNKey) {
        $binArray[14] = $binArray[14] -band 0xF7
        }
 
        $last = 0
 
        for ($i = 24; $i -ge 0; $i--) {
            $k = 0
            for ($j = 14; $j -ge 0; $j--) {
                $k = $k * 256 -bxor $binArray[$j]
                $binArray[$j] = [math]::truncate($k / 24)
                $k = $k % 24
            }
            $productKey = $charsArray[$k] + $productKey
            $last = $k
        }
 
        if ($isNKey) {
            $part1 = $productKey.Substring(1, $last)
            $part2 = $productKey.Substring(1, $productKey.Length-1)
            if ($last -eq 0) {
                $productKey = "N" + $part2
            }
            else {
                $productKey = $part2.Insert($part2.IndexOf($part1) + $part1.Length, "N")
            }
        }
 
        $productKey = $productKey.Insert(20, "-").Insert(15, "-").Insert(10, "-").Insert(5, "-")
    } 
    catch {
        $productkey = "Cannot decode product key."
    }
 
    $productKey
}

Change the instance name to what is displayed in the registry

Then run the below command in powershell, after execting the function creation script above ofcourse.

GetSqlServerProductKey($InstanceName="MSSQL13.MSSQLSERVER")

Adapt the instance name to what is required.

# Define the new DNS server IP addresses
$dnsServers = "8.8.8.8", "1.1.1.1", "8.8.4.4"

# Define the OU where you want to change DNS servers
$OU = "OU=server,DC=domain,DC=local"  # Replace with your actual OU path

# Create an array to store results
$results = @()

# Get all computer objects in the specified OU
$computers = Get-ADComputer -Filter {Name -like "AZ-EIW-DC01*"} -SearchBase $OU

# Loop through each computer object
foreach ($computer in $computers) {
    $computerName = $computer.Name
    $computerDN = $computer.DistinguishedName

    try {
        # Set the DNS server IP addresses on the remote computer
        Invoke-Command -ComputerName $computerName -ScriptBlock {
            $networkAdapter = Get-NetAdapter | Where-Object { $_.Status -eq 'Up' }
            Set-DnsClientServerAddress -InterfaceAlias $networkAdapter.Name -ServerAddresses $using:dnsServers -ErrorAction Stop
        }

        # Record the success in the results array
        $results += [PSCustomObject]@{
            ComputerName = $computerName
            Status = "Success"
        }
    }
    catch {
        # If an error occurs, record the failure in the results array
        $results += [PSCustomObject]@{
            ComputerName = $computerName
            Status = "Error: $_"
        }
    }
}

# Export the results to a CSV file
$results | Export-Csv -Path "C:\Temp\DNSChangeResults.csv" -NoTypeInformation

# Display the results on the console
$results

HKLM\System\CurrentControlSet\Control\SecurityProviders\Schannel\

value: CertificateMappingMethods
Data Type: DWORD
Data: 0x1F

If you are facing issues on a newly deployed NPS or even an existing one, whilst using a certificate method to authenticate your users/devices, you might find that the above registry key will resolve the issue.

There has also been reports about the below updates causing issues with NPS on Server 2019 –

KB5013641

KB5013941

KB5014754