How to retrieve the serial number of SQL server instance after installation

Time:2020-11-24

When you need to install SQL server again, if the serial number cannot be found, you can try to retrieve the serial number from the installed instance, because after installing SQL server, the product key is saved in the registry;

If the installation package downloaded by MSDN subscription has a built-in serial number, there is no such problem.

1、 Where is the serial number saved

Through undocumented extended stored procedure XP_ Regread to read the registry:


--For SQL Server 2008, 2008 R2
use master
GO
exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server0\Tools\Setup','ProductCode'
exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server0\Tools\Setup','DigitalProductID'
GO
--For SQL Server 2012
use master
GO
exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server0\Tools\Setup','ProductCode'
exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server0\Tools\Setup','DigitalProductId'
GO

Don’t be confused by productcode. Even if only SQL Server client is installed, this key value will be found in the registry. It is not the serial number, but the digital productid. However, it needs to be decoded after base24 encoding.

As you can see, for different versions, the path of the registry is different, but the key is the same.

The express version is free and has no serial number, so the registry does not have the digital product ID key.

2、 How to decode the serial number

1. Introduction of base24 and Base64 coding

Many people may have heard of Base64 encoding, which is used to encode long strings to facilitate transmission;

Base24 coding is mainly used in the generation of serial number. The realization ideas of the two codes are similar, but the encoding mode is a little changed.

The coding table corresponding to Base64 is as follows:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=

There are 64 characters in total.

The coding table corresponding to base24 is as follows:

BCDFGHJKMPQRTVWXY2346789

There are 24 characters in total, and some characters which are not easy to recognize and easy to confuse are removed here.

The serial number of SQL Server adopts base24 encoding. After decoding the encoded characters, the original text can be obtained.

Encoding / decoding is not encryption / decryption. There is no secret key. Only the rules of character conversion, base24 and Base64 can be found on the Internet.

2. Decoding by using PowerShell

The following shell functions are used to decode / retrieve the SQL Server serial number. The test is passed on SQL Server 2008, 2008 R2 instance:


function Get-SQLServerKey {
  ## function to retrieve the license key of a SQL 2008 Server.
   param ($targets = ".")
  $hklm = 2147483650
  $regPath = "SOFTWARE\Microsoft\Microsoft SQL Server0\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 format of characters in SQL Server 2012 serial number has changed, and $binarray = ()$ data.uValue )[0.. 16] different from SQL Server 2008’s $binarray = ($ data.uValue )[52.. 66], at the same time, don’t forget to change the registry path $regpath = software / Microsoft / Microsoft SQL Server / 110 / tools / setup “. The modified path is as follows and passed the test on the instance of SQL Server 2012:


function Get-SQLServerKey {
## function to retrieve the license key of a SQL 2012 Server.
## by Jakob Bindslet ([email protected])
## 2012 Modification by Xian Wang ([email protected])
param ($targets = ".")
$hklm = 2147483650
$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server0\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)[0..16]
$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
}
}

3. Call the shell function and output the serial number

Open the shell, paste the function above, press enter, enter get SQL server key and press enter;

Or save the above functions as. PS1 file for direct reference:


PS C:\Windows\system32> . C:\Users\username\Desktop\pk.ps1
PS C:\Windows\system32> Get-SQLserverKey

The output results are as follows: the first and last characters are changed to asterisks artificially, and they are not pasted out.


Computer  : .
OSCaption : Microsoft Windows Server 2012 R2 Standard
OSArch   : 64-bit
SQLver   : 11.2.5058.0
SQLedition : Developer Edition
ProductKey : *****-G8T4R-QW4XX-BVH62-*****

For SQL Server 2000 and 2005, there is no test because there is no environment at hand; for office and windows systems, the serial number should be obtained in a similar way.

The above content is about how to retrieve the serial number of SQL server instance after installation. I hope you can help.