Sometimes you'll need to retrieve your SQL Server Product Key from an existing installation on your Client or Server machine: the most common scenario takes place when you have an old Server to move or relocate and no one around you seem to remember where the license are... or if you've simply lost the Service Key post-it.
Luckily enough, you can easily get this information back thanks to this neat PowerShell script made by Jacob Bindslet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
function GetSqlServerProductKey { ## function to retrieve the license key of a SQL 2008 Server. param ($targets = ".") $hklm = 2147483650 $regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup" $regValue1 = "DigitalProductId" $regValue2 = "PatchLevel" $regValue3 = "Edition" Foreach ($target in $targets) { $productKey = $null $win32os = $null $wmi = [WMIClass]"\\$target\root\default:stdRegProv" $data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1) [string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue [string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue $binArray = ($data.uValue)[52..66] $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" ## decrypt base24 encoded binary data 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 If (($i % 5 -eq 0) -and ($i -ne 0)) { $productKey = "-" + $productKey } } $win32os = Get-WmiObject Win32_OperatingSystem -computer $target $obj = New-Object Object $obj | Add-Member Noteproperty Computer -value $target $obj | Add-Member Noteproperty OSCaption -value $win32os.Caption $obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture $obj | Add-Member Noteproperty SQLver -value $SQLver $obj | Add-Member Noteproperty SQLedition -value $SQLedition $obj | Add-Member Noteproperty ProductKey -value $productkey $obj } } |
The script works with any SQL Server edition & version starting from 2005: SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. Pay close attention, though, if you're using Sql Server 2012 or Sql Server 2014 you'll have to make some small modifications to that code.
For Sql Server 2012 you need to replace two lines of code. In details, replace line 5 with the following line:
|
1 |
$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup" |
And also replace line 16 with the following line (thanks to gprkns for pointing it out):
|
1 |
$binArray = ($data.uValue)[0..16] |
You can also take a look of the complete script code for Sql Server 2012 at the following link.
For Sql Server 2014, Microsoft moved the DigitalProductID node to the actual instance name in the registry, so you will need to replace line 5 with something similar to the following (depending on your installation):
|
1 |
$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.[YOUR SQL INSTANCE NAME]\Setup" |
All you have to do in order to execute this script is to perform these actions:
- Launch a PowerShell prompt (Start > Run, then type powershell and press ENTER.
- Copy the above function text and past it directly inside the prompt area.
- Press ENTER a couple times, just to be sure you're back to the prompt.
- Type GetSqlServerProductKey, then press ENTER.
If everything has been done like it should you'll be able to see the following informations:
As you can see there's a lot of stuff regarding your SQL Server installation, most of them you should know already, the latter being the Product Key.
That's all for now: happy recover!
I love the idea. I need this to work. But I’m getting an error when I run the function script. Any idea why? Here is the error. Same result on a Windows Server 2008 R2 and 2012 R2 machine. Both have SQL Server 2012 installed upon them.
Cannot index into a null array.
At line:23 char:17
+ $binArray[$j] = [math]::truncate($k / 24)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray
Computer : .
OSCaption : Microsoft Windows Server 2008 R2 Standard
OSArch : 64-bit
SQLver :
SQLedition :
ProductKey : BBBBB-BBBBB-BBBBB-BBBBB-BBBBB
I gave this a try on a Windows Server 2008 R2 that was hosting SQL Server 2008 R2, and viola! It worked. But your caveat emptor regarding SQL Server 2014 must also apply to SQL Server 2012? In other words, the value for ‘$regPath = “SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL11.[YOUR SQL INSTANCE NAME]Setup” ‘ needs to be changed in the function? But I’m still getting the error, even though I opened up regedit and confirmed the path is as shown. Any other ideas?
You were on the right track, but for Sql Server 2012 you still have to use the old-format regPath. I updated the post with the proper mods to apply in order for it to work with Sql Server 2012. Please let me know if it solves your issue.
Changing the regkey is not enough by itself. (Sorry no SQL 2014 to test that mod.) I did a little more Googling and found a working version of your script modified by Xian Wang for SQL 2012. Here is a link to his working version of the same PowerShell function: https://xzwang.wordpress.com/2013/06/22/retreiving-sql-server-2012-product-key-from-the-registry/
You’re right, the binArray has to be changed aswell. I updated the post mentioning your suggestion and including the link to Xian Wang’s blog. Thanks for your valuable feedback! :)
Thanks, that link worked great for my SQL2014 server in Powershell/ (Once I updated Powershell. Thanks xzwang!)
Hi , I’ve had the same issue.
The value “DigitalProductId” was in a different registry location. After changing the script to match that registry key it all went well.
Hope it helps
Thank you! It really works and it’s actually straightforward to recover your key.
hi wonder if you can help
ive a licence number but no product key for SQL SVR STD EDTN 2014 SNGL OLP NL
how do i enter licence number
——————————
There are validation errors on this page. Click OK to close this dialog box. Review errors at the bottom of the setup page, then provide valid parameters or click Help for more information.
——————————
BUTTONS:
OK
——————————
IT SYS ADMINS!!! This script DOES NOT WORK! I’ve already wasted a few days on it. https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8/view/Discussions#content Doesn’t work either! Figured I’d save yall some time. I still have no working solution.
I get the following error: Cannot index into a null array.
At line:23 Char:27
$binArray[ <<<< $j] = [math]::truncate($k / 24)
Help!
I’m trying this on Windows Server 2008 R2 running SQL Server 2008 R2 and I get the following error:
Cannot index into a null array.
At line:23 char:27
+ $binArray[$j] = [math]::truncate($k / 24)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (1:Int32) [], RuntimeException
+ FullyQualifiedErrorId : NullArray
Please help!
I tried this in SQL 2014 and got the same error however I decided to check the registry to double check the path in line 5 and regedit showed HKLM\Software\Microsoft SQL Server\120\Tools\Setup
The NullArray error went away and it produced a valid key once this was changed. This is an old post but for those who look back and ready this I suggest validating the existence of the path before giving up.
Thank you for your nice contribution
Find yourself a licence of Landsweeper and once the server is scanned click on the server and go in: Software – Licence
Both windows server with SQL licences I have had licence displayed there.
No need of purchasing (or “finding”) the license of a commercial product when you can obtain the same info with a free and working Powershell script. But hey, to each his own.
Evidently, you didn’t notice that none of this PowerShell scritping works.
Evidently, you didn’t notice that this comment streak is full of people who said it worked (including me, for SQL Server 2008 and 2014 the last time I’ve used them).
it worked for me with SQL Server 2017 with making the changes as suggested.
In an old installation of Windows2012R2 and MS SQL 2014 Standard,
got it to work after an additional change:
from:
$binArray = ($data.uValue)[52..66]
to:
$binArray = ($data.uValue)[0..15]
Just commenting to say this change worked for me
Worked for me as well, thanks! (sql2014 on server 2012 R2)
Has anyone updated this script for SQL Server 2019?
Hi Deacon336,
I managed to get this to work on SQL 2017 and SQL 2019 – I had to change the registry location of the DigitalProductId and update the sqlVersionArray to include 140 (2017) and 150 (2019).
$regPathProdId = $baseRegPath + $sqlVersion + “\Tools\ClientSetup”
$data = $wmi.GetBinaryValue($hklm,$regPathProdId,$regValue1)
Hopefully this helps you, I can paste the full script if you wish.
If you would be so kind. Posting the full script would be very helpful. I currently have no experience with powershell scripting, and am just trying to get reporting services on my machine. Thanks!
The below script will just decrypt the product key binary
If you are unable to boot the system that has the sql server installed
load the registry from that system into registry viewer
C:\Windows\System32\config\SOFTWARE
find the binary for the product key
SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup\DigitalProductID (note this was for SQL 2008 R2 Standard )
Paste the binary in to the below script and run
function DecryptSqlProductKeyBinary {
## Paste Binary below (format should be A4 00 00 00 03 and so on)
param ($targets = “.”)
$binArray = (“PutBinaryHere”)[52..66]
$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″
## decrypt base24 encoded binary data
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
If (($i % 5 -eq 0) -and ($i -ne 0)) {
$productKey = “-” + $productKey
}
}
how do you get the code into the prompt area?
Can you please post the entire script? I tried making your suggested changes but I’m unable to get it to work.
Thank you
The above decoding method will not work for 2016 and above. Microsoft changed the encoding after 2016 SQL.
Even though it will return what will look like a product key, it will not be valid.
Here is a working script for SQL Server 2019 that I had to dig out of the internet after hours of searching and troubleshooting.
$fromReg = 1
if ($fromReg -eq 1) {
$localmachine = [Microsoft.Win32.RegistryHive]::LocalMachine
$defaultview = [Microsoft.Win32.RegistryView]::Default
$reg = [Microsoft.Win32.RegistryKey]::OpenBaseKey($localmachine, $defaultview)
$key = “SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Setup”
$encodedData = $reg.OpenSubKey($key).GetValue(“DigitalProductID”)
$reg.Close()
}
else {
$encodedData = 27,17,0,0,0,0,172,132,0,167,211,98,242,243,11,0
}
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.”
}
Write-Host $productKey
This worked for me. SQL Server 2014 STD. Thanks!
When I copy\pasted this version I got an error on these lines. There should be two dashes after $i and $j.
Probably lost in clipboard.
for ($i = 24; $i -ge 0; $i–) {
$k = 0
for ($j = 14; $j -ge 0; $j–) {
:::::::::
for ($i = 24; $i -ge 0; $i–) {
$k = 0
for ($j = 14; $j -ge 0; $j–) {