Home>

When you need to install SQL Server again, if the serial number is not found,Try to retrieve the serial number from the installed instance,Because after installing SQL Server, the serial number (product key) is saved in the registry;

msdn subscription download installation package is built-in serial number,There is no such trouble.

I. Where is the serial number saved

Read the registry through the undocumented extended stored procedure xp_regread:

--for sql server 2008, 2008 r2
use master
go
exec xp_regread "hkey_local_machine", "software \ microsoft \ microsoft sql server \ 100 \ tools \ setup", "productcode"
exec xp_regread "hkey_local_machine", "software \ microsoft \ microsoft sql server \ 100 \ tools \ setup", "digitalproductid"
go
--for sql server 2012
use master
go
exec xp_regread "hkey_local_machine", "software \ microsoft \ microsoft sql server \ 110 \ tools \ setup", "productcode"
exec xp_regread "hkey_local_machine", "software \ microsoft \ microsoft sql server \ 110 \ tools \ setup", "digitalproductid"
go

Don't be fooled by the productcode, even if only the sql server client is installed, there will be this key in the registry,Not a serial number,The digital productid is, but after base24 encoding, it needs to be decoded.

can be seen,For different versions,The path to the registry is different,But the keys are consistent.

The express version is free,No serial number,Thus the registry does not have the digitalproductid key.

How to decode the serial number

1. Introduction to base24, base64 encoding

Many people may have heard of base64 encoding, which is used to encode longer strings.To facilitate transmission;

Base24 encoding is mainly used for serial number generation.The implementation ideas of the two are similar,It's just that the mode of encoding has changed a bit.

The encoding table corresponding to base64 is:

abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz0123456789 +/=

A total of 64 characters.

The encoding table corresponding to base24 is:

bcdfghjkmpqrtvwxy2346789

There are a total of 24 characters, and some characters that are not easy to identify and confuse for the serial number are mainly removed here.

Encoding/decoding is not encryption/decryption. There is no secret key.Only the rules for character conversion,The detailed algorithms of base24 and base64 can be found online.

2. Decoding using powershell

The following powershell functions are used to decode/retrieve the serial number of the SQL server. The test passed on the 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 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 format of the characters in the SQL Server 2012 serial number has changed.$binarray=($data.uvalue) [0..16] is different from $binarray=($data.uvalue) [52..66] of sql server 2008, and do n’t forget to change the registry path $regpath=" software \ microsoft \ microsoft sql server \ 110 \ tools \ setup ", modified as follows,Tested on the SQL Server 2012 instance:

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 server \ 110 \ 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 powershell function and output the serial number

Open powershell and paste the above function into it.Enter, enter get-sqlserverkey and press Enter;

Or save the above function as.The ps1 file directly references:

ps c:\ windows \ system32>. c:\ users \ username \ desktop \ pk.ps1
ps c:\ windows \ system32>get-sqlserverkey

The output is as follows,The first and last characters were artificially changed to asterisks.Not posted.

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, 2005, because there is no environment at hand,Not testedFor office and windows systems, you should be able to get the serial number in a similar way.

  • Previous Explain the use of array and string types in Java
  • Next js view the execution time of a function instance code