Thursday, November 11, 2010

SQL Server Product Key

A while back I wrote a small script to retrieve the Windows license key. Since then I've been asked several times how to retrieve the product key of an SQL Server.
So today I threw together this small script to do just that

function Get-SQLserverKey {
    ## function to retrieve the license key of a SQL 2008 Server.
    ## by Jakob Bindslet (jakob@bindslet.dk)
    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
    }
}



Use the function to retrieve the Product Key from the local PC:

Get-SQLserverKey

Or to retrieve the Product Key from one or more PCs (locally or remotely):

Get-SQLserverKey "pc1", "pc2", "server999", "server777"

Remeber that the output from the function is a standard PowerShell object, so you can pipe into sort-object, format-table or mayby ConvertTo-HTML ...

27 comments:

Jakob Bindslet said...

The above script works against SQL Server 2008 and SQL Server 2008 R2, NOT SQL 2005.

Partha said...

Thanks very much! it helped me.
Partha

Unknown said...

This doesn't seem to work for me??? Although I am only a powershell novice. I have tried to run this directly in Powershell, and from the run prompt, but nothing is displayed.

Would you mind posting details on how exactly to run this??

Jakob Bindslet said...

Copy the function to your PowerShell prompt.

Use the function like this:

Get-SQLServerKey

Please note that it is the servername, not server\instance.

If the script is used remotely (ie. not on the server running SQL Server), sufficient rights on the windows box, need to be in order.

Unknown said...

Worked like a charm.

Copy the code into the powershell window.

Very cool thanks!!

Lars M. Nielsen said...

Hi Jakob. I'm running the script, but I'm getting no output. The server is 64 bit, does that make a difference?

John D said...

Thanks for the script. I have the same issue as Lars. Server is 64 bit and I'm getting no output. I'm very much a newbie to Powershell - actually this is the first thing I've tried to run.

Unknown said...

Even I tried on server, 64 bit Enterprise Edition but ran into errors...

Kimmee said...

I am running on SQL 2008 64bit and do not receive any output. Has there been any updates for the script?

Jakob Bindslet said...

I've received quite a lot of questions regarding this script.

Please note that:

To use the script you should start PowerShell, copy'n'paste the function into the session (press ENTER).
THEN you can use the function by typing:

Get-SQLserverKey

This should procude output similar to the following:

Computer : MyServer001.domain.com
OSCaption : Microsoft Windows Server 2008 R2 Enterprise
OSArch : 64-bit
SQLver : 10.50.1600.1
SQLedition : Enterprise Edition
ProductKey : AAAAA-BBBBB-CCCCC-DDDD-EEEEE

The function has been testet on both 32- and 64-bit SQL Servers.

Chr!s said...

Hi Jakob,

many thanks for your nice skript. Works like a charm.
I'd be interested in the inverse function too: writing a new PID to the registry. Dom you have an idea?

Cheers,
Chr!s from Hamburg, Germany

Arun Shetty said...

Your script works great! Saved my day today. Thanks a bunch and keep up the good work :-)

-Arun.

Lay said...
This comment has been removed by the author.
Lay said...

Jakob,
thanks heaps for sharing, works for me!

Ed said...

Good day! And quite by chance, you do not have a procedure that would have replaced the key installation? The problem is that when you install the correct key is not used. Now the license purchased, the correct key is, but peretavlyat SQL server is not desirable. I have installed MS SQL 2008 R2 Standart Edition. Thank you in advance for your help!

Saravanan said...

How to change the product key for SQL 2008 R2, I need to do this for 100+ computers, pls help me on this

pi said...

Is it possible to know with a powershell script how many CALs are in the SQL server?
Thanks

Ken Quigley said...

Thanks for this Jacob. Once I ran it int the correct Powershell environment, it worked first try.
This was exceedingly usefuly for a customer that had SQL installed by my IT predecessor and lost the licensing key.

P.A. said...

The script worked for me. Many thanks to the author!

Juanita Fischer said...

I had to reroute the script to look for the DigitalProductID in a different location, but it worked like a charm!!! Thanks....

xerio said...

Thanks a lot!

sleepless said...

i cannot get past this code: $binArray = ($data.uValue)[52..66]

Cannot index into a null array.
At line:17 char:36
+ $binArray = ($data.uValue)[ <<<< 52..66]
+ CategoryInfo : InvalidOperation: (System.Object[]:Object[]) [], RuntimeException
+ FullyQualifiedErrorId : NullArray

i copied and pasted from the article.

Nirmal said...

awesome..works like a charm..
thanks a lot

Rajan said...

Thanks a lot. You saved my time. Really it is awesome.

Xian Wang said...

Hi all,
I've modified the script to work with Sql Server 2012 installations. Please see this post:
http://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/

Joseph S said...

Thank you very much, it was awesome!!
I installed an evaluation version of SQL server in my production server and later on purchased the license. As my company was under volume licensing scheme, as such, it won't have product key, just have to download and install from the resellers website.
Calling MS support team was of no use, they asked me to uninstall and then re-install the downloaded version. They can only provide key if we purchase a retail license.

I was going nuts until I saw your post, I was able to use the script to read the key from another server which was running the same version. I got the key, ran the edition upgrade in the target server and in 2 mins, everything is settled.

Great job!! keep it up.

Thanks again

Joseph S said...

Thank you very much, it was awesome!!
I installed an evaluation version of SQL server in my production server and later on purchased the license. As my company was under volume licensing scheme, as such, it won't have product key, just have to download and install from the resellers website.
Calling MS support team was of no use, they asked me to uninstall and then re-install the downloaded version. They can only provide key if we purchase a retail license.

I was going nuts until I saw your post, I was able to use the script to read the key from another server which was running the same version. I got the key, ran the edition upgrade in the target server and in 2 mins, everything is settled.

Great job!! keep it up.

Thanks again