OK, this task is not accidental in general. The task, probably, appeared withe the very first deployment of SQL Server, or a bit later, when its CD box was lost. But it was new for me, so I started googling.
The very first solution seem to be in Jakob Bindslet’s post on 11th of November 2010. His script is locked to SQL Server 2008 and not looking for multi-instance installations. The post has lots of comments and some updated scripts suggested by visitors. Next to that come posts by Ryan on 17th of October 2015 and Xian Wang’s one on 22nd of June 2013. Both scripts have improvements for SQL Server 2012, but still not universal. Forgot to mention: I need a really universal solution working with SQL from 2005 to SQL 2017. Reading comments to these posts, I found a link to a brilliant script by Chrissy LeMaire in MS Script Center here. The script is really universal, as it’s browsing registry scanning for all installed instances and do a wildcard search for older versions. Unfortunately, for today’s date, Chrissy’s script is a bit outdated, as HawkmanAZ pointed out in commend, as suggested a new decoding procedure. The new procedure works really great for SQL 2012+, however doesn’t have backward compatibility (as the author warned, he/she didn’t test it with earlier versions).
Since here, I had nothing to do, but build my own tool. Luckily, I have access to installations of almost all versions of SQL Server: 2005, 2008 /R2, 2012, 2014, 2016, and 2017, all in Standard, Enterprise and other editions. First of all, I took HawkmanAZ’s decoding procedure and optimized it for backward compatibility.
private static string RecoveryProductKeyFromBinary(byte[] regBinary)
{
if (regBinary == null)
return null;
try
{
bool isNKey = ((regBinary[14] / 0x6) & 0x1) != 0;
if (isNKey)
regBinary[14] = Convert.ToByte((regBinary[14] & 0xF7));
string regBinaryOutput = "";
int last = 0;
for (int i = 24; i >= 0; i--)
{
int k = 0;
for (int j = 14; j >= 0; j--)
{
k = (k * 256) ^ regBinary[j]; // k = k * 256; k = regBinary[j] + k;
regBinary[j] = Convert.ToByte(k / 24);
k = k % 24;
}
regBinaryOutput = charsArray[k] + regBinaryOutput;
last = k;
}
string regBinarypart1 = regBinaryOutput.Substring(1, last);
string regBinarypart2 = regBinaryOutput.Substring(1, regBinaryOutput.Length - 1);
if (isNKey)
if (last == 0)
regBinaryOutput = "N" + regBinarypart2;
else
regBinaryOutput = regBinarypart2.Insert(regBinarypart2.IndexOf(regBinarypart1) + regBinarypart1.Length, "N");
string a = regBinaryOutput.Substring(0, 5);
string b = regBinaryOutput.Substring(5, 5);
string c = regBinaryOutput.Substring(10, 5);
string d = regBinaryOutput.Substring(15, 5);
string e = regBinaryOutput.Substring(20, 5);
string regBinaryproduct = a + "-" + b + "-" + c + "-" + d + "-" + e;
return regBinaryproduct;
}
catch
{
return null;
}
}
Strictly speaking, there are just two change to decoding from the very original Jakob’s post. After detecting a new type of encoding by examining 15’s (index 14) byte, the same byte either masked by 0xF7 or not, and the letter N is either inserted or not at the position of last remainder. Don’t ask me why. I don’t know.
Next task was to create a proper registry discovery for all possible versions. Honestly, only Chrissy’s script is great in this. On the top of that, I made my own research through all installations, available for me, which resulted in the following code:
RegistryKey sqlRoot = RegistryHelper.GetRegistryKey(PrincipalName, "HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server");
RegistryKey instanceListRoot = sqlRoot.OpenSubKey("Instance Names");
foreach (string roleTypeKeyName in instanceListRoot.GetSubKeyNames())
{
RegistryKey roleTypeKey = instanceListRoot.OpenSubKey(roleTypeKeyName);
foreach (string roleInstanceValueName in roleTypeKey.GetValueNames())
{
RegistryKey roleInstanceSetupKey = sqlRoot.OpenSubKey($"{(string)roleTypeKey.GetValue(roleInstanceValueName)}\\Setup");
string versionStr = (string)roleInstanceSetupKey.GetValue("Version", "N/A");
Version version = null;
try { version = new Version(versionStr); } catch { }
switch (roleTypeKeyName)
{
case "SQL":
Console.WriteLine("Role Type: SQL Server");
break;
case "OLAP":
Console.WriteLine("Role Type: SQL Server Analysis Services");
break;
case "RS":
Console.WriteLine("Role Type: SQL Server Reporting Services");
break;
default:
Console.WriteLine("Role Type: Other Services");
break;
}
Console.WriteLine("Instance Name: " + roleInstanceValueName);
Console.WriteLine("Version: {0} ({1})", versionStr, GetSQLGeneration(version));
Console.WriteLine("Edition: " + (string)roleInstanceSetupKey.GetValue("Edition", "N/A"));
Console.WriteLine("PatchLevel: " + (string)roleInstanceSetupKey.GetValue("PatchLevel", "N/A"));
Console.WriteLine("Service Pack: " + (int)roleInstanceSetupKey.GetValue("SP", 0));
string ProductKey = "N/A";
try
{
if (version != null)
{
if (version.Major >= 11)
ProductKey = RecoveryProductKeyFromBinary((byte[])roleInstanceSetupKey.GetValue("DigitalProductID", null));
else if (version.Major >= 10)
{
byte[] productId = new byte[15];
Array.Copy((byte[])roleInstanceSetupKey.GetValue("DigitalProductID", null), 52, productId, 0, 15);
ProductKey = RecoveryProductKeyFromBinary(productId);
}
else
{
string template = "HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\{0}\\ProductID";
for (int ver = 140; ver >= 70; ver = ver - 10)
{
string regPath = string.Format(template, ver);
if (RegistryHelper.RegistryKeyExists(PrincipalName, regPath))
{
RegistryKey productKey = RegistryHelper.GetRegistryKey(PrincipalName, regPath);
foreach (string prdSubKey in productKey.GetValueNames())
if (prdSubKey.IndexOf("DigitalProductID") >= 0)
{
byte[] productId = new byte[15];
Array.Copy((byte[])productKey.GetValue(prdSubKey, null), 52, productId, 0, 15);
ProductKey = RecoveryProductKeyFromBinary(productId);
}
}
}
}
}
}
catch (Exception e) { Console.WriteLine(e.Message); }
Console.WriteLine("Product Key: " + ProductKey);
Console.WriteLine();
}
}
This code successfully decoded product keys for SQL Server 2008 R2 Standard, 2012 Standard, 2016 Standard/Enterprise Core-Based, and 2017 Standard/Enterprise/Enterprise Core-Based. By saying “successfully “, I mean I compared decoded codes with the original ones used for these installations, and they matched.
You can download the whole project from my GitHub repository.
Max's blog on SCOM Authoring and more 
I’ve tested the code and is not returning any output.
LikeLike
Hey, can you give a bit more details about your environment? Like what version of SQL Server you’re trying to recover?
LikeLike
My bad, it’s requires an elevated privileges
LikeLike
It worked perfectly, thanks a lot.
LikeLike
You’re welcome!
LikeLike