Saturday, July 14, 2012

SQL Server Product Key - update

In the past, I've posted a few scripts to extract the Windows and/or SQL Server license keys.
Since these posts have been some of the most popular* posts on my blog, I've decided to update those scripts, and make them more compatible with newer version of Windows and SQL Server.

* I've received more than 100 emails and comments about just those scripts.

The script below has been tested against the following versions:
          Windows Server 2003
          Windows Server 2008
          Windows Server 2008 R2
          Windows Server 2012 RC
          SQL Server 2005
          SQL Server 2008
          SQL Server 2008 R2
          SQL Server 2012


Function SQLbuild2name {
    Param ($build)   
    Switch -wildcard ($build) {
        "8.0*"    { $name = "SQL Server 2000"}
        "9.0*"    { $name = "SQL Server 2005"}
        "10.0*"    { $name = "SQL Server 2008"}
        "10.5*"    { $name = "SQL Server 2008R2"}
        "11.0*"    { $name = "SQL Server 2012"}
        Default    { $name = "Unknown version"}
    }
    $name
}

Function Get-SQLserverKey {
    ## function to retrieve the Windows Product Key from any PC as well as the SQL Server Product Key
    ## by Jakob Bindslet (jakob@bindslet.dk), last updated 2012-07-14
    Param ($targets = ".")
    $hklm = 2147483650
    $regPath2005 = "SOFTWARE\Microsoft\Microsoft SQL Server\90\ProductID"
    $regPath2005version = "SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion"
    $regPath2008 = "SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup"
    $regPath2012 = "SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup"
    $regValue2005 = "DigitalProductId77591"
    $regValue2008 = "DigitalProductId"
    $regValue2012 = "DigitalProductId"
    $regValueVersion2005 = "CurrentVersion"
    $regValueVersion2008 = "Version"
    $regValueVersion2012 = "Version"
    $SQLinstalled = $false
    Foreach ($target in $targets) {
        $productKey = $null
        $win32os = $null
        $sqlversion = $null
        $wmi = [WMIClass]"\\$target\root\default:stdRegProv"
        $data2005 = $wmi.GetBinaryValue($hklm,$regPath2005,$regValue2005)
        $data2008 = $wmi.GetBinaryValue($hklm,$regPath2008,$regValue2008)
        $data2012 = $wmi.GetBinaryValue($hklm,$regPath2012,$regValue2012)
        $productKey = ""
        if ($data2005.uValue.length -ge 1) {
            $binArray2005 = ($data2005.uValue)[52..66]
            $binArray = $binArray2005
            $sqlversion = $wmi.GetStringValue($hklm,$regPath2005version,$regValueVersion2005).sValue
            $SQLinstalled = $true
        }
        if ($data2008.uValue.length -ge 1) {
            $binArray2008 = ($data2008.uValue)[52..66]
            $binArray = $binArray2008
            $sqlversion = $wmi.GetStringValue($hklm,$regPath2008,$regValueVersion2008).sValue
            $SQLinstalled = $true
        }
        if ($data2012.uValue.length -ge 1) {
            $binArray2012 = ($data2012.uValue)
            $binArray = $binArray2012
            $sqlversion = $wmi.GetStringValue($hklm,$regPath2012,$regValueVersion2012).sValue
            $SQLinstalled = $true
           }

        if ($SQLinstalled) {
            ## decrypt base24 encoded binary data
            $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"
            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
                }
            }
        } else {
            $productKey = "no SQL Server found"
        }
        $win32os = Get-WmiObject Win32_OperatingSystem -computer $target
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Computer -value $target
        $obj | Add-Member Noteproperty Caption -value $win32os.Caption
        $obj | Add-Member Noteproperty CSDVersion -value $win32os.CSDVersion
        $obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
        $obj | Add-Member Noteproperty BuildNumber -value $win32os.BuildNumber
        $obj | Add-Member Noteproperty RegisteredTo -value $win32os.RegisteredUser
        $obj | Add-Member Noteproperty "ProductID (Windows)" -value $win32os.SerialNumber
        $obj | Add-Member Noteproperty "SQL Version" -value "$(SQLbuild2name $sqlversion) ($sqlversion)"
        $obj | Add-Member Noteproperty "ProductKey (SQL Server)" -value $productkey
        $obj
    }
}




Usage:


Get-SQLserverKey computername



Or if you just need to target the local computer:

Get-SQLserverKey

3 comments:

Unknown said...

Thanks for sharing this usefull script.

I noticed a couple of SQL 2005 reporting as "No SQL Server found"
Your script checks for the hardcoded ProductID
$regValue2005 =
"DigitalProductId77591"

Where i found some of ours to be using "77671"

just fyi.

Regards.

Jakob Bindslet said...

For a much improved version, see Chrissy LeMaires' script here: https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8

Unknown said...

You guys can try this site to get a valid Product Key all editions: www.gankings.com.