Getting the SQL Server Product Key via Powershell

I was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM, but needed to get the product key. There are a few places where you can find Powershell functions to get this product key, with Ryan @ Ryadel’s post being the most well-known. That method covers versions from SQL Server 2005 through 2014 (if you follow the notes in the blog post), but breaks on 2016.

What’s interesting is that the 2014-style code actually does work, in the sense that it generates a 25-character product code. But it turns out to be the wrong product code. At the very bottom of the post, Welp28 has a version which worked for me. I’ve cleaned it up a little bit to deal with formatting issues, made the instance name a parameter, and also removed the manually-encoded version. Here’s the script, which I’ve tested on 2016 and 2019, and also works on 2017. It might work on future versions of SQL Server, but given that there were different mechanisms for 2008, 2012, 2014, and 2016, who knows the future?

# Confirmed working for Windows versions of SQL Server 2016-2019
# May work for later versions but this changes a lot!
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
}

You would call this thusly for a default installation of SQL Server 2016: GetSqlServerProductKey("MSSQL13.MSSQLSERVER"). If you don’t know what the instance name should be, you can also pop into regedit.exe and go to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ and look for something like MSSQL[##].[SOMETHING], where [##] is a two-digit number (13 = 2016, 14 = 2017, 15 = 2019) and [SOMETHING] is your instance name, with MSSQLSERVER as the default.

This also will only work on SQL Server for Windows, as it’s looking for items in the registry. For SQL Server on Linux users, there’s going to be a different method. I don’t know what that method is, but it’s probably out there…

Advertisement