After the installation is complete, how can I retrieve the serial number of the SQL Server instance during installation?

After the installation is complete, how can I retrieve the serial number of the SQL Server instance during installation?

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 serial number (Product Key) saved in the registry;

This problem does not occur if the installation package subscribed to and downloaded by MSDN has a built-in serial number.

I. Where can I save the serial number?

Read the Registry through the undocumented extended stored procedure xp_regread:

--For SQL Server 2008, 2008 R2use masterGOexec 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 2012use masterGOexec 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

Do not be confused by ProductCode. Even if only the SQL Server Client is installed, the key value in the registry is not the serial number. DigitalProductID is the key value, but it must be decoded after Base24 encoding.

We can see that for different versions, the registry paths are different, but the keys are consistent.

The Express version is free and has no serial number, so the registry does not have the DigitalProductID key.

Ii. How to decode the serial number

1. Introduction to Base24 and Base64 encoding

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

Base24 encoding is mainly used to generate serial numbers. The implementation of the two methods is similar, but the encoding mode is somewhat changed.

The encoding table corresponding to Base64 is:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 +/=

A total of 64 characters.

The encoding table corresponding to Base24 is:

Bcdfghjkmpqrtvwxy231689

There are 24 characters in total. Here we mainly remove some characters that are not easy to identify and confuse for serial numbers.

The serial number of SQL Server uses Base24 encoding to decode the encoded characters.

Encoding/decoding is not encryption/decryption, and there is no key. Only character conversion rules, Base24 and Base64 detailed algorithms can be found online.

2. Use Powershell to decode

The following powershell function is used to decode/retrieve the SQL Server serial number. It passes the test on the SQL Server 2008,200 8 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 character format in SQL Server 2012 serial number has changed, $ binArray = ($ data. uValue) [0 .. 16] different from SQL Server 2008's $ binArray = ($ data. uValue) [52 .. 66]. Do not forget to change the Registry path $ regPath = "SOFTWARE \ Microsoft SQL Server \ 110 \ Tools \ Setup". The modification is as follows:

function Get-SQLServerKey {## function to retrieve the license key of a SQL 2012 Server.## by Jakob Bindslet (jakob@bindslet.dk)## 2012 Modification by Xian Wang (daanno2@gmail.com)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 dataFor ($i = 24; $i -ge 0; $i--) {$k = 0For ($j = 14; $j -ge 0; $j--) {$k = $k * 256 -bxor $binArray[$j]$binArray[$j] = [math]::truncate($k / 24)$k = $k % 24}$productKey = $charsArray[$k] + $productKeyIf (($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, paste the function above into it, press enter, enter Get-SQLServerKey, and press ENTER;

Or directly reference the above function as the. ps1 file:

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

The output result is as follows. The first and last characters are manually changed to asterisks and are not pasted.

Computer  : .OSCaption : Microsoft Windows Server 2012 R2 StandardOSArch   : 64-bitSQLver   : 11.2.5058.0SQLedition : Developer EditionProductKey : *****-G8T4R-QW4XX-BVH62-*****

For SQL Server 2000,200 5, there is no environment at hand and no tests are available. For Office and Windows systems, serial numbers can also be obtained in a similar way.

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