How to retrieve the serial number of the SQL Server instance installation when the installation is complete _mssql

When you need to install SQL Server again, if the serial number is not found, you can try to retrieve the serial number from the installed instance, because after the installation of SQL Server, the serial number (Product Key) is stored in the registry;

MSDN Subscriptions Download the installation package is a built-in serial number, there is no such trouble.

First. Where is the serial number saved?

Read the registry through Xp_regread extended stored procedures:

--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 ProductCode, even if only the SQL Server client is installed, this key will be in the registry, not the serial number, DigitalProductID is, but after Base24 encoding, decoding is required.

It can be seen that the registry path is different for different versions, but the keys are the same.

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

Second. How to decode the serial number

1. Base24, Base64 encoding introduction

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

Base24 encoding is mainly used in the generation of serial numbers. The implementation of the two is similar, but the encoding mode is slightly changed.

The encoding table corresponding to Base64 is:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 + / =

A total of 64 characters.

The coding table corresponding to Base24 is:

BCDFGHJKMPQRTVWXY2346789

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

The serial number of SQL Server uses Base24 encoding, and the encoded characters can be decoded to obtain the original text.

Encoding / decoding is not encryption / decryption. There is no secret key, only character conversion rules, and detailed algorithms of Base24 and Base64 can be found online.

2. Decode with Powershell

The following powershell function is used to decode / retrieve the SQL Server serial number, and it passes the test 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 characters in the serial number of SQL Server 2012 has changed. $ BinArray = ($ data.uValue) [0..16] is 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". After modification, the test is passed on the SQL Server 2012 instance:

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 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, paste the above function, press Enter, enter Get-SQLServerKey and press Enter;

Or save the above function as a .ps1 file and directly quote:

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, but they were 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, there is no test; for Office and Windows systems, the serial number should be obtained in a similar manner.

The above content is about how to retrieve the serial number when the SQL Server instance is installed after the installation is completed.

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch